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