Post reply

PIVOT on months

  • December 30, 2013 at 3:26 pm

    #286679

    Hi,

    I was wondering if there is a way to do the PIVOT to get the data for last 14 Months.

    In my below query, I can get the Passenger Counts for 12 months (from Jan2013 - Dec 2013)

    DECLARE @StartDate as datetime

    ,@EndDate as datetime

    SET @StartDate = '1/1/2013'

    SET @EndDate = '12/30/2013'

    SELECT *

    FROM

    (

    SELECT DATENAME(MONTH, TravelDateTime) as Months

    , COUNT(*) as TotalPassengerBooked

    FROM dbo.TravelDetails

    GROUP BY DATENAME(MONTH, TravelDateTime)

    ) as M

    PIVOT

    (

    MAX(TotalPassengerBooked) FOR Months IN ( [January],[February],[March],[April],[May]

    ,[June],[July],[August],[September]

    ,[October],[November],[December])

    ) as piv;

    Now the client is asking to see this trend for the LATEST 14 months of data whenever they run this report. So, if the @EndDate = '1/15/2014' then they should see the data from DEC 2012 (dec 1st) till JAN 2014(jan 15th), grouped by Months.

    It would be nice if we could also do the ORDER BY the latest months.

    Any guidance is much appreciated.

    Thanks.

  • December 30, 2013 at 4:05 pm

    #1676501

    You'll have to go to dynamic SQL to pull this off.

    This article will point you at the generic idea:

    https://www.simple-talk.com/blogs/2007/09/14/pivots-with-dynamic-columns-in-sql-server-2005/

    Also, you'll need to name your columns something like Apr13 and Apr12 so that they can split properly, which you'll have to account for as a source field in the inbound data.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 30, 2013 at 9:19 pm

    #1676510

    Quite naturally, I like the following article on Dynamic Pivots better. 😉

    http://www.sqlservercentral.com/articles/Crosstab/65048/

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

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

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