Year on Year Comparison by Customer

[former_member596919]

Hi,

I'm trying to create a report that shows year on year sales invoice totals by customer. So it would be:

Customer, 2014 Invoiced Total, 2015 YTD Invoiced Total

I've tried the below, but this isn't grouping the customers together to show a total value per year.

SELECT DISTINCT T0.[CardCode], T0.[CardName], CASE WHEN T1.[DocDate] BETWEEN '01.01.2014' AND '12.31.14' THEN SUM(T1.[DocTotal]) ELSE NULL END AS '2014 Invoiced (£)', CASE WHEN T1.[DocDate] BETWEEN '01.01.2015' AND GETDATE() THEN SUM(T1.[DocTotal]) ELSE NULL END AS '2015 Invoiced (£)' FROM OCRD T0  INNER JOIN OINV T1 ON T0.CardCode = T1.CardCode WHERE T1.[CANCELED] <> 'Y' GROUP BY T0.[CardCode], T0.[CardName],T1.[DocDate]

Kind Regards,

Kirstie

[KennedyT21 · Accepted solution]

SELECT CardCode,

       (

           SELECT SUM(DocTotal)

           FROM   OINV

           WHERE  CardCode = T0.CardCode

                  AND CANCELED = 'N'

                  AND DocDate BETWEEN '2014-01-01' AND '2014-12-31'

       ) Total2014,

       (

           SELECT SUM(DocTotal)

           FROM   OINV

           WHERE  CardCode = T0.CardCode

                  AND CANCELED = 'N'

                  AND DocDate BETWEEN '2014-05-01' AND '2015-12-31'

       ) Total2015

FROM   OCRD T0

WHERE  T0.CardType = 'C'

Regards

Kennedy

[former_member596919 · Accepted solution]

Hi Kennedy,

This seems to have worked.

However, it doesn't include credit notes. Any idea how I can also include these?

Please note I don't want VAT to be included so I have added this in too.

Kind  regards,

Kirstie

[Former Member · Accepted solution]

Use this

SELECT CardCode,

       (

           SELECT SUM(v1.lineTotal)-(SELECT isnull(SUM(i1.lineTotal),0)

           FROM   ORIN i inner join rin1 i1 on i.docentry=i1.docentry

           WHERE  i.CardCode = v.CardCode

                  AND CANCELED = 'N'

                  AND i.DocDate BETWEEN '2014-01-01' AND '2014-12-31')

            FROM   OINV v inner join INV1 v1 on v.DocEntry=v1.DocEntry

           WHERE  v.CardCode = T0.CardCode

                  AND CANCELED = 'N'

                  AND v.DocDate BETWEEN '2014-01-01' AND '2014-12-31' group by v.CardCode

       ) Total2014,

       (

           SELECT SUM(v1.lineTotal)-(SELECT isnull(SUM(i1.lineTotal),0)

           FROM   ORIN i inner join rin1 i1 on i.docentry=i1.docentry

           WHERE  i.CardCode = v.CardCode

                  AND CANCELED = 'N'

                  AND i.DocDate BETWEEN '2015-01-01' AND '2015-12-31')

           FROM   OINV v inner join INV1 v1 on v.DocEntry=v1.DocEntry

           WHERE  v.CardCode = T0.CardCode

                  AND CANCELED = 'N'

                  AND v.DocDate BETWEEN '2015-01-01' AND '2015-12-31' group by v.CardCode

       ) Total2015

FROM   OCRD T0

WHERE  T0.CardType = 'C'

[former_member596919 · Accepted solution]

Hi,

Thank you, this is a lot closer. There is one more thing it still isn't taking into consideration discounts.

Would we be able to remove these as well?

Kind  Regards,

Kirstie

[frank_wang6 · Accepted solution]

Try this.

SELECT CardCode,

ISNULL((SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2014-01-01' AND '2014-12-31'), 0)

- ISNULL((SELECT SUM(DocTotal) FROM ORIN WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2014-01-01' AND '2014-12-31'), 0)

Total2014,

ISNULL((SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2015-01-01' AND '2015-12-31'), 0)

- ISNULL((SELECT SUM(DocTotal) FROM ORIN WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2015-01-01' AND '2015-12-31'), 0)Total2015

FROM OCRD T0

WHERE T0.CardType = 'C'

[former_member596919 · Accepted solution]

Hi Frank,

Thank you for this. Once I subtracted the VAT total it was then showing the correct figures!

Kind Regards,

Kirstie

[former_member596919 · Accepted solution]

Hi all,

One last thing.

Does anyone know how I would be able to only show business partners that have a value against 2014 or 2015 sales figures?

Kind  Regards,

Kirstie

[frank_wang6 · Accepted solution]

Yes, u add another layer on that.

Frank

SELECT * FROM

(
SELECT CardCode,
ISNULL((SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2014-01-01' AND '2014-12-31'), 0)
- ISNULL((SELECT SUM(DocTotal) FROM ORIN WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2014-01-01' AND '2014-12-31'), 0)
Total2014,
ISNULL((SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2015-01-01' AND '2015-12-31'), 0)
- ISNULL((SELECT SUM(DocTotal) FROM ORIN WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2015-01-01' AND '2015-12-31'), 0)Total2015
FROM OCRD T0
WHERE T0.CardType = 'C') T
WHERE T.Total2014 <> 0 OR T.Total2014 <> 0

[former_member596919 · Accepted solution]

Hi Frank,

thanks for the above.

This seems to have worked for 2014. But for the 2015 numbers it is excluding customers that have had sales in 2015 but none in 2014.

Kind  Regards,

Kirstie

[former_member596919 · Accepted solution]

Hi Frank,

There was one small error which I managed to fix "T.Total2014" was listed twice instead of T.Total2015.

Thank you for your help!

Kind Regards,

Kirstie

[kothandaraman_nagarajan]

Hi,

Try this query for easy comparison:

SELECT

[CustName] as CustName,[2011],[2012],[2013]

from

(SELECT

T0.[CardName] as CustName, sum(T1.[LineTotal]) as Total,

datepart(yy,T0.[DocDate]) as year FROM OINV T0 INNER JOIN INV1 T1 ON

T0.DocEntry = T1.DocEntry WHERE

T0.[CardCode] = 'cvet01' GROUP BY T0.[CardName],T0.[DocDate]

union all

(SELECT

T0.[CardName] as CustName, -sum(T1.[LineTotal]) as Total,

datepart(yy,T0.[DocDate]) as year FROM ORIN T0 INNER JOIN RIN1 T1 ON

T0.DocEntry = T1.DocEntry WHERE

T0.[CardCode] = 'cvet01'  GROUP BY T0.[CardName],T0.[DocDate])) S

Pivot (sum(S.total) For  YEAR IN ([2011],[2012],[2013])) P

Thank you

[Former Member]

Use Crystal Reports to group by year, then you can cnompare

[frank_wang6]

SELECT CardCode,

(SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2014-01-01' AND '2014-12-31') Total2014,
(SELECT SUM(DocTotal) FROM OINV WHERE CardCode = T0.CardCode AND CANCELED = 'N' AND DocDate BETWEEN '2014-05-01' AND '2015-12-31') Total2015

FROM OCRD T0
WHERE T0.CardType = 'C'