Opposite of Running Total (Running Difference?)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Opposite of Running Total (Running Difference?)

Author

Topic

stannius
Starting Member

10 Posts

Posted - 2004-02-13 : 13:05:30

I have a database which contains a bunch of year-to-date revenue data at the end of some(though often not all) months. For example:

Member report_date_from report_date_to ytd_revenue
100 2002-01-01 00:00:00.000 2002-04-30 00:00:00.000 9492891.2500
100 2002-01-01 00:00:00.000 2002-08-31 00:00:00.000 11514479.2500
100 2002-01-01 00:00:00.000 2002-09-30 00:00:00.000 12132443.2500
100 2002-01-01 00:00:00.000 2002-10-31 00:00:00.000 12408736.2500
100 2002-01-01 00:00:00.000 2002-11-30 00:00:00.000 12396812.2500
100 2002-01-01 00:00:00.000 2002-12-31 00:00:00.000 12710908.2500

This is an example of a subset of the data; there are other members in there too.

What I want to do is figure out another column, period_revenue, which is the difference between the ytd_revenue of a record and the most recent record before that. I have found examples of how to calculate the running total but not how to subtract it like this.

Thanks,
steve

stannius
Starting Member

10 Posts

Posted - 2004-02-13 : 15:34:36

I came up with one potential solution, but it's slow and at i'm not sure it's right:

SELECT
rpt_date_from,
rpt_date_to,
Revenue Tota],
(y1.[Revenue Total] -
(select y2.[Revenue Total]
FROM ytddatatable y2
WHERE y2.[Member Number] = y1.[Member Number] AND
y2.rpt_date_to =
(select MAX(y3.rpt_date_to)
FROM ytddatatable y3
WHERE y2.[Member Number] = y3.[Member Number] AND
y3.rpt_date_to < y1.rpt_date_to)))
AS [Revenue for Period]
FROM ytdddatatable y1

It seems like I should be able to eleminate the two levels of nested selects, since the innermost select at least in theory uniquely identifies the previous period's record.

-steve

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-13 : 16:38:35

or

,
revenue-(select top 1 revenue from y y2 where
y2.mem=y1.mem and y2.date<y1.date order by y2.date desc)

from y1

stannius
Starting Member

10 Posts

Posted - 2004-02-17 : 14:18:56

quote:
Originally posted by Stoad

or

,
revenue-(select top 1 revenue from y y2 where
y2.mem=y1.mem and y2.date<y1.date order by y2.date desc)

from y1

Thanks, that did the trick, much faster now

stannius
Starting Member

10 Posts

Posted - 2004-02-17 : 17:30:14

quote:
Originally posted by Stoad

or

,
revenue-(select top 1 revenue from y y2 where
y2.mem=y1.mem and y2.date<y1.date order by y2.date desc)

from y1

Another question - what if I want two fields from the most recent previous record? I.e., in the new data set I want to set rpt_date_from = previousrecord.rpt_date_to + 1 day, and also get the revenue info. I can't select two fields in the inner select and I can't use the top 1 syntax in a join.

thanks
steve

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources