How to write a CASE WHEN statement with multiple DATEDIFF variables

Question

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

Answer 1

   CASE
     WHEN rpt_critical_info_view.facility_start_date > rpt_critical_info_view.facility_end_date
     THEN NULL
     WHEN rpt_critical_info_view.facility_end_date IS NULL
           OR Datepart(DAY, rpt_critical_info_view.facility_end_date) > Datepart(DAY, @EndDate)
     THEN Datediff(d, rpt_critical_info_view.facility_start_date, @EndDate) - 1
     ELSE Datediff(d, rpt_critical_info_view.facility_start_date, rpt_critical_info_view.facility_end_date)
   END

I am not 100% clear on what you expect the result to be. You are evaluating DAY offsets, not date offsets. With your DATEPART function, 05/31/2010 will evaluate as greater than 06/16/2010. I am also not sure why you are subtracting 1 from the result of your DATEDIFF. If you are truly trying to calculate the DATE differences, use this:

   CASE
     WHEN rpt_critical_info_view.facility_start_date > rpt_critical_info_view.facility_end_date
     THEN NULL
     WHEN rpt_critical_info_view.facility_end_date IS NULL
           OR rpt_critical_info_view.facility_end_date > Datepart(DAY, @EndDate)
     THEN Datediff(d, rpt_critical_info_view.facility_start_date, @EndDate) - 1
     ELSE Datediff(d, rpt_critical_info_view.facility_start_date, rpt_critical_info_view.facility_end_date)
   END

Answer 2

I'm not very much clear with the problem you have and the query you posted, but see if this might help you..Use this in your select statement..

datediff(facility_start_date ,CASE WHEN facility_end_date is null 
THEN CASE WHEN facility_end_date > @EndDate THEN @EndDate ELSE @EndDate 
END ELSE facility_end_date END)