Crosstab query for date

  • 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

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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