Trying to add prior year column to this query.

Question

Trying to add prior year column to this query.

mjakachira

SSC Veteran

Points: 215
More actions
April 1, 2016 at 12:35 am

#309635

Here is my code. The results come in the form of 2 columns one product descriptions and the other the YTD sums for those products. I want to add the previous YTD as the next column.
SELECT *
INTO #TEMP
FROM
(SELECT DISTINCT
A.INVC_NUMB
,CONVERT(CHAR(10), DATEADD(d, A.INVC_RDAT +5843, 0), 1) [INVC_DATE]
from JR80 A
WHERE YEAR(CONVERT(CHAR(10), DATEADD(d, A.INVC_RDAT +5843, 0), 1)) = YEAR(GETDATE())
GROUP BY
A.INVC_NUMB
,CONVERT(CHAR(10), DATEADD(d, A.INVC_RDAT +5843, 0), 1)
) AS X
SELECT
/*DATENAME(MM, B.INVC_DATE)[INVC_MTH]
,MONTH(B.INVC_DATE)[MONTH_NUMB]
,*/C.PROD_DESC
,SUM(CAST(A.SALE_PRICE1 AS MONEY))[YTD INVOICE]
FROM JR91 A
LEFT OUTER JOIN #TEMP B ON A.INVC_NUMB = B.INVC_NUMB
LEFT OUTER JOIN JC17 C ON A.PROD_CODE = C.CODE
WHERE
YEAR(B.[INVC_DATE]) = YEAR(GETDATE())
AND A.LINE_NUMB <> ''
AND A.PROD_CODE <> ''
AND A.LINE_NUMB <> 'TOT'
GROUP BY
/*DATENAME(MM, B.INVC_DATE)
,MONTH(B.INVC_DATE)
,*/C.PROD_DESC
DROP TABLE #TEMP

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

I really shouldn't be posting any code without being able to test it. But it seemed so cumbersome that I couldn't help myself.

You shouldn't use functions on your columns in WHERE clauses, you'll only prevent that indexes can be used against them.

Here's my approach that might be what you're looking for. Basically, you need to include all the invoices from this year and the previous one. Then use cross tabs to define to which column they belong.

SELECT DISTINCT

A.INVC_NUMB

,DATEADD(d, A.INVC_RDAT +5843, 0) [INVC_DATE]

INTO #TEMP

FROM JR80 A

WHERE A.INVC_RDAT >= DATEDIFF( DD, '19160101', DATEADD(YY, DATEDIFF( YY, 0, GETDATE())-1, 0))

AND A.INVC_RDAT < DATEDIFF( DD, '19160101', DATEADD(YY, DATEDIFF( YY, 0, GETDATE())+1, 0));

SELECT

C.PROD_DESC

,CAST( SUM( CASE WHEN YEAR(B.[INVC_DATE]) = YEAR(GETDATE()) THEN A.SALE_PRICE1 END) AS MONEY) [YTD INVOICE]

,CAST( SUM( CASE WHEN YEAR(B.[INVC_DATE]) = YEAR(GETDATE()) - 1 THEN A.SALE_PRICE1 END) AS MONEY) [PREV YEAR INVOICE]

FROM JR91 A

LEFT OUTER JOIN #TEMP B ON A.INVC_NUMB = B.INVC_NUMB

LEFT OUTER JOIN JC17 C ON A.PROD_CODE = C.CODE

WHERE A.LINE_NUMB <> ''

AND A.PROD_CODE <> ''

AND A.LINE_NUMB <> 'TOT'

GROUP BY

C.PROD_DESC;

DROP TABLE #TEMP;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2