Running total challenge - SORT OF

  • Hello ladies and gents!

    I have a task that has boggled me for a few days now. What the client needs is a Yearly/Quarterly summation of their overall square footage (SF) over all their stores. BUT they want to know their SF at the beginning of each quarter (BOQ), each quarter increase (Increase) and at the end of each Quarter (EOQ).

    For our purposes the data starts 2012 Q2, the BOQ will always start as 0. 

    I have attached a sample excel file with test data and how the output should return. 

    ANY help, hints or tricks are much appreciated!

  • Rather than providing an Excel document, could you provide it in a consumable format please? Have a look at the link in my signature.

    Thanks.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • You need to make sure you provide results as well.

    The description isn't that clear. I'm not sure you want a running total, but you want some summary, right? Or do you need a change by quarter? In other words,  Store 2 opens w/ 5000 ft. If that's the only transaction, I assume you mean you need to know that 0 was the start, the change was 5000 and the end was 5000. Is that one row, or do you need to see this as:


    Store Date Change Value
    2  BOQ 0  0
    2  MidQ 5000 5000
    2  EOQ 5000 5000
    2  Q2 0  5000
    2  chg 103  5103
    2  chg2 200  5303
    2  EQ2 303  5303

    It isn't quite clear what you starting data is and what you want calculated.

  • This should get you what you're looking for...
    -- test data from spreadsheet...
    IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL
    DROP TABLE #TestData;

    CREATE TABLE #TestData (
        Record INT NOT NULL,
        fiscalyear INT NOT NULL,
        fiscalquarter INT NOT NULL,
        changetype VARCHAR(10) NOT NULL,
        Increase INT NOT NULL,
        NewSqftTotal INT NOT NULL
        );
    INSERT #TestData (Record, fiscalyear, fiscalquarter, changetype, Increase, NewSqftTotal) VALUES
        (145, 2012, 2, 'Open', 5000, 5000),
        (117, 2012, 3, 'Open', 5000, 5000),
        (140, 2012, 3, 'Remodel', 103, 103),
        (142, 2012, 3, 'Open', 5000, 5000),
        (35, 2012, 3, 'Open', 5000, 5000),
        (6, 2012, 4, 'Open', 5000, 5000),
        (78, 2013, 1, 'Open', 5000, 5000),
        (6, 2013, 2, 'Close', -5000, -5000),
        (51, 2013, 3, 'Open', 5000, 5000),
        (145, 2013, 4, 'Remodel', 107, 107),
        (142, 2013, 4, 'Remodel', 105, 105),
        (62, 2013, 4, 'Open', 5000, 5000),
        (134, 2014, 1, 'Open', 5000, 5000),
        (105, 2014, 2, 'Open', 5000, 5000);

    The solution...
    WITH
        cte_AggYQ AS (
            SELECT
                td.fiscalyear,
                td.fiscalquarter,
                TotalIncrease = SUM(td.Increase)
            FROM
                #TestData td
            GROUP BY
                td.fiscalyear,
                td.fiscalquarter
            UNION ALL
            SELECT 2014, 3, 0 UNION ALL -- manually adding missing Q 3 & 4 for 2014...
            SELECT 2014, 4, 0
            ),
        cte_RunningTotals AS (
            SELECT
                ayq.fiscalyear,
                ayq.fiscalquarter,
                ayq.TotalIncrease,
                BOQ = ISNULL(SUM(ayq.TotalIncrease) OVER (ORDER BY ayq.fiscalyear, ayq.fiscalquarter ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0),
                EOQ = SUM(ayq.TotalIncrease) OVER (ORDER BY ayq.fiscalyear, ayq.fiscalquarter ROWS UNBOUNDED PRECEDING)
            FROM
                cte_AggYQ ayq
            )
    SELECT
        rt.fiscalyear,
        rt.fiscalquarter,
        v.Lable,
        v.DataValue
    FROM
        cte_RunningTotals rt
        CROSS APPLY ( VALUES (1, 'BOQ', rt.BOQ), (2, 'INCREASE', rt.TotalIncrease), (3, 'EOQ', rt.EOQ) ) v (SortBy, Lable, DataValue)
    ORDER BY
        rt.fiscalyear,
        rt.fiscalquarter,
        v.SortBy;

    Results...
    fiscalyear fiscalquarter Lable  DataValue
    ----------- ------------- -------- -----------
    2012   2     BOQ    0
    2012   2     INCREASE 5000
    2012   2     EOQ    5000
    2012   3     BOQ    5000
    2012   3     INCREASE 15103
    2012   3     EOQ    20103
    2012   4     BOQ    20103
    2012   4     INCREASE 5000
    2012   4     EOQ    25103
    2013   1     BOQ    25103
    2013   1     INCREASE 5000
    2013   1     EOQ    30103
    2013   2     BOQ    30103
    2013   2     INCREASE -5000
    2013   2     EOQ    25103
    2013   3     BOQ    25103
    2013   3     INCREASE 5000
    2013   3     EOQ    30103
    2013   4     BOQ    30103
    2013   4     INCREASE 5212
    2013   4     EOQ    35315
    2014   1     BOQ    35315
    2014   1     INCREASE 5000
    2014   1     EOQ    40315
    2014   2     BOQ    40315
    2014   2     INCREASE 5000
    2014   2     EOQ    45315
    2014   3     BOQ    45315
    2014   3     INCREASE 0
    2014   3     EOQ    45315
    2014   4     BOQ    45315
    2014   4     INCREASE 0
    2014   4     EOQ    45315

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

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