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: