Skip to Content
0
Former Member
Jul 31, 2008 at 02:56 PM

Year to Date query

20 Views

SELECT T0.CardName, sum(T1.Price) [Year To Date], '0' [Previous] FROM OINV T0 INNER JOIN INV1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.DocDate Between DATEADD(year,-1,GETDATE()) AND GETDATE() GROUP BY T0.CardName

UNION

SELECT T2.CardName, '0' [Year To Date], sum(T1.Price) [Previous] FROM OINV T2 INNER JOIN INV1 T1 ON T2.DocEntry = T1.DocEntry WHERE T2.DocDate Between DATEADD(year,-2,GETDATE()) AND DATEADD(year,-1,GETDATE()) GROUP BY T2.CardName

ORDER BY T0.CardName asc

Gives me, for example:

A 100 0

A 0 150

B 200 0

B 0 50

C 300 0

D 0 550

How would I go about joining them together into:

A 100 150

B 200 50

C 300 0

D 0 550

Thanks,

Jason