How to write a CASE WHEN statement with multiple DATEDIFF variables
- by Anne C
I need to calculate the difference between two dates (facility_start_date, facility_end_date) for a report in Reporting Services in SQL 2005. If the facility_end_date is null then it needs to use the report parameter @EndDate in the calculation. However if the facility_end_date is greater than the parameter @EndDate, then it also needs to use the paramenter @EndDate. The code below works fine except that if the facility_end_date is greater than the parameter @EndDate it is still calculating between the facility_start_date and facility_end_date, rather than between the facility_start_date and @EndDate. Any help would be appreciated.
CASE WHEN facility_start_date > facility_end_date THEN
NULL
WHEN DATEPART(day , facility_start_date) > DATEPART(day , facility_end_date) THEN
DATEDIFF(d , facility_start_date , ISNULL(facility_end_date , @EndDate)) - 1
WHEN DATEPART(day , .facility_end_date) > DATEPART(day , @EndDate) THEN
DATEDIFF(d , facility_start_date , @EndDate) - 1
ELSE DATEDIFF(d , facility_start_date , ISNULL facility_end_date , @EndDate))
END
