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.

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: