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

vbnetcoderAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
The problem with PIVOT is you need to know what the resulting columns are going to be.

They have to be hardcoded - or - you can use dynamic SQL to formaat.

With only 12 columns, it isnt too much of a problem, because you simply type them in - not so good for automating the report process, but that is what MS SQL offers as their PIVOT

So, for your code, you can do (using 2017 as our base)
DECLARE @START DATE = '2017-01-01'
DECLARE @END DATE = DATEADD(month ,11,@start)

;WITH _DateList AS
( SELECT @START as [DATE]
  UNION ALL
  SELECT DATES = DATEADD(MONTH,1,[DATE])   
  FROM _DateList 
  WHERE DATEADD(month,1,[DATE])<=@END
)
SELECT * from  
  ( select c.contract, 1 as quantity, right(convert(varchar(10),p.[date],103),7) [date] 
    FROM dbo.Contract c
    cross apply (select [DATE] from _datelist where [date] between c.effdate AND c.termdate ) src
PIVOT
  ( count(quantity) for [date] in ([01/2017],[02/2017],[03/2017],[04/2017],[05/2017],[06/2017],[07/2017],[08/2017],[09/2017],[10/2017],[11/2017],[12/2017])) pvt

Open in new window


Now, you can always explore Dynamic SQL to fulfill whatever date ranges you care to enter...

I have written and Article about it :  https://www.experts-exchange.com/articles/653/Dynamic-Pivot-Procedure-for-SQL-Server.html
0
 
Chris LuttrellSenior Database ArchitectCommented:
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?
0
 
vbnetcoderAuthor Commented:
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
0
 
vbnetcoderAuthor Commented:
ty
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.