title: aggregate functions return null
body: <p>I have this sample data</p>
  
  <pre><code>+------+------------+------------+
  | 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 |
  +------+------------+------------+
  </code></pre>
  
  <p>DDL</p>
  
  <pre><code>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'})
  </code></pre>
  
  <p>I'm expected to get this data</p>
  
  <pre><code>+------+------------+------------+
  | CODE | START_DATE | END_DATE   |
  +------+------------+------------+
  | 0001 | 2012-01-01 | NULL       |
  +------+------------+------------+
  | 0002 | 2012-02-01 | NULL       |
  +------+------------+------------+
  | 0003 | 2012-02-01 | 2012-03-31 |
  +------+------------+------------+
  </code></pre>
  
  <p>currently in <strong>SQL Server 2005</strong>, I use this query</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I'm cheating <strong>NULL value to date 9999-12-31</strong></p>
  
  <p>I'm looking for better solution for this</p>
  
  <p>thank you</p>