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:
>> 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"; 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?
HTH,
John
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
