Ashan - 11 months ago 85

SQL Question

`SELECT rd_rfiledelayid_pk,`

rd_rfileid_fk_ind,

rd_enddate

FROM t_rfiledelay

WHERE rd_rfileid_fk_ind = '4873'

- i need to get result as
**date different summery**group by foreign key () and criteria is when`RD_RfileID_fk_ind`

RD_EndDate get today date.`NULL`

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,

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`

.

Source (Stackoverflow)