[thelist] sql - subtract todays value from yesterdays

Paul Cowan evolt at funkwit.com
Mon Dec 22 05:03:50 CST 2003

Richard Bennett wrote on 22/12/2003 11:48 AM +0100:
> What I have is this:
> SELECT     [Date] , [Amount],
> [Amount] - CASE WHEN [Date] = DATEADD(day, -1, [Date])
> THEN [Amount] ELSE 0 END AS 'Used',
> FROM         table
> Which doesn't get yesterdays 'Amount' value.

If you need to combine two rows of the input table into one row of
the output table, you'll need to join the table onto itself -- matching
each row up with its corresponding "day before row".

That's probably as clear as mud, so try this:

		table1.amount - ISNULL(table2.amount, 0)
		table AS table1
	LEFT JOIN table AS table2 ON
		(table2.date = dateadd(day, -1, table1.date))

That should do what you're after -- play around with it a bit and it
should make a bit more sense.

Note that this will fail if there are any days missing from the table --
if so, the query will get more complicated (but it's still do-able).



More information about the thelist mailing list