> And how can I calculate the value for Action?
Just do the same approach with CASE you've already used:
Select ID,
CASE WHEN d1='X' AND d2 IS NULL THEN 'NEW'
WHEN d1 IS NULL AND d2='X' THEN 'DROP'
ELSE 'CHANGE' END AS Action,
Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) - Sum(CASE WHEN d2='X' THEN
AMT ELSE 0 END) AS Net
FROM Source GROUP BY ID ORDER BY 2 DESC;
Pavel
On Fri, Jul 24, 2009 at 2:17 PM, David Bicking<[email protected]> wrote:
> I am trying to create a report. The source table can have one or two records
> for each given ID. If there is a record with D1='X' but no record with
> D2='X', then it is a "NEW" action. If there are both D1 and D2 record, it is
> a "CHANGE" and the Net amount is the D1 amount less the D2 amount. If there
> is no D1, then it is a "DROP" and is the negative of the D2 amount.
>
> I can get close, but I have no idea how to calculate the Action words.
>
> Select * from Source
>
> ID | D1 | D2 | AMT
> 1 | X | | 15
> 2 | X | | 6
> 2 | | X | 7
> 3 | | X | 12
>
> Select ID, ??? AS Action, Sum(CASE WHEN d1='X' THEN AMT ELSE 0 END) -
> Sum(CASE WHEN d2='X' THEN AMT ELSE 0 END) AS Net FROM Source GROUP BY ID
> ORDER BY 2 DESC;
>
> ID | Action | Net
> 1 | NEW | 15
> 3 | DROP | -12
> 2 | CHANGE | -1
>
> Is the approach I am taking a "good" approach? And how can I calculate the
> value for Action?
>
> Thanks,
> David
>
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
