I have a table containing three fields: code, date, quantity. I want to know the date, quantity and cumulative quantity for the given code and period also.
What we'll need to do is to add up the quantities on all the rows which have a date that is less than or equal to the date of each row. This means a self-join.
select t1.date , t1.quantity , sum(t2.quantity) as cumulative_qty from yourtable as t1 inner join yourtable as t2 on t2.code = t1.code and t2.date <= t1.date where t1.code = 'PID' group by t1.date , t1.quantity
The self-join will join each row in the table (T1) to all the other rows in the table (T2) for the same code which have an equal or earlier date. Conceptually, for the earliest row, it's joining that row to itself, for the second earliest row, it's joining that row to both itself and the earliest row, for the third earliest row, it's joining that row to both itself and the two earlier rows, etc. By the time you're at the latest row in the table, it's joining that row to itself and all the other rows in the table.
To restrict the results to a specific period, you have to include the period in both the ON clause and the WHERE clause:
select t1.date , t1.quantity , sum(t2.quantity) as cumulative_qty from yourtable as t1 inner join yourtable as t2 on t2.code = t1.code and t2.date <= t1.date and t2.date between '2006-04-01' and '2006-04-30' where t1.code = 'PID' and t1.date between '2006-04-01' and '2006-04-30' group by t1.date , t1.quantity
Is self-joining each row to a number of other rows like this efficient? It can be, with the proper indexes. But there may be a better way to approach the cumulative sum problem. See Running sums, redux for an approach using a cursor. I don't normally even mention using a cursor, because 99% of the time it's less efficient than a set-based solution, but in this case it deserves consideration.
This was first published in June 2006