CODEWITHSUNDEEP
sql-serversql-server-2005nullaggregate-functions
tsurahman
tsurahman

Reputation: 1912

aggregate functions return null

I have this sample data

+------+------------+------------+
| CODE | START_DATE | END_DATE   |
+------+------------+------------+
| 0001 | 2012-01-01 | 2012-01-31 |
+------+------------+------------+
| 0001 | 2012-02-01 | 2012-02-29 |
+------+------------+------------+
| 0001 | 2012-03-01 | NULL       |
+------+------------+------------+
| 0002 | 2012-02-01 | 2012-02-29 |
+------+------------+------------+
| 0002 | 2012-03-01 | 2012-03-31 |
+------+------------+------------+
| 0002 | 2012-04-01 | NULL       |
+------+------------+------------+
| 0003 | 2012-02-01 | 2012-02-29 |
+------+------------+------------+
| 0003 | 2012-03-01 | 2012-03-31 |
+------+------------+------------+

DDL

CREATE TABLE SAMPLE
(
    CODE VARCHAR(4),
    START_DATE DATETIME,
    END_DATE DATETIME
)

INSERT INTO SAMPLE (CODE, START_DATE, END_DATE) VALUES ('0001', {d '2012-01-01'}, {d '2012-01-31'})
INSERT INTO SAMPLE (CODE, START_DATE, END_DATE) VALUES ('0001', {d '2012-02-01'}, {d '2012-02-29'})
INSERT INTO SAMPLE (CODE, START_DATE, END_DATE) VALUES ('0001', {d '2012-03-01'}, NULL)

INSERT INTO SAMPLE (CODE, START_DATE, END_DATE) VALUES ('0002', {d '2012-02-01'}, {d '2012-02-29'})
INSERT INTO SAMPLE (CODE, START_DATE, END_DATE) VALUES ('0002', {d '2012-03-01'}, {d '2012-03-31'})
INSERT INTO SAMPLE (CODE, START_DATE, END_DATE) VALUES ('0002', {d '2012-04-01'}, NULL)

INSERT INTO SAMPLE (CODE, START_DATE, END_DATE) VALUES ('0003', {d '2012-02-01'}, {d '2012-02-29'})
INSERT INTO SAMPLE (CODE, START_DATE, END_DATE) VALUES ('0003', {d '2012-03-01'}, {d '2012-03-31'})

I'm expected to get this data

+------+------------+------------+
| CODE | START_DATE | END_DATE   |
+------+------------+------------+
| 0001 | 2012-01-01 | NULL       |
+------+------------+------------+
| 0002 | 2012-02-01 | NULL       |
+------+------------+------------+
| 0003 | 2012-02-01 | 2012-03-31 |
+------+------------+------------+

currently in SQL Server 2005, I use this query

SELECT  CODE,
        MIN(START_DATE) AS START_DATE,
        CASE
            WHEN    MAX(ISNULL(END_DATE, {d '9999-12-31'})) = {d '9999-12-31'}
            THEN    NULL
        ELSE
            MAX(END_DATE)
        END AS END_DATE
FROM    SAMPLE
GROUP BY    CODE

I'm cheating NULL value to date 9999-12-31

I'm looking for better solution for this

thank you

Upvotes: 3

Views: 1569

Answers (2)

Serge Belov
Serge Belov

Reputation: 5803

You could utilize the fact that the COUNT of NULL and NOT NULL columns will be different. So the script can go like this:

SELECT  CODE,
        MIN(START_DATE) AS START_DATE,
        CASE 
            WHEN    COUNT(*) > COUNT(END_DATE)
            THEN    NULL
        ELSE
            MAX(END_DATE)
        END AS END_DATE
FROM    SAMPLE
GROUP BY    CODE

Upvotes: 1

Daniel PP Cabral
Daniel PP Cabral

Reputation: 1624

This script uses a cte (common table expressions) to first get null end_dates and does a left join on it. It eliminates the need for the hard-coded arbitrary date you are using.

As it requires an extra query it would be slower than your initial solution.

So it also depends on the number of records and how frequent this script would be invoked, in deciding the best solution.

;WITH NULL_END_DATES_CTE AS (SELECT CODE FROM SAMPLE WHERE END_DATE IS NULL)
SELECT  S.CODE,
    MIN(S.START_DATE) AS START_DATE,
    CASE WHEN C.CODE IS NULL THEN MAX(S.END_DATE) ELSE NULL END AS END_DATE
FROM    SAMPLE S
LEFT JOIN NULL_END_DATES_CTE C ON C.CODE = S.CODE 
GROUP BY S.CODE,C.CODE
ORDER BY S.CODE

Upvotes: 0

Related Questions