>Hi,
>
>The following is a segment of one of SQL Selects:
>
>

>sum( CASE WHEN DATEDIFF(dd, MYTABLE.DATE1, MYTABLE.DATE2) <= 0 THEN 1 ELSE 0 END) as DONE_ONTIME 
>

>
>Basically the above counts how many jobs were done on time. If DATE2 is greater than DATE1 the result should be 1 (done on time).
>
>What I find is that even if some of the records/rows have the value in field DATE2 set to NULL, the resulting value of DONE_ONTIME is always 0 (zero).
>I would like to account for these records in such way that when DATE2 is NULL the DATEDIFF() would see it as very early date. That is, in case of DATE2 NULL the DATEDIFF() return 0. But for other records, it should be whatever the difference.
>
>How do you "deal" with the nulls in the above? TIA
>
>UPDATE. I think I am going to create a CASE within CASE to check for null value and if it is, set the DATE2 to '19000101'.

Use COALESCE function, e.g.

datediff(day, MyTable.Date1, coalesce(myTable.Date2, '19000101'))