case count issue

[burnside]

Hi,

Need help with the following query - if there is a count for USED then no NEW are counted for each day, can anyone tell me why?

SELECT 
CASE eventstocklink.statusid WHEN 2 THEN count(CB.actionid) ELSE 0 END AS USED, 
CASE eventstocklink.statusid WHEN 1 THEN count(CB.actionid) ELSE 0 END AS NEW
FROM action AS CB 
LEFT JOIN event ON event.eventid = CB.eventid 
LEFT JOIN eventstocklink ON eventstocklink.eventstocklinkid = CB.eventstocklinkid 
WHERE date(CB.actiondate) 
BETWEEN '20091102' AND '20091108' 
GROUP BY eventstocklink.statusid, date(CB.actiondate)

thanks in advance

[burnside]

this solved my issue

     , COUNT(CASE WHEN eventstocklink.statusid = 2 
                  THEN CB.actionid END) AS USED
     , COUNT(CASE WHEN eventstocklink.statusid = 1 
                  THEN CB.actionid END) AS NEW

[FiveForMe]

Hi,

CASE FBD for PLC & NovaPro SCADA:

Oops wrote: