Monday March 20th 2017

Categories

Insider

Archives

Running Aggregate

An excelent use of sql to produce running aggregate like we do in reporting tools like Crystal Reports. Yhis example is take from Microsoft SQL Server 2008 Database Development self paced traing kit for 70433 by Tobias Rhernstrom,Ann Weber, Mike Hotek and Grand Masters.
We used the database Adventure works in Sql Server 2008 and it produces the out put belowrunning-aggregarte

Select sh3.salespersonid,sh3.orderdate,sh3.dailytotal,SUM(sh4.dailytotal)
runningtotal
from (select sh1.salespersonid,sh1.orderdate,SUM(sh1.totaldue)dailytotal
from sales.SalesOrderHeader sh1
where sh1.salespersonid is not null
group by sh1.salespersonid,sh1.orderdate)sh3
inner join (select sh2.salespersonid, sh2.orderdate,sum(sh2.totaldue)dailytotal
from sales.salesorderheader sh2
where sh2.salespersonid is not null
group by sh2.salespersonid,sh2.orderdate)sh4
on sh3.salespersonid=sh4.salespersonid
and sh3.orderdate>=sh4.orderdate
group by sh3.salespersonid,sh3.orderdate,sh3.dailytotal
order by sh3.salespersonid, sh3.orderdate