Ashan Ashan - 4 months ago 41
SQL Question

Nest sum,datediff and case statement

I have a table like this,

enter image description here

SELECT rd_rfiledelayid_pk,
rd_rfileid_fk_ind,
rd_enddate
FROM t_rfiledelay
WHERE rd_rfileid_fk_ind = '4873'



  1. i need to get result as date different summery group by foreign key (
    RD_RfileID_fk_ind
    ) and criteria is when
    NULL
    RD_EndDate get today date.



as this i tried like this,

SELECT rd_rfileid_fk_ind,
Sum(Datediff(day, rd_startdate, CASE
WHEN rd_enddate = NULL THEN Getdate()
ELSE rd_enddate
END)) AS ES_SubmittorDelays
FROM t_rfiledelay
WHERE rd_rfileid_fk_ind = '4873'
GROUP BY rd_rfileid_fk_ind


i got result,

enter image description here

but result need to like this,


2016-06-20 to 2016-06-25 = 5 days

2016-07-01 to today (2016-07-07) = 6 days

and result need to be 11 days.


Can you please let me know what i miss here ?

Answer

This is the query that you want:

SELECT RD_RfileID_fk_ind,
       SUM(DATEDIFF(day, RD_Startdate, COALESCE(RD_EndDate, GETDATE() ))
          ) as ES_SubmittorDelays
FROM t_RfileDelay 
WHERE RD_RfileID_fk_ind = '4873'
GROUP BY RD_RfileID_fk_ind;

Your problem is the = NULL. The correct syntax is IS NULL; = never returns true when either (or both) of the arguments is NULL.

In any case, COALESCE() is simpler than CASE.

Comments