[thelist] sql - subtract todays value from yesterdays

[Richard Bennett]

Hi,
Sorry for the delay, I got an eye infection right after posting this.

Your solution looks logical and simple. In my first quick try table2 is
returning NULL values though... maybe a date format problem.

Thinking about it though, it seemed more logical to link by a sequential
counter field, that should cover for missing dates...

Thanks for the tip!

Richard.


<----- Original Message -----
<From: "Paul Cowan" <evolt at funkwit.com>
<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).