Running Sums the sequel part x
If you've been following the trillogy (Adams posts 1,2 Jamies post ) of attempts at getting a running sum out of TSQL you will appreciate that there are many ways to solve a problem. Following my previous post on partitions and aggregates I thought that might be an avenue. Well it is, and i've thrown in a use of the unpivot as well. I know this could have been written using CASE statements, but that wasn't the point.
If anyone can come up with a better solution then let me know.
DBCC
DROPCLEANBUFFERS -- Clears the data cache
DBCC
FREEPROCCACHE -- Clears the procedure cache
GO
declare
@start datetime
set
@start = getdate()
;
with agg
as
(
SELECT
year(OrderDate) year
,
month(orderdate) month
,
SUM(D.LineTotal) OrderTotal
,
row_number() over( partition by year(OrderDate) order by year(OrderDate)
,
month(orderdate) )row
From
Sales.SalesOrderHeader H
JOIN
Sales.SalesOrderDetail D ON D.SalesOrderId = H.SalesOrderId
WHERE
TerritoryID IS NOT NULL
group
by year(OrderDate)
,
month(orderdate))
,
temp
as
(select year, month, row, aggrow, ordertotal, orders
from
(
select
year, month, row, ordertotal
,
sum(ordertotal) over (partition by year, row/2) [1]
,
sum(ordertotal) over (partition by year, row/3) [2]
,
sum(ordertotal) over (partition by year, row/4) [3]
,
sum(ordertotal) over (partition by year, row/5) [4]
,
sum(ordertotal) over (partition by year, row/6) [5]
,
sum(ordertotal) over (partition by year, row/7) 
,
sum(ordertotal) over (partition by year, row/8) [7]
,
sum(ordertotal) over (partition by year, row/9) 
,
sum(ordertotal) over (partition by year, row/10) [9]
,
sum(ordertotal) over (partition by year, row/11) [10]
,
sum(ordertotal) over (partition by year, row/12) [11]
,
sum(ordertotal) over (partition by year, row/13) [12]
from
agg) p
unpivot
(orders for aggrow in(
[1]
,
[2]
,
[3]
,
[4]
,
[5]
,
,
[7]
,
,
[9]
,
[10]
,
[11]
,
[12]
)) as unpvt
where
row = aggrow)
select
year, month, ordertotal, orders from temp
order
by year, row, cast(aggrow as int)
select
cast(datediff(ms, @start, getdate()) as varchar(20)) [time taken]
-