Running Total Made Easy with SQL Server 2012


With SQL Server 2012, it is easy to get a running total with the enhanced window function. Here is a solution for a quetion from MSDN T-SQL forum:
http://social.msdn.microsoft.com/Forums/en/transactsql/thread/c7ce7da5-904b-42ca-9cd1-7872635b9de2
–SQL Server 2012
declare @InvBal table (
WeekNumber int,
ProductLineCode char(4),
BrandCode char(4),
ProjectedSalesQty int,
PlannedReceptionsQty int,
ClosingStockQty int,
primary key (WeekNumber, ProductLineCode, BrandCode)
)

declare @i int = 1

while @i<28
begin
insert into @InvBal values (@i, 'L001', 'B001', 10*RAND(), 100*RAND(), null)
insert into @InvBal values (@i, 'L001', 'B002', 10*RAND(), 100*RAND(), null)
insert into @InvBal values (@i, 'L002', 'B003', 10*RAND(), 100*RAND(), null)
insert into @InvBal values (@i, 'L002', 'B004', 10*RAND(), 100*RAND(), null)

set @i = @i + 1;
end

update @InvBal set ClosingStockQty = 1000*RAND() where WeekNumber = 1;

–Query
SELECT WeekNumber,ProductLineCode,BrandCode,ProjectedSalesQty,PlannedReceptionsQty,
SUM(ISNULL(ClosingStockQty, (PlannedReceptionsQty – ProjectedSalesQty )))
OVER ( Partition BY ProductLineCode, BrandCode ORDER BY WeekNumber) AS ClosingStockQty
FROM @InvBal
Order by ProductLineCode, BrandCode, WeekNumber;

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s