CODEWITHSUNDEEP
sqlsql-serversql-server-2008
ebjdnh
ebjdnh

Reputation: 11

Need date to show instead of being Null

The date is showing as NULL now when the count is 0, any ideas on how to get it to show the date?

SELECT
    dc.dateasdate AS 'Date',
    CASE
        WHEN dc.dateasdate IS NULL THEN '0'
        ELSE COUNT(Practice)
    END AS 'Total Enquiries',
    dp.practice,
    de.Origin
FROM dim.Practice dp
LEFT JOIN fact.enquiry fe
    ON fe.PracticeSKey = dp.PracticeSKey
LEFT JOIN dim.EnquiryOrigin de
    ON de.EnquiryOriginSKey = fe.EnquiryOriginSKey
    AND de.Origin = 'Web Enquiry'
LEFT JOIN dim.Calendar dc
    ON dc.CalendarSKey = fe.EnquiryCreatedSKey
    AND dc.dateasdate = '2016-04-03'
WHERE
    de.Origin IS NOT NULL
GROUP BY
    dc.dateasdate,
    de.Origin,
    practice
ORDER BY dc.dateasdate, Practice

Upvotes: 1

Views: 48

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270773

Hmmm . . . You should keep the types the same:

SELECT dc.dateasdate,
       (case WHEN dc.dateasdate is null THEN 0 ELSE COUNT(Practice) END) AS TotalEnquiries,
       dp.practice,
       de.Origin

However, the date is showing as NULL because the value is NULL in the original data. What value do you want to show in this case?

EDIT:

You can use COALESCE():

SELECT COALESCE(dc.dateasdate, '2016-04-03')
       (case WHEN dc.dateasdate is null THEN 0 ELSE COUNT(Practice) END) AS TotalEnquiries,
       dp.practice,
       de.Origin
. . .

However, I suspect that the LEFT JOIN is not what you really want.

Upvotes: 0

Mark Roper
Mark Roper

Reputation: 1389

I believe you want the following:

case WHEN COUNT(Practice) is null THEN '0' ELSE COUNT(Practice) END AS 'Total Enquiries'

Upvotes: 0

Related Questions