I suspect the answer is that it is best to do this in the application program. However, the platform I want to use is dumb as a brick. It basically can call sqlite3_get_table, mildly reformat the data and send it to the display.
Anyway, there are two tables CREATE TABLE Goals (period integer primary key, amount int); CREATE TABLE Data (period int, type text, amount int); periods above are 1 to 12, and amounts are zero or positive. INSERT INTO Goals Values (1,10), (2,10), (3,10); INSERT INTO Data Values (1,'A',3), (2, 'A', 5) (2, 'B', 6), (3, 'A', 2); Okay, last time I tried to send a list to the group it came out unreadable. Crossing fingers: PERIOD | TYPE | AMOUNT 1 | A | 3 1 | UNK | 7 -- the goal of 10 - data of 3 2 | A | 5 2 | B | 6 2 | UNK | 0 -- goals of 1&2 = 20 - previous lines of 21 -- with the negative result changed to 0 3 | A | 2 3 | UNK | 7 -- goals 1-3 = 30 - previous lines of 23 Hopefully that makes sense. I need to insert a row into the output that ends each period at or above the accumulated goals. A further complication. The "UNK" row only gets inserted for the current or future periods. (i.e in two weeks it will be February, so the January UNK will be removed and the Feb UNK would become 6 (20 - 3 - 11). The best I got is select period, type, amt from Data UNION select period, 'UNK', (select sum(amount) from Goals G where g.period <= goals.period) -(select sum(amount) from Data D where d.period <= goals.period) from Goals; But it doesn't pick up the calculated "UNK" values from the prior periods. nor does it deal with negatives or not calculating UNK from periods in the past. So can this report be done within a query? Thanks, David _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
