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

[gonzalogomez]

But.... if you want fields of the user table, You have to add the fields in the first part of the select and in the group by ... take a look at this, if no... there is no necesary....try with left join simple

SELECT TT.CardCode, TT.CardName, TT.U_Commission, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months'
FROM (
SELECT T0.CardCode, T1.CardName, T2.Name, T1.U_Commission, 
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 JOIN [dbo].[@COMMISSION] T2 ON T1.U_Commission = T2.Code
) TT
GROUP BY TT.CardCode, TT.CardName, T1.U_Commission

[former_member509679]

Thank you, it is the User Defined Table name not the value i am trying to pull through.

The UDT is called @COMMISSION and i am trying to pull through the Name field not the value, not all BP's have a value set so i have used a left outer join.

The issue i am having now is with the below it is not pulling through any value at all? Any suggestions as to why? Sorry to keep asking.

SELECT TT.CardCode, TT.CardName, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months'
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 [dbo].[@COMMISSION] T2 ON T1.[U_Commission] = T2.[Code]
) TT
GROUP BY TT.CardCode, TT.CardName

[gonzalogomez]

Hello, in this one i´ve added the join with OCRD and the CardName, make the same with other tables that you want

SELECT TT.CardCode, TT.CardName, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months'
FROM (
SELECT T0.CardCode, T1.CardName,
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
) TT
GROUP BY TT.CardCode, TT.CardName

[kothandaraman_nagarajan]

Hi,

Share error message from above query if any.

Regards,

Nagarajan

[gonzalogomez]

Try with this...Modify to add your fields

SELECT TT.CardCode, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months'
FROM (
SELECT T0.CardCode,
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
) TT
GROUP BY TT.CardCode

[former_member509679]

Thank you, i have tried this and it works but i am unable to add my fields, so i have clearly done them wrong any idea why?

SELECT TT.CardCode, max(TT.Actual) as 'Actual Year', max(TT.Last) as 'Last Year', max(TT.months) as 'Last 3 months', T2.[Name]
FROM (
SELECT T0.CardCode
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 LEFT OUTER JOIN OCRD T1 ON T0.CardCode=T1.CardCode LEFT OUTER JOIN [dbo].[@COMMISSION] T2 ON T1.[U_Commission] = T2.[Code]
) TT
GROUP BY TT.CardCode, T2.[Name]