SQL - CASE Statement if record is NULL because record doesnt exist in table

Question

I have a problem which i thought one of the questions would solve at the bottom but i still have a problem. The issue is my case statement is wrong (but runs without an error) but unsure what else i am missing.

My aim is, if DueDate IS NULL because there is no record in Table2 therefore all i want is for this value to be displayed as 'No' and not 'NULL'. I added "IsNull" into my code but hasnt solved my problem. I have seen ISNULL() and COALEASE() on these forums but unable to get my desired result

Here is my working code below:

SELECT 
    Client, 
    COUNT(*) AS ReadyRecords, 
    (SELECT COUNT(*) FROM Table1 EPR WHERE actioned=8 AND EPR.Client=Table1.Client) AS Outstanding, 
    (SELECT TOP 1 
        CASE 
            WHEN DueDate < GETDATE() THEN 'Yes' 
            WHEN DueDate IS NULL THEN 'No' 
        ELSE 'No' 
        END AS DueDate
    FROM Table2 GL WHERE GL.Client=Table1.Client ORDER BY DueDate) AS DueDate

    FROM Table1 WHERE Actioned=2 GROUP BY Client

EDIT:

Current Result Set:

Client   | ReadyRecords  |  Outstanding  |  DueDate

Test     |     42        |      54       |   NULL
Stack    |     150       |      10       |   NULL
JBloggs  |     32        |      28       |   Yes
DBloggs  |     2         |      17       |   Yes
Overflow |     1         |      1        |   No

Desired Result Set:

Client   | ReadyRecords  |  Outstanding  |  DueDate

Test     |     42        |      54       |   No
Stack    |     150       |      10       |   No
JBloggs  |     32        |      28       |   Yes
DBloggs  |     2         |      17       |   Yes
Overflow |     1         |      1        |   No

Table 1:

id      |  Client   |   Actioned  | etc...

5245    |  Test     |      8      | etc...
....so on

Table 2:

id   |   Client   |   DueDate

1    |  JBloggs   | 2012-12-01 00:00:00.000
2    |  DBloggs   | 2012-12-05 00:00:00.000
3    |  Overflow  | 2012-12-12 00:00:00.000

Let me know if you need anything else.

Similar/Related Questions:

Also Instead of `count(*)` you should use `count(1)` this will work same as `count(*)` but will execute much faster than getting `*(all)` values... — Mayank Pathak, Dec 06 '12 at 12:35
@MayankPathak Thank you i will start using this from now on. — Naxos, Dec 06 '12 at 12:54
@MayankPathak I'm not sure there is any performance difference between count(*) and count(1) in SQL Server. See [this answer](http://stackoverflow.com/a/1221649/943359) — Tim S, Dec 06 '12 at 12:58

score 5 · Accepted Answer · answered Dec 06 '12 at 13:32

You need to apply the ISNULL() or COALESCE() function to the whole inline subquery, like this:

SELECT 
    Client, 
    COUNT(*) AS ReadyRecords, 
    (SELECT COUNT(*) FROM Table1 EPR 
     WHERE actioned=8 AND EPR.Client=Table1.Client
    ) AS Outstanding, 

    COALESCE(
        (SELECT TOP 1 
            CASE 
                WHEN DueDate < GETDATE() THEN 'Yes' 
            END AS DueDate
        FROM Table2 GL WHERE GL.Client=Table1.Client ORDER BY DueDate
        ), 'No' 
    ) AS DueDate

FROM Table1  WHERE Actioned=2  GROUP BY Client ;

or move the CASE expression there:

SELECT 
    Client, 
    COUNT(*) AS ReadyRecords, 
    (SELECT COUNT(*) FROM Table1 EPR 
     WHERE actioned=8 AND EPR.Client=Table1.Client
    ) AS Outstanding, 

    CASE WHEN 
           (SELECT TOP 1 
              DueDate
            FROM Table2 GL WHERE GL.Client=Table1.Client ORDER BY DueDate
           ) < GETDATE() 
        THEN 'Yes'
        ELSE 'No'
    END AS DueDate

FROM Table1  WHERE Actioned=2  GROUP BY Client ;

score 2 · Answer 2 · answered Dec 06 '12 at 13:48

First of all, you need to know, that datetime fields in table may appear as NULL, but when use it in a query, then sql convert it into a minimal date allowed for that datatype, and that is '1900-01-01' so it is possible that you get more 'Yes' values than actually is.

score 0 · Answer 3 · answered Dec 06 '12 at 12:24

0

You can use this...
SELECT 
Client, 
COUNT(*) AS ReadyRecords, 
(SELECT COUNT(*) FROM Table1 EPR WHERE actioned=8 AND EPR.Client=Table1.Client) AS Outstanding, 
(SELECT TOP 1 
    CASE 
        WHEN DueDate < GETDATE() THEN 'Yes' 
        ELSE ISNULL(Cast(DueDate AS VARCHAR),'NO' )        
    END AS DueDate
FROM Table2 GL WHERE GL.Client=Table1.Client ORDER BY DueDate) AS DueDate

FROM Table1 WHERE Actioned=2 GROUP BY Client

answered Dec 06 '12 at 12:24

Mohit

39
1
11

I tried what you said but i still get 'NULL' in the result set. I have edited my original post with the 2 tables and the result set i get now (with and without your change) and the desired result set. – Naxos Dec 06 '12 at 12:52

codingbiz · Answer 4 · 2012-12-06T13:28:53.700

I think the problem is if DueDate is NULL your DueDate < GetDate() results to NULL and you get No as result as DueDate is never less than GetDate in such instance. Change the order like this

CASE 
    WHEN DueDate IS NULL THEN 'No'
    WHEN DueDate < GETDATE() THEN 'Yes'        
ELSE 'No' 
END AS DueDate

By doing this the NULL value is checked first before other conditions. Alternatively you can change this line to

WHEN ISNULL(DueDate,'Some Valid Default Value Here') < GETDATE() THEN 'Yes'

whytheq · Answer 5 · 2012-12-07T10:07:51.297

0

does this work - suspect there might be a couple of glitches in it but I don't currently have management sudio:

SELECT 
        Client, 
        COUNT(*) AS ReadyRecords, 
        (SELECT COUNT(*) FROM Table1 EPR WHERE actioned=8 AND EPR.Client=Table1.Client) AS Outstanding, 
        [DueDate] = CASE 
                                WHEN [MXDATE] < GETDATE() THEN 'Yes' 
                                WHEN [MXDATE] IS NULL THEN 'No' 
                            ELSE 'No' 
                            END 
FROM 
    Table1 T1
    LEFT OUTER JOIN
        (
        SELECT 
            Client,
            [MXDATE] = MAX(DueDate)
        FROM Table2
            GROUP BY Client
        ) GL
        ON
         GL.Client=T1.Client 
WHERE Actioned=2 
GROUP BY Client

edited Dec 07 '12 at 10:07

answered Dec 06 '12 at 13:27

whytheq

34,466
65
172
267

It doesnt seem to for me, i get this error doing your solution: Msg 8120, Level 16, State 1, Line 15 Column 'Client' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. – Naxos Dec 06 '12 at 14:20
no worries: you have a solution but I added the missing `GROUP BY`anyway – whytheq Dec 07 '12 at 10:08

SQL - CASE Statement if record is NULL because record doesnt exist in table

5 Answers5