Skip to Content
0

Sales Comparison Year on Year

Feb 09, 2017 at 02:12 PM

68

avatar image

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]

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

6 Answers

Best Answer
Scott Cox Feb 13, 2017 at 03:45 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded
Gonzalo Gomez Feb 09, 2017 at 04:54 PM
0

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
Show 1 Share
10 |10000 characters needed characters left characters exceeded

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]
0
Nagarajan K Feb 10, 2017 at 07:38 AM
0

Hi,

Share error message from above query if any.

Regards,

Nagarajan

Share
10 |10000 characters needed characters left characters exceeded
Gonzalo Gomez Feb 10, 2017 at 08:32 AM
0

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
Share
10 |10000 characters needed characters left characters exceeded
Scott Cox Feb 13, 2017 at 03:04 PM
0

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
Share
10 |10000 characters needed characters left characters exceeded
Gonzalo Gomez Feb 14, 2017 at 11:39 AM
0

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
Share
10 |10000 characters needed characters left characters exceeded