CODEWITHSUNDEEP
sql-server-2008datepivot
LisaRieken
LisaRieken

Reputation: 29

Pivot with changing dates

I have a script with result set something like this:

-- #Test (temptable)
Branch_Name   C_Date     Percentage
Branch 1      20140107   90
Branch 1      20140108   82
Branch 2      20140107   85
Branch 2      20140108   86

I would like to pivot this data, however the C_Date is populated to get 7 days back:

WHERE     (1 = 1) AND
       (tTable.C_Date > CONVERT(VARCHAR(10),GETDATE() -7, 112)) AND
       (tEnter.C_Date < CONVERT(VARCHAR(10),GETDATE() -1, 112)).

I've tried

Select * from #Test
pivot (avg (Percentage) for C_date in ([20140107],[20140108])) as PivotTable

and it gives me the data I want (see below), 

Branch_Name   20140107   20140108
Branch 1      90         82
Branch 2      85         86

but how do I get the pivot to look at dates populated by the GETDATE? I've tried putting the GETDATE command in each [] but that obviously didn't work. ** Note, my example shows 2 days, but my query is for 7 days back, not including the day it's being run.

Any help appreciated - thank you!

Upvotes: 1

Views: 94

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

Instead of trying to work with the dates, try working with "the number of days between C_Date and today".

So early on (in a subquery or CTE) you do DATEDIFF(day,C_Date,GETDATE()) as NumDays.

You can then filter your where as WHERE NumDays between 1 and 7 and your pivot as:

pivot (avg (Percentage) for NumDays in ([1],[2],[3],[4],[5],[6],[7])) as PivotTable

Now, that handles most of what you need. The one thing we can't do (in plain SQL) is to convert those column names back into dates - because any particular SQL query has to produce a result set with a fixed "shape" - the number, names and types of the columns are fixed.

But hopefully that's enough to do in SQL, and if you do need to convert back into date headings, that can be done with whatever is consuming this result set.

Upvotes: 5

Related Questions