SQLTeam.com | Weblogs | Forums

CASE WHEN Not evaluating first argument in DateDiff function


#1

I have an order shipping app whereas I can create a derived column to subtract the working days when an order was placed to when the shipping dept. receives the item to put on the truck:

select(DATEDIFF(dd,orders.dateadded,CASE WHEN ordetails.Code = 'REC' THEN CONVERT(VARCHAR, ordetails.detaildate, 111) end)) -
(2 * DATEDIFF(wk,orders.dateadded,CASE WHEN ordetails.Code = 'REC' THEN CONVERT(VARCHAR, ordetails.detaildate, 111) end))
as daysRcv from tables

... this works fine
..but when I try to do another column to compute on the fly the days from when an item is in inventory to the time another alert (an email, another note regarding the order itself) is put into the system , the below doesnot evaluate the first argument for the DateDiff function at all, it always comes out NULL. I've tried every combination possible (even using ELSE clause at end of Case Whens to know I can display a result) , but seems not to display the days difference. The daysRcv is using a field that is already installed in the database table (a datetime datatype).

select(DATEDIFF(dd,CASE WHEN ordetails.Code = 'REC' THEN CONVERT(VARCHAR, ordetails.detaildate, 111) end),CASE WHEN ordetails.Code = 'A' THEN CONVERT(VARCHAR, ordetails.detaildate, 111) end)) -
(2 * DATEDIFF(wk,CASE WHEN ordetails.Code = 'REC' THEN CONVERT(VARCHAR, ordetails.detaildate, 111) end),CASE WHEN ordetails.Code = 'A' THEN CONVERT(VARCHAR, ordetails.detaildate, 111) end))
as daysCheck from tables

???
S2017


#2

One of the values in the first DATEDIFF must be NULL, since "ordetails.Code" can't be both 'REC' and 'A' at the same time. Once a NULL value is present, it will force the result of the entire expression to be NULL also.

I strongly suspect the code's not doing the actual calculation you actually want to do, but naturally I don't the details of what you're actually trying to compute.


#3

Scott thanks, what I've done is highlighted the first argument for DateDiffs in each of the two blocks. The first block works. The second block, when the stored procedure runs thru the SELECT clause (theres a dozen or so fields, derived columns) there are rows that do have a REC value , i'm wondering why this doesnt evaluate
???


#4

Again, because ordetails.Code can't be both 'REC' and 'A' as the same time. Remember, the expressions in the SELECT are evaluated separately for every row.

DATEDIFF(...,CASE WHEN ordetails.Code = 'REC' THEN ... END,
    CASE WHEN ordetails.Code = 'A' THEN ... END)

Since you didn't specify an ELSE value, that defaults to NULL. For rows where ordetails.Code = 'REC', then the second expression will yield NULL. When ordetails.Code = 'A', then the first expression will yield NULL. Once a NULL is present, the result of the DATEDIFF will be NULL, and then any expression that is used with will also become NULL.

More generally, any NULL value in an expression sets the result of the expression to NULL also.


#5

I've tried COALESCE and ISNULL , but those don't work either. Other than building another table , anything else I can do to make the first argument work
Thanks
SQ


#6

Difficult to say without more knowledge of the data.

Is there only one ordetails row with " Code = 'REC' "? If not, which detaildate should be used?
Is there more than ordetails row with " Code = 'A' "? If so, which detaildate should be used?