Previous Row T-SQL (Part1/2)

Calculating running totals, difference from the previous row are common problems. The tricky part to resolve these problems is to be able to link to the previous row. This first part of a two part series in which i discuss options to solve this probelm. I start with running totals.

Test Data:

For this post i use the following script to create and populate sample data. Note that i use row constructors introduced in SQL Server 2008. Below script won’t work as is in version prior to 2008 unless inserted using multiple insert statements.

CREATE TABLE #YrSales
(
Yr INT
, SaleAmount INT
)

INSERT #YrSales
VALUES
(2007,15000)
,(
2008,10000)
,(
2009,25000)
,(
2010,20000)
,(
2011,18000)

How to calculate running totals:

Using Sub Query:

Following query uses a correlated sub query inside the select clause. Correlated sub query executes once for each row returned by the outer query. I originally learned this method from an article by Itzik Ben-Gan, who obviously doesn’t need any introduction.

SELECT
Yr, SaleAmount
,RunningTotal = (SELECT SUM(SaleAmount) FROM #YrSales WHERE Yr<= A.Yr)
FROM
#YrSales A

Suppose the first row returned is Yr = 2009; SaleAmount = 25000. Sub query performs a sum of SaleAmount for all the years less than or equals 2009 and returns 50000as RunningTotal. This continues until there are no more rows returned by the outer query.

Using Cross Join:

Using cross join is an efficient way especially when dealing with larger data sets. Cross joining the table with itself causes every row from the left table (a) to be joined to every row in the right table (b). Where clause ensures that join is performed only when Yr from the right table (b) is less than yr from the left table (a). Yr, SaleAmount from left table (a) in the select clause return Yr and SaleAmount. Sum(b.SaleAmount) returns running total.

SELECT a.Yr,
a.SaleAmount
,SUM(b.SaleAmount) AS RunningTotal
FROM #YrSales a
CROSS JOIN #YrSales b
WHERE (b.Yr <= a.Yr)
GROUP BY a.Yr,a.SaleAmount
ORDER BY a.Yr,a.SaleAmount

To better understand, execute the following queries one at a time:

SELECT
a.Yr,
a.SaleAmount
FROM
#YrSales a
CROSS JOIN
#YrSales b

SELECT
a.Yr,
a.SaleAmount
FROM
#YrSales a
CROSS JOIN
#YrSales b
GROUP BY
a.Yr,a.SaleAmount

SELECT
a.Yr,
a.SaleAmount
,SUM(b.SaleAmount) RunningTotal
FROM
#YrSales a
CROSS JOIN
#YrSales b
WHERE (b.Yr <= a.Yr)
GROUP BY
a.Yr,a.SaleAmount
ORDER BY
a.Yr,a.SaleAmount

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