A query for a growth chart

[Curtis Rutland]

I'm working on a chart, x axis is Date, y axis is Count.

Now, I'm no SQL expert by far. I can write a query to get me the count for each date, but the requirements I have is to get the total count at each date.

For example, if the start date was two days ago, with zero entries, then yesterday we get 50, and today we get 25, the resultset would need to look like this:

Date            | Count
2010-03-21      | 0
2010-03-22      | 50
2010-03-23      | 75

I'm really not sure how to approach this query. If anyone has any suggestions, I'd appreciate it.

[ck9663]

If there is a single record for each date:

select y1.datecolumn, y1.count,
counter = y1.count - (select top 1 y2.count from yourtable y2 where y1.datecolumn < y2.datecolumn)
from y1

The above pseudo-code will grab the next record after the current record. Which means, on your sample, if the next record after 21st is 23rd, it will grab the 23rd. If you want it to grab the following day (columnday+1) the above will not work but it will get you started. The result of the query could be negative, so convert as necessary.

If there are multiple records each day, and you need to sum the count before you get the delta, you're going to have to change the sub-query into an aggregate query.

Caution:
1. Make sure you qualify each column weather it belong to y1 or y2.
2. If this is a huge table, say 1M~, you might see some performance issue. Just create the necessary index.

Happy Coding!!!

~~ CK

[Curtis Rutland]

Thanks, that definitely set me off in the right direction. I did have to make the subquery an aggregate, but the subquery part was the logic I was missing.