Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Unanswered: GROUP BY & Include NULL Rows

    Hi All,

    I am trying to write an SQL query for MS SQL Server 2005.

    Table1:
    Columns: WFID, TaskID, DateStart, DateDone
    1,1,02/20/10,02/20/10
    1,2,02/21/10,02/21/10
    1,3,02/22/10,02/22/10
    1,4,02/22/10,02/23/10
    1,5,02/23/10,02/23/10,
    1,6,02/23/10,NULL

    Here I am writing the SQL as
    Code:
    select WFID,min(DateStart), max(DateDone) from table1 where taskid in (3,4,5,6)
    group by WFID
    I am getting the output as
    1,02/22/10,02/23/10
    But I want it like
    1,02/22/10,NULL

    Which means, If there is a NULL in the DateDone then I want to show as NULL else the MAX DateDone.

    Please let me know if there a way..

    Thanks,
    Mahesh

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    MAX(NULLIF(COALESCE(DateDone, '20790101'),  '20790101'))
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2004
    Posts
    6
    Thanks for a quick response but I didn't get the '20790101' value?

  4. #4
    Join Date
    Nov 2009
    Posts
    37
    Its just a date into the future to use as a comparison within the NULLIF method. Actually the above didnt work for me as having MAX as the outer function means you are back to square one.....when I changed the MAX and NULLIF it does work though:

    works:
    Code:
    NULLIF(max(COALESCE(DateDone, '20790101')), '20790101')
    doesn't work:
    Code:
    MAX(NULLIF(COALESCE(DateDone, '20790101'),  '20790101'))

  5. #5
    Join Date
    Apr 2004
    Posts
    6
    Gotcha...Thanks alot..

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Oops. Rushed air code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •