On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote: > On 25/07/2009 6:17 AM, David Bicking wrote: > > That works. Thanks! > > It struck me that Pavel's revised query didn't mention the d2 column at > all, only d1: >
Thanks for the additional info. In real life, I added WHERE d1='X' OR D2='X' to the inner subqeury. Yes, there are records where neither are 'X' and they are to be ignored. > >> sum(case when d1='X' then 1 else -1 end) as act_sum, > >> sum(case when d1='X' then amt else -amt end) as net > > ... backtracking, it seems that you haven't stated anything about the > possibility of having 'X' in neither d1 nor d2. You may like to program > this along more defensive lines: > > sqlite> create table source (id integer, d1 text, d2 text, amt int); > sqlite> insert into source values(1, 'X', 'Y', 15); > sqlite> insert into source values(2, 'X', 'Z', 6); > sqlite> insert into source values(2, 'A', 'X', 7); > sqlite> insert into source values(3, 'B', 'X', 12); > sqlite> insert into source values(4, 'C', 'W', 99); > sqlite> .headers on > sqlite> select id, > ...> case kind > ...> when 1 then 'NEW' > ...> when 2 then 'DROP' > ...> when 3 then 'CHANGE' > ...> else 'WHOOPS' > ...> end as Action, > ...> net > ...> from ( > ...> select id, > ...> sum((d1='X') + (d2='X') * 2) as kind, > ...> sum(((d1 = 'X') - (d2 = 'X')) * amt) as net > ...> from source > ...> group by id > ...> ) > ...> order by 2 desc > ...> ; > id|Action|net > 4|WHOOPS|0 > 1|NEW|15 > 3|DROP|-12 > 2|CHANGE|-1 > sqlite> select id, > ...> case when act_sum = 1 then 'NEW' > ...> when act_sum = 0 then 'CHANGE' > ...> else 'DROP' > ...> end as Action, > ...> net > ...> from > ...> ( > ...> select id, > ...> sum(case when d1='X' then 1 else -1 end) as act_sum, > ...> sum(case when d1='X' then amt else -amt end) as net > ...> from Source > ...> group by id > ...> ) > ...> order by 2 desc; > id|Action|net > 1|NEW|15 > 3|DROP|-12 > 4|DROP|-99 > 2|CHANGE|-1 > sqlite> > > An accounting system where the sign of the amount is detached and has to > be obtained from another column is tedious and error-prone; obtaining it > from TWO columns is "interesting"; You must have never dealt with SAP. The values are all positive, and in another column they have H or S to say if it is positive or negative. (H and S being the initials of Credit and Debit in German. Or maybe those initials are used because I work for the US division of a German company. Not entirely sure.) > a system where the nature of the > transaction or event is not recorded and has to be reverse-engineered > from circumstantial evidence is also "interesting" (especially where the > evidence spans more than one row) -- is this an existing system, or is > it a prototype that you could reconsider? > The values are always what they are in the amt column, but this report is tracking changes for a given date. d1 and d2 are starting and ending dates. If you have an id that starts on a given date, but has no prior record ending on that date, then it is brand new. If one ends on a given date but there is no newer record starting, then the item was dropped, and represents a decrease in the total. If an item end son one date, and picks up with a new value, then it changed. The format seems nice and compact. You can get the detail for any given date by selecting WHERE target>=d1 and target<d2. And since items tend to stay the same value over a long period of time, there is only one record in the table to cover it. I was extracting the values for the target day and the day before into a second table, and comparing the values, but I realized that I could grab both values in one go by looking for the common dates in d1 and d2. I just couldn't figure how to get the descriptive Action of New, Drop or Change since multiple rows are involved. Given that I have used a similar trick in calculating the "kind" value many times, I have been kicking myself that I didn't see using it here. I'm open to suggestions for other ways to implement this. David _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
