asked on

problem with PIVOT

This Query is supposed to return Contract and the sum of the rows for contract for that year.

Can somebody tell me how I can make this work?

DECLARE @START DATE = '2015-01-01'
DECLARE @END DATE = DATEADD(YEAR ,2,DATEADD(DAY,-10,DATEADD(yy, DATEDIFF(yy,0,getdate()) + 1, -1)))

;WITH _DateList([DATE]) AS(
SELECT @START
UNION ALL
SELECT DATES = DATEADD(MONTH,1,[DATE])   
 FROM _DateList 
 WHERE DATEADD(DAY,1,[DATE])<@END
)
SELECT * FROM 
(
SELECT DISTINCT
		'test' = ''
		,[Contract] =						LTRIM(RTRIM(c.Description))
	   --,[Benefit Plan]=	     			bp.longdescription
       -- ,CASE WHEN bp.upid = '007' THEN 'a' ELSE 'b' END  -----??????
		 

FROM dbo.Contract              c
JOIN _DateList
       ON  DATE BETWEEN  c.effdate AND c.termdate
GROUP BY LTRIM(RTRIM(c.Description)),YEAR(DATE)
) TP
PIVOT
(
	COUNT (test)
	FOR [test] IN ([2017], [2018])
) p

Open in new window

Chris Luttrell

Can you share a few rows of sample data and what you are wanting for the output so we can be clear on the expected results?

vbnetcoder

ASKER

Contract 1/2018 2/2018 3/2018
acbfdf 12 52 5
contr2 2 32 23

ETC. for the whole year.

Basically I want to return the last 12 months like that for today's date

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vbnetcoder

ASKER