September 22, 2017 at 2:00 pm
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!
September 22, 2017 at 6:16 pm
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.
September 23, 2017 at 11:20 pm
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply