Sales Comparison Year on Year

[former_member509679]

Hi all,

Wondering if you can help, i am trying to get a comparison on sales figures.

It needs to include the last 3 months sales, this years sales and last years sales.

I created the code below but got stuck as it doesn't pull through the correct data, any suggestions?

SELECT T0.[CardCode], T2.[Name],

(SELECT sum(T1.[DocTotal]-T1.[VatSum]) FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN [dbo].[@COMMISSION] T2 ON T0.[U_Commission] = T2.[Code] WHERE YEAR( T1.[DocDate]) = datepart(mm,getdate())-3) as 'Last 3 Months',

(SELECT sum(T1.[DocTotal]-T1.[VatSum]) FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN [dbo].[@COMMISSION] T2 ON T0.[U_Commission] = T2.[Code] WHERE YEAR( T1.[DocDate]) = datepart(yy,getdate())-1) as 'Last Year',

(SELECT sum(T1.[DocTotal]-T1.[VatSum]) FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] INNER JOIN [dbo].[@COMMISSION] T2 ON T0.[U_Commission] = T2.[Code] WHERE YEAR( T1.[DocDate]) = datepart(yy,getdate())) as 'Current Year'

FROM OCRD T0 INNER JOIN OINV T1 ON T0.[CardCode] = T1.[CardCode] LEFT OUTER JOIN [dbo].[@COMMISSION] T2 ON T0.[U_Commission] = T2.[Code]

GROUP BY T0.[CardCode], T2.[Name]

[former_member509679 · Accepted solution]

Managed to resolve the issue.

Thank you all for your help, really appreciated.

SELECT TT.CardCode, TT.CardName, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months', TT.Name
FROM (
SELECT T0.CardCode, T1.CardName, T2.Name,
ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate()) group by CardCode),0) as 'Actual',
ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and year(OINV.DocDate)=year(getdate())-1 group by CardCode),0) as 'Last',
ISNULL((SELECT sum(DocTotal-VatSum) from OINV where OINV.CardCode=T0.Cardcode and datediff (day, getdate(), DocDate)>-90 group by CardCode),0) as 'months'
FROM OINV T0
INNER JOIN OCRD T1 ON T0.CardCode=T1.CardCode
LEFT OUTER JOIN [@COMMISSION] T2 ON T1.[U_Commission] = T2.[Code]
) TT
GROUP BY TT.CardCode, TT.CardName, TT.Name