Pageviews

Ads

Wednesday, May 20, 2009

In one of my previous posts I wrote about Calculating Running Totals using a SELF JOIN. But after realizing that, SELF JOIN is not efficient way of calculating Running Totals I thought of writing this post, with another technique without using Cursor.

In this post I'll show you how to Calculate Running Totals without using a cursor. On the same line with my previous post, the different scenarios I’ll be covering in this post are -

Case I : Running Total For all records.Here I will calculate the Running Totals for each records. For that I'll be using a correlated sub query to calculate the Running Totals for the all the records. So the query will be :

SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity ,(SELECTSUM(I.Quantity) FROM Sales I WHERE I.ShipId <= O.ShipId ) as RunningTotal FROM Sales O ORDER BY O.ShipId

Here is the result returned by above query :

ShipId

OrderId

ShipDate

Quantity

RunningTotal

1

1

2009-02-01 12:06:16.820

10

10

2

1

2009-02-02 16:16:16.820

15

25

3

2

2009-02-01 13:26:16.820

20

45

4

2

2009-02-03 17:40:16.820

12

57

5

2

2009-02-04 13:05:16.820

15

72

6

3

2009-02-03 14:25:16.820

25

97

7

3

2009-02-04 15:50:16.820

50

147

8

3

2009-02-05 19:40:16.820

30

177

9

4

2009-02-06 15:30:16.820

20

197

10

4

2009-02-07 16:20:16.820

10

207

11

5

2009-02-05 17:05:16.820

35

242

Case II: Running Total on each Group of Records.Now instead of calculating the running total for each record we will calculate the running total for each OrderId. For that small change in our above query will be addition of one condition in WHERE clause of , that will be - I.OrderId = O.OrderId. So the final query will be:

SELECT O.ShipId, O.OrderId, O.ShipDate, O.Quantity ,(SELECTSUM(I.Quantity) FROM Sales I WHERE I.OrderId = O.OrderId AND I.ShipId <= O.ShipId ) as RunningTotal FROM Sales O ORDER BY OrderId

And the Output :

ShipId

OrderId

ShipDate

Quantity

RunningTotal

1

1

2009-02-01 12:06:16.820

10

10

2

1

2009-02-02 16:16:16.820

15

25

3

2

2009-02-01 13:26:16.820

20

20

4

2

2009-02-03 17:40:16.820

12

32

5

2

2009-02-04 13:05:16.820

15

47

6

3

2009-02-03 14:25:16.820

25

25

7

3

2009-02-04 15:50:16.820

50

75

8

3

2009-02-05 19:40:16.820

30

105

9

4

2009-02-06 15:30:16.820

20

20

10

4

2009-02-07 16:20:16.820

10

30

11

5

2009-02-05 17:05:16.820

35

35

Case III: Running Total on each Date.Now we will see how to calculate the Running Total for each day. For that some small modifications will required in our WHERE clause, observe them carefully and try to understand what I’m trying to do :