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
