Re: [sqlite] Reporting on summary data

On 25/07/2009 11:59 AM, David Bicking wrote: > On Sat, 2009-07-25 at 10:33 +1000, John Machin wrote: >> 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"; >

Re: [sqlite] Reporting on summary data

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'

Re: [sqlite] Reporting on summary data

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

Re: [sqlite] Reporting on summary data

That works. Thanks! > From: Pavel Ivanov > Oops, sorry! I misunderstood what you > need. Try this: > > 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

Re: [sqlite] Reporting on summary data

Oops, sorry! I misunderstood what you need. Try this: 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

Re: [sqlite] Reporting on summary data

> From: Pavel Ivanov > > 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) -

Re: [sqlite] Reporting on summary data

> 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

[sqlite] Reporting on summary data

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