Case with Sum? 2

[notadba]

Looking for a quick fix (UDB 7.1) - using a sql tool that does not support outer joins..

Simplified version of the Table

ID, date_from, Approved, duration

Table will have mutiple rows per ID.

Need to get a result set where the sum of 'duration ' is held in its own column - where the approved
flag = 'Y' and another summed column where the flag = 'N'

So result looks like

ID, approved_sum, not_approved_sum

I can get the query to work where it will return a separate row for each sum using the 'approved' column in the results and grouping on that.
When I use a sum against each of the approved flags (joining table back and passing the flag criteria), I only
get rows where employees have a sum against both criteria - dropping those that only meet one.

Thought of a union but since we are dealing with the same ID goup, there is no clean way to exclude and would still
end up with two rows.

Is it possible to do a case/sum combination? Where would the group by go?

Some variation on the following...(which doesn't work)
SELECT A.ID
, A.NAME
, CASE B.APPROVED WHEN 'Y' THEN SUM(C.DURATION_HOURS) ELSE 0 END AS APPROVED
, CASE B.APPROVED WHEN 'N' THEN SUM(C.DURATION_HOURS) ELSE 0 END AS NOT_APPROVED
FROM TBL1 A, TBL2 B
WHERE A.ID = B.ID
AND C.DATE_FROM > (User Prompt for date)
GROUP BY A.ID, A.NAME

Probably missing something really obvious.

 

[gregsimpson]

notadab,

I found on a previous project the "reporting package" wouldn't support many standard SQL operations but did support views. I was therefore able to put the main of my coding into views and get the reporting package to select from views.

Greg

 

[MarcLodge]

notadab,

Try this (unchecked) code:

SELECT A.ID
, A.NAME
, SUM(CASE B.APPROVED WHEN 'Y' THEN C.DURATION_HOURS ELSE 0 END) AS APPROVED
, SUM(CASE B.APPROVED WHEN 'N' THEN C.DURATION_HOURS ELSE 0 END) AS NOT_APPROVED
FROM TBL1 A, TBL2 B
WHERE A.ID = B.ID
AND C.DATE_FROM > (User Prompt for date)
GROUP BY A.ID, A.NAME


Hope this helps/works,

Marc

 

[blom0344]

shouldn't it be:

SELECT  A.ID
, A.NAME
, SUM(CASE WHEN B.APPROVED = 'Y' THEN C.DURATION_HOURS ELSE 0 END) AS APPROVED
, SUM(CASE WHEN B.APPROVED = 'N' THEN C.DURATION_HOURS ELSE 0 END) AS NOT_APPROVED
FROM TBL1 A, TBL2 B    
WHERE A.ID = B.ID       
AND C.DATE_FROM > (User Prompt for date)      
GROUP BY A.ID, A.NAME

?

Ties Blom

 

[ddiamond]

Both Marc's and Ties' queries will work and return the same results. They are just using 2 different syntaxes of the case statement.

 

[notadba]

Thanks for all your input - have gotten it to work.

The main problem is working through a query/sql tool that has 'interesting' ways of parsing sql - so it still needed some tricking to get it to work.. Was exactly what I was looking for.