Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Crosstab query for date Expand / Collapse
Author
Message
Posted Thursday, June 13, 2013 12:53 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 12:30 PM
Points: 310, Visits: 586
I am learning crosstab query and create a sample. It works fine.
Since my real project needs date range is mydate >= (getdate() -15) which will make mydate are different everyday.
How to modify code to meet this condition? That is, column's name for mydate are change everyday.


create table #TestPivot
(
MyValue int,
MyDate datetime
)

insert into #TestPivot values(20,'11/11/2012');
insert into #TestPivot values(30,'11/12/2012');

select * from
(
SELECT [MyValue]
,[MyDate]
FROM #TestPivot
) as sourceTable
Pivot
(
sum([MyValue]) for
[MyDate] in ([2012-11-11],[2012-11-12])
) as PivotTable

result
2012-11-11 ----- 2012-11-12
20---------------30
Post #1463266
Posted Thursday, June 13, 2013 12:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:59 PM
Points: 13,007, Visits: 12,426
adonetok (6/13/2013)
I am learning crosstab query and create a sample. It works fine.
Since my real project needs date range is mydate >= (getdate() -15) which will make mydate are different everyday.
How to modify code to meet this condition? That is, column's name for mydate are change everyday.


You will to use dynamic sql to achieve this. It is probably easier to do this with a crosstab query. Please see the link in my signature about dynamic cross tabs. It explains in great detail how to accomplish this sort of thing.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1463268
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse