[thelist] sql - subtract todays value from yesterdays

[Paul Cowan]

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:

	SELECT
		table1.date,
		table1.amount,
		table1.amount - ISNULL(table2.amount, 0)
	FROM
		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).

Cheers,

Paul