Using Start and End dates to create a number of months column

  • Among several other things.

    Honestly, it's one of the things that is 'cool', but I haven't figured out what a practical need would be for it yet. Here's the query I was playing around with today and the results (since you can't run it).

    IF OBJECT_ID('TempDB..#GST','U') IS NOT NULL DROP TABLE #GST

    CREATE TABLE #GST(

    Cust_Numvarchar(10),

    CallTypevarchar(10),

    Open_Datedatetime,

    Amountdecimal(9,2))

    INSERT INTO #GST (Cust_Num, CallType, Open_Date, Amount)

    VALUES ('AAAAAA','SERVICE','1/1/2006',100),

    ('AAAAAA','ESTIMATE','1/1/2007',100),

    ('AAAAAA','PM','1/1/2008',100),

    ('AAAAAA','SERVICE','1/1/2006',100),

    ('AAAAAA','ESTIMATE','1/1/2007',100),

    ('AAAAAA','PM','1/1/2008',100),

    ('AAAAAA','SERVICE','1/1/2009',100),

    ('BBBBBB','SERVICE','1/1/2006',100),

    ('BBBBBB','ESTIMATE','1/1/2008',100),

    ('BBBBBB','PM','1/1/2009',100),

    ('BBBBBB','SERVICE','1/1/2005',100),

    ('CCCCCC','ESTIMATE','1/1/2005',100),

    ('BBBBBB','PM','1/1/2009',100),

    ('BBBBBB','SERVICE','1/1/2005',100),

    ('CCCCCC','ESTIMATE','1/1/2005',100),

    ('BBBBBB','PM','1/1/2009',100),

    ('BBBBBB','SERVICE','1/1/2005',100),

    ('CCCCCC','ESTIMATE','1/1/2005',100),

    ('CCCCCC','SERVICE','1/1/2008',100),

    ('CCCCCC','SERVICE','1/1/2009',100)

    SELECT Cust_Num, CallType, YEAR(Open_Date) ODYear, SUM(Amount) Total,

    CASEWHEN Cust_Num IS NOT NULL AND CallType IS NULL AND YEAR(Open_Date) IS NULL THEN '3 Customer Total'

    WHEN CallType IS NOT NULL AND Cust_Num IS NULL AND YEAR(Open_Date) IS NULL THEN '4 CallType Total'

    WHEN YEAR(Open_Date) IS NOT NULL AND Cust_Num IS NULL AND CallType IS NULL THEN '5 ODYear Total'

    WHEN Cust_Num IS NOT NULL AND CallType IS NOT NULL AND YEAR(OPEN_Date) IS NULL THEN '1 CustType Total'

    WHEN Cust_Num IS NOT NULL AND CallType IS NOT NULL AND YEAR(OPEN_Date) IS NOT NULL THEN '2 CustTypeYear Total'

    ELSE '6 Grand Total'

    END TotalType

    FROM #GST

    GROUP BY GROUPING SETS(

    (Cust_Num),

    (Cust_Num, CallType),

    (Cust_Num, CallType, YEAR(Open_Date)),

    (YEAR(Open_Date)),

    (CallType),

    ())

    ORDER BY TotalType, Cust_Num

    Results:

    AAAAAAESTIMATENULL200.001 CustType Total

    AAAAAAPMNULL200.001 CustType Total

    AAAAAASERVICENULL300.001 CustType Total

    BBBBBBPMNULL300.001 CustType Total

    BBBBBBESTIMATENULL100.001 CustType Total

    BBBBBBSERVICENULL400.001 CustType Total

    CCCCCCSERVICENULL200.001 CustType Total

    CCCCCCESTIMATENULL300.001 CustType Total

    AAAAAAESTIMATE2007200.002 CustTypeYear Total

    AAAAAAPM2008200.002 CustTypeYear Total

    AAAAAASERVICE2006200.002 CustTypeYear Total

    AAAAAASERVICE2009100.002 CustTypeYear Total

    BBBBBBSERVICE2005300.002 CustTypeYear Total

    BBBBBBSERVICE2006100.002 CustTypeYear Total

    BBBBBBPM2009300.002 CustTypeYear Total

    BBBBBBESTIMATE2008100.002 CustTypeYear Total

    CCCCCCESTIMATE2005300.002 CustTypeYear Total

    CCCCCCSERVICE2008100.002 CustTypeYear Total

    CCCCCCSERVICE2009100.002 CustTypeYear Total

    AAAAAANULLNULL700.003 Customer Total

    BBBBBBNULLNULL800.003 Customer Total

    CCCCCCNULLNULL500.003 Customer Total

    NULLSERVICENULL900.004 CallType Total

    NULLESTIMATENULL600.004 CallType Total

    NULLPMNULL500.004 CallType Total

    NULLNULL2005600.005 ODYear Total

    NULLNULL2006300.005 ODYear Total

    NULLNULL2007200.005 ODYear Total

    NULLNULL2008400.005 ODYear Total

    NULLNULL2009500.005 ODYear Total

    NULLNULLNULL2000.006 Grand Total

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. πŸ˜‰

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Garadin (3/5/2010)


    Honestly, it's one of the things that is 'cool', but I haven't figured out what a practical need would be for it yet. Here's the query I was playing around with today and the results (since you can't run it).

    I'll sometimes use such a thing (ie. WITH ROLLUP) to preaggregate data for a report. Think of it as a "mini-datamart".

    --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)

  • Chris,

    Wherever did you get the idea to use an alias name of iTVF?

    :laugh:

    Paul

  • Paul White (3/6/2010)


    Chris,

    Wherever did you get the idea to use an alias name of iTVF?

    :laugh:

    Paul

    Hey Paul, it was from a great article I was reading last week - about transgenderism πŸ˜€

    β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Chris Morris-439714 (3/8/2010)


    Hey Paul, it was from a great article I was reading last week - about transgenderism πŸ˜€

    :laugh: Very funny!

Viewing 5 posts - 16 through 19 (of 19 total)

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