0

I am having an issue getting a record produced if my count = 0.

Basically my query is counting logons based on some criteria, but if there has never been a logon for a specific customerID I still want it to show as 0.

I have tried IFNULL on the count but with no joy.

SELECT 
    CUSTOMERID,
    CASE 
    WHEN STATUSCODE = 600 THEN 'Successful Logon' 
    ELSE 'Unsuccessful Logon' 
    END as LogonStatus,
    COUNT( * ) COUNTOFACCOUNTS

FROM 
    SCEMEA.TABLENAME
WHERE 
    CUSTOMERID in ('"+join(Parameters!CustomerID.Value, "','")+"')
    AND (Cast(DATETIME as Date) >= '"& Format(Parameters!FromDate.Value, "yyyy-MM-dd") & "' 
    AND Cast(DATETIME as Date) <= '" & Format(Parameters!ToDate.Value, "yyyy-MM-dd") & "') 
    AND COMPONENTDESCRIPTION = 'RandomText'
    AND METHOD = 'RandomText' 

GROUP BY 
    CUSTOMERID,
    CASE 
        WHEN STATUSCODE = 600 THEN 'Successful Logon' 
        ELSE 'Unsuccessful Logon' 
    END

ORDER BY
CUSTOMERID ASC

Please let me know if you need anymore information, any help would be appreciated.

ivpavici
  • 1,117
  • 2
  • 19
  • 30
JoelG
  • 37
  • 1
  • 6
  • In the event a customer has not logged on, will there be an entry in the source table or not? Examples of your data for a logged on customer and a customer that has not logged on would be helpful to understand your query and isolate the problem. – Jonathon Ogden Aug 15 '16 at 14:40
  • Hi Jonathon, Thanks for the comment. There would be no record in the table if the customer had not logged on, but I want to show that as a 0 rather then nothing. Let me know if you need anything else. – JoelG Aug 16 '16 at 07:49
  • I assume there's at least two tables then. One containing customer information and the other logon information? If so, can you give me the names and if they join on `CUSTOMERID`? – Jonathon Ogden Aug 16 '16 at 07:59
  • Currently no, we only have one table that holds an audit trail which we can determine what a customer has done by the COMPONENTDESCRIPTION and MEHOD – JoelG Aug 16 '16 at 08:25

0 Answers0