YTD Sales for 2 years

[Former Member]

Hello forum

I'm trying to create a YTD Sales for 2 years but I'm stuck. Here my query

SELECT TOP 15 T3.SlpName, T2.CardCode AS 'Customer Code', Max(T2.CardName) AS 'Customer Name',

SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) AS 'Total Current Year',

SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) AS 'Total Previous Year'

FROM dbo.JDT1 T0

LEFT JOIN OJDT T1 ON T1.TransID = T0.TransID and T0.TransType IN (13,14)

LEFT JOIN OCRD T2 ON T2.CardCode = T0.ShortName

INNER JOIN OSLP T3 ON T3.SlpCode = T2.SlpCode

WHERE DATEDIFF(DD,T1.RefDate, GETDATE()) > 0 AND

DATEDIFF(DD, T1.RefDate, GETDATE()) < 635

AND  T3.SlpCode IN (1,2,3,38,40,44)

GROUP BY T2.CardCode, T3.SlpName

ORDER BY SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) DESC

Please Advise

[kothandaraman_nagarajan · Accepted solution]

Hi,

Please try this query,

Select [S] as SlpName, [Customer Code] as CustomerCode, [Customer Name] as CustName, [2016],[2015]

from

(

SELECT TOP 15 T3.SlpName as S, T2.CardCode AS 'Customer Code', Max(T2.CardName) AS 'Customer Name',

SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) AS 'Total', Year(T0.Refdate) as Y

FROM JDT1 T0

LEFT JOIN OJDT T1 ON T1.TransID = T0.TransID and T0.TransType IN (13,14)

LEFT JOIN OCRD T2 ON T2.CardCode = T0.ShortName

INNER JOIN OSLP T3 ON T3.SlpCode = T2.SlpCode

WHERE

T3.SlpCode IN (1,2,3,38,40,44)

GROUP BY T2.CardCode, T3.SlpName,T0.Refdate

  )S

pivot

(SUM(Total) FOR Y in([2016],[2015])) P

Thanks

[Former Member · Accepted solution]

Hello

It didn't show me any result. Nothing to show on 2015 and 2016

[kothandaraman_nagarajan · Accepted solution]

Please check your SLP code. Do you have transactions for 2016 and 2015?

[Former Member · Accepted solution]

Yes I have it.

For 1 year I have this

[kothandaraman_nagarajan · Accepted solution]

If you got correct answer, please mark my query as correct answer.

[Former Member · Accepted solution]

But I don't have the solution yet.

Your query shows me NULL Values instead the original.

Any advise ???

[kothandaraman_nagarajan · Accepted solution]

Try this query,

Select [S] as SlpName, [Customer Code] as CustomerCode, [Customer Name] as CustName, [2016],[2015]

from

(

SELECT T3.SlpName as S, T2.CardCode AS 'Customer Code', T2.CardName AS 'Customer Name',

SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) AS 'Total', Year(T0.Refdate) as Y

FROM JDT1 T0

LEFT JOIN OJDT T1 ON T1.TransID = T0.TransID and T0.TransType IN (13,14)

LEFT JOIN OCRD T2 ON T2.CardCode = T0.ShortName

INNER JOIN OSLP T3 ON T3.SlpCode = T2.SlpCode

WHERE

T3.SlpCode IN (1,2,3,38,40,44)

GROUP BY T2.CardCode, T3.SlpName,T0.Refdate,T2.CardName

  )S

pivot

(SUM(Total) FOR Y in([2016],[2015])) P

Thanks

[Former Member · Accepted solution]

Still show me few values.

With the query

SELECT TOP 15 T3.SlpName, T2.CardCode AS 'Customer Code', Max(T2.CardName) AS 'Customer Name',

(select SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0))) AS 'Total Current Year'

FROM dbo.JDT1 T0

INNER JOIN dbo.OJDT T1 ON T1.TransID = T0.TransID and T0.TransType IN (13,14)

INNER JOIN dbo.OCRD T2 ON T2.CardCode = T0.ShortName

INNER JOIN dbo.OSLP T3 ON T3.SlpCode = T2.SlpCode

WHERE DATEDIFF(DD,T1.RefDate, GETDATE()) > 0 AND

DATEDIFF(DD, T1.RefDate, GETDATE()) < 635

AND  T3.SlpCode IN (1,2,3,38,40,44)

GROUP BY T2.CardCode, T3.SlpName

ORDER BY SUM(ISNULL(T0.Debit,0) - ISNULL(T0.Credit,0)) DESC

I get result for all 15 top customers for 1 year. I need to get the same but for the previous year too.

It is possible ?

[kothandaraman_nagarajan · Accepted solution]

Run standard sales analysis report by sale employee from Sales reports. Let me know the result.

[Former Member · Accepted solution]

Sales Analysis doesn't work for me.

I need to get Top 15 Accounts for salesperson for current year and previous year.

The query that I gave you works for current year, but I need to get the same for the previous years.

[Former Member · Accepted solution]

Any other advise ?

[kothandaraman_nagarajan · Accepted solution]

I will check and update.

[Former Member · Accepted solution]

Thanks

Any news?

[Former Member · Accepted solution]

Hello

Any advise?

[Former Member · Accepted solution]

Hello

Any advise?

[kothandaraman_nagarajan · Accepted solution]

Please try same query again without Top function.If result not good, we can find alternative way.

Also refer below threads,

Year on Year Comparison by Customer | SCN