CODEWITHSUNDEEP
sql-server-2008select
Khrys
Khrys

Reputation: 2774

Select to Count "Null" and "Not Null"

The following select is working when the CLOSE_DATE field IS NOT NULL:

SELECT
    YEAR (CLOSE_DATE) AS TheYear,
    MONTH (CLOSE_DATE) AS TheMonth,
    COUNT (*) AS cnt
FROM
    Table1
WHERE
    Table1.DEPT = 'SUPPORT'
GROUP BY
    YEAR (CLOSE_DATE),
    MONTH (CLOSE_DATE)
ORDER BY
    TheYear DESC,
    TheMonth DESC

The output is:

+-------------+------+
| Year | Month | cnt |
+-------------+------+
| 2014 |     4 |  10 |
| 2014 |     3 |   9 |
| 2014 |     2 |  15 |
| 2014 |     1 |  12 |
| 2013 |    12 |  10 |
| 2013 |    11 |  50 |
+--------------+-----+

If the CLOSE_DATE has, for example, 7 rows with NULL, the result will be:

+-------------+------+
| Year | Month | cnt |
+-------------+------+
| NULL |  NULL |   7 |
| 2014 |     4 |  10 |
| 2014 |     3 |   9 |
| 2014 |     2 |  15 |
| 2014 |     1 |  12 |
| 2013 |    12 |  10 |
| 2013 |    11 |  50 |
+--------------+-----+

The NULL indicates it should count for the actual year and month, so the expected result should be:

+-------------+------+
| Year | Month | cnt |
+-------------+------+
| 2014 |     4 |  17 |
| 2014 |     3 |   9 |
| 2014 |     2 |  15 |
| 2014 |     1 |  12 |
| 2013 |    12 |  10 |
| 2013 |    11 |  50 |
+--------------+-----+

UPDATE

The example above is just part of a bigger select. I Actually count for opened and closed cases.

If the ticket did not have a close_date it means it is open. So, intead of count as close it should count as open.

The select is the same, with open column:

SELECT
    YEAR (OPEN_DATE) AS TheYear,
    MONTH (OPEN_DATE) AS TheMonth,
    COUNT (*) AS cnt
FROM
    Table1
WHERE
    Table1.DEPT = 'SUPPORT'
GROUP BY
    YEAR (OPEN_DATE),
    MONTH (OPEN_DATE)
ORDER BY
    TheYear DESC,
    TheMonth DESC

So the line YEAR (OPEN_DATE) AS TheYear, and MONTH (OPEN_DATE) AS TheMonth, is bringing the year and month from the open_date column, but if the close_date column is null it should count on that line too.

This way all the rows with null values in the close_date will be treated as open_date.

Upvotes: 0

Views: 102

Answers (1)

AK47
AK47

Reputation: 3797

You can use Isnull() function, This function allows a another predefined value whenever null value occurs.

SELECT
    YEAR (Isnull(CLOSE_DATE,getdate())) AS TheYear,
    MONTH (Isnull(CLOSE_DATE,getdate())) AS TheMonth,
    COUNT (*) AS cnt
FROM
    Table1
WHERE
    Table1.DEPT = 'SUPPORT'
GROUP BY
    YEAR (Isnull(CLOSE_DATE,getdate())),
    MONTH (Isnull(CLOSE_DATE,getdate()))
ORDER BY
    TheYear DESC,
    TheMonth DESC

Upvotes: 1

Related Questions