Ashan Ashan - 1 year ago 92
SQL Question

Nest sum,datediff and case statement

I have a table like this,

enter image description here

SELECT rd_rfiledelayid_pk,
FROM t_rfiledelay
WHERE rd_rfileid_fk_ind = '4873'

  1. i need to get result as date different summery group by foreign key (
    ) and criteria is when
    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 Source

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.