> 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<dbic...@yahoo.com> 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 > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users