Sql-server – Case always returns null

casedatetimesql server

I have a subquery that gets the DATEDIFF of two dates. However the first case always returns null yet the actual sub query returns dates?!

CASE 
WHEN [date1] > [date2]
THEN DATEDIFF(DD,getdate(),date1) 
WHEN date1 < date2 
THEN DATEDIFF(DD,getdate(),date2) 
END[date diff]

FROM(

SELECT
licext.new_licenceId,
CASE 
WHEN CONVERT(time,licext.new_EmergencyLicenceExpiryDate) like '%23:00:00.000%'
THEN DATEADD(dd,1,CONVERT(date,licext.new_EmergencyLicenceExpiryDate))
ELSE CONVERT(date,licext.new_EmergencyLicenceExpiryDate)
END [date1], --EM Code

CASE 
WHEN CONVERT(time,licext.new_standardLicenceExpiryDate) like '%23:00:00.000%'
THEN DATEADD(dd,1,CONVERT(date,licext.new_standardLicenceExpiryDate))
ELSE CONVERT(date,licext.new_standardLicenceExpiryDate)
END [date2]  --FRL
from new_licenceExtensionBase licext
)QUERY

The reason the second cases are on there is because the database I am working with stores datetime in a really weird way, ie)If it has 23:00 at the end it actually means tomorrows date?! (This is a Microsoft CRM Database) -But that's a whole different matter.

EDIT:

It did have an ELSE clause which worked fine for date2 being bigger than date1, because that was being evaluated. For Example, the following code works fine for getting the difference between today and date2:

CASE 
WHEN [date1] > [date2]
THEN DATEDIFF(DD,getdate(),date1) 
ELSE DATEDIFF(DD,getdate(),date2) 
END[date diff]

EDIT 2:

I've just done a couple of tests:
Using a select to return the dates works fine. -They return as expected.

Test 2: I've added a third WHEN to the query to say that when a date is null, return 1990-01-01..However it still returns null, Is my whole case being skipped over?

Best Answer

There's no ELSE in your first CASE, which means if none of your WHEN expressions are matched then it returns NULL.

My guess is there's an issue in your logic below that causes date1 and date2 to always be equal, which would miss both your WHENs in the first CASE and return NULL.

Related Solutions

Sql-server – Update “NULL” string to Actual NULL value

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ' + QUOTENAME(name) + ' = CASE
  WHEN ' + QUOTENAME(name) + ' = ''NULL'' THEN NULL ELSE '
  + QUOTENAME(name) + ' END,'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('dbo.YourTableName')
AND system_type_id IN (35,99,167,175,231,239);

SELECT @sql = N'UPDATE dbo.YourTableName SET ' + LEFT(@sql, LEN(@sql)-1) + ';';

PRINT @sql;
--EXEC sp_executesql @sql;

Sql-server – function hangs with null case operation

Part of your initial query is as follows.

  FROM   [dbo].[calendar] a
          LEFT JOIN [dbo].[colleagueList] b
            ON b.[Date] = a.d
   WHERE  DAY(a.[d]) = 1
          AND a.[d] BETWEEN @dateStart AND COALESCE(@dateEnd,@dateStart)

That section of the plan is shown below

enter image description here

Your revised query BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart) has this for the same join

enter image description here

The difference seems to be that ISNULL simplifies further and as a result you get more accurate cardinality statistics going into the next join. This is an inline table valued function and you are calling it with literal values so it can do something like.

 a.[d] BETWEEN @dateStart AND ISNULL(@dateEnd,@dateStart) 
 a.[d] BETWEEN '2013-06-01' AND ISNULL(NULL,'2013-06-01') 
 a.[d] BETWEEN '2013-06-01' AND '2013-06-01'
 a.[d] = '2013-06-01'

And as there is an equi join predicate b.[Date] = a.d the plan also shows an equality predicate b.[Date] = '2013-06-01'. As a result the cardinality estimate of 28,393 rows is likely to be pretty accurate.

For the CASE/COALESCE version when @dateStart and @dateEnd are the same value then it simplifies OK to the same equality expression and gives the same plan but when @dateStart = '2013-06-01' and @dateEnd IS NULL it only goes as far as

a.[d]>='2013-06-01' AND a.[Date]<=CASE WHEN (1) THEN '2013-06-01' ELSE NULL END

which it also applies as an implied predicate on ColleagueList. The estimated number of rows this time is 79.8 rows.

The next join along is

   LEFT JOIN colleagueTime
     ON colleagueTime.TC_DATE = colleagueList.Date
        AND colleagueTime.ASSOC_ID = CAST(colleagueList.ID AS VARCHAR(10))

colleagueTime is a 3,249,590 row table which is (again) apparently a heap with no useful indexes.

This discrepancy in estimates affects the join choice used. The ISNULL plan chooses a hash join that just scans the table once. The COALESCE plan chooses a nested loops join and estimates that it will still just need to scan the table once and be able to spool the result and replay it 78 times. i.e. it estimates that the correlated parameters will not change.

From the fact that the nested loops plan was still going after two hours this assumption of a single scan against colleagueTime seems likely to be highly inaccurate.

As for why the estimated number of rows between the two joins are so much lower I'm not sure without being able to see the statistics on the tables. The only way I managed to skew the estimated row counts that much in my testing was adding a load of NULL rows (this reduced the estimated row count even though the actual number of rows returned remained the same).

The estimated row count in the COALESCE plan with my test data was in the order of

number of rows matching >= condition * 30% * (proportion of rows in the table not null)

Or in SQL

SELECT 1E0 * COUNT([Date]) / COUNT(*) * ( COUNT(CASE
                                                  WHEN [Date] >= '2013-06-01' THEN 1
                                                END) * 0.30 )
FROM   [dbo].[colleagueList]

but this does not square with your comment that the column has no NULL values.

Best Answer

Related Solutions

Sql-server – Update “NULL” string to Actual NULL value

Sql-server – function hangs with null case operation

Related Question