# Query to Count Days Within a Fixed Date Range

Hi Experts,

Goal: Count total bed days spent in a hospital, within a fixed date range for an annual report.

One complete Bed Day occurs when a patient is in a hospital bed past  midnight, eg a patient admitted at 11pm but discharged at 5am the next day (6 hours later) will have an Occupied Bed Day count of 1 because midnight has passed.

The bed days in the attached XL are incorrect because the DATEDIFF starts to count from  IPAdmission Date disregarding @StartDate.

EE---Sample-Output-for-Limiting-.xlsx

## Algorithm

I would like to achieve the following:

## 1. Determine Start Date

THEN use @StartDate

## 2. Determine End Date

If IPDischargeDate is NULL --Patient is still in hospital bed
THEN use @EndDate
ELSEIF IPDischargeDate is after @EndDate
THEN use @EndDate
ELSE IPDischargeDate

## Current Query

``````DECLARE @StartDate DATETIME = '01-Jun-2012 00:00:00'
DECLARE @EndDate DATETIME = '31-May-2013 23:59:59';

SELECT
ClientID,
CareCluster,
IPDischargeDate,

``````
NB Query is a lot longer, but above is the general essence.

## Question

How do I elegantly achieve the above algorithm?
How do I achieve the above algorithm using the OVER Clause?

JohnAeris
###### Who is Participating?

Commented:
case
when ( ipAdmissionDate >= @StartDate and ipAdmissionDate < @EndDate ) and ( ipDischargeDate >= @StartDate and ipDischargeDate <= @EndDate )
then floor(cast(ipdischargeDate - ipAdmissionDate) as Float)
when ( ipAdmissionDate < @StartDate ) and ( ipDischargeDate >= @StartDate and ipDischargeDate <= @EndDate )
then floor(cast(ipdischargeDate - @StartDate) as Float)
when ( ipAdmissionDate >= @StartDate and ipAdmissionDate < @EndDate ) and ( ipDischargeDate > @EndDate )
then floor(cast(@EndDate - ipAdmissionDate) as Float)
when ( ipAdmissionDate < @StartDate and ipDischargeDate > @EndDate )
then floor(cast(@EndDate - @StartDate) as Float)
else 0
end as OccupiedBedDays
0

Commented:
Can you post what the occupied bed days should be per your sample?
0

Commented:
How does this work for you:

``````if( object_id( 'tempdb..#inpatientAdmissions_TEMP' ) is not null )

(
rowNum int not null,
clientID bigint not null,
careCluster varchar( 3 ),
clusterAssessmentDate datetime2 not null,
ipDischargeDate datetime2 null
);

values
( 1, 10000003, 'P11', '2012-12-23 22:53', '2013-3-8 23:30', '2013-5-14 13:12' ),
( 2, 10000003, 'P11', '2012-2-24 9:00', '2012-7-7 3:56', '2012-12-23 22:50' ),
( 3, 10000003, 'P17', '2011-8-11 13:40', '2011-10-13 16:24', '2012-5-25 22:12' ),
( 4, 10000003, 'P11', '2012-12-23 22:53', '2012-12-28 18:40', '2013-1-8 10:20' ),
( 5, 10000003, 'P11', '2012-12-23 22:53', '2013-1-31 13:48', '2013-2-19 11:50' ),
( 6, 10000004, 'P04', '2012-1-16 8:01', '2012-11-21 2:04', '2012-11-23 11:30' ),
( 7, 10000004, 'P11', '2013-4-5 0:53', '2013-4-9 4:40', '2013-5-8 11:57' ),
( 8, 10000004, 'P11', '2011-11-1 10:13', '2012-5-7 14:20', '2012-5-28 16:00' ),
( 9, 10000004, 'P08', '2012-5-28 20:34', '2013-1-26 4:30', '2013-2-11 17:24' );

values
( 1, 10000005, 'P12', '2012-05-23 22:53', '2012-5-27 23:30', '2012-6-14 13:12' ),
( 1, 10000005, 'P12', '2013-05-23 22:53', '2013-5-27 23:30', '2013-6-14 13:12' );

DECLARE @StartDate DATETIME = '01-Jun-2012 00:00:00'
DECLARE @EndDate DATETIME = '31-May-2013 23:59:59';

SELECT
ClientID,
CareCluster,
IPDischargeDate,
case
when ( ipAdmissionDate >= @StartDate and ipAdmissionDate < @EndDate ) and ( ipDischargeDate >= @StartDate and ipDischargeDate <= @EndDate ) then DATEDIFF( day, ipAdmissionDate, ipDischargeDate )
when ( ipAdmissionDate < @StartDate ) and ( ipDischargeDate >= @StartDate and ipDischargeDate <= @EndDate ) then DATEDIFF( day, @StartDate, ipDischargeDate )
when ( ipAdmissionDate >= @StartDate and ipAdmissionDate < @EndDate ) and ( ipDischargeDate > @EndDate ) then DATEDIFF( day, ipAdmissionDate, @EndDate )
when ( ipAdmissionDate < @StartDate and ipDischargeDate > @EndDate ) then DATEDIFF( day, @startDate, @endDate )
else 0
end
as OccupiedBedDays
``````

Due to some of the counting, I think some of those may need to have 1 day added to them, but I figure you're most familiar with the requirements, so you can tell me if they do or don't!

Edit:

Also, I put the logic in the OccupiedBedDays calculation because I wasn't sure if you wanted the admission/discharge dates to actually change in the resultset or not?
0

Commented:
>>The bed days in the attached XL are incorrect because the DATEDIFF starts to count from  IPAdmission Date disregarding @StartDate<<
Are you saying that you want the number of days starting from the startdate, even if the admission date occurs after that? Or are you saying that the results in your sample are correct except for the two records where the admissiondate and dischargedate are outside the range?
0

Author Commented:
@awking00 - The expected Occupied Bed Days attached.

NB I calculated this in Excel. I measured a day by resetting the discharge time to 00:00. This is the key measure for an occupied bed day.

Output-with-Correct-Days.xlsx
0

Author Commented:
Are you saying that you want the number of days starting from the startdate, even if the admission date occurs after that?
No. If the Admission Date occurs after @StartDate then use the Admission Date. (See the algorithm above)

Or are you saying that the results in your sample are correct except for the two records where the admissiondate and dischargedate are outside the range?
No. The correct output is in the comment above.
0

Principal ConsultantCommented:
Here is what you can do.  You can eliminate the differences in Days where DATEDIFF makes decisions for you.

You can test this query to see if you get what you expect.

SELECT DATEDIFF(ss, CONVERT(DATETIME, CONVERT(VARCHAR(20), @StartDate, 101)), CONVERT(DATETIME, CONVERT(VARCHAR(20), @EndDate, 101))) / 86400

Basically you are taking the difference between the days in Seconds and dividing it by the number of seconds in a day (86400).
0

freelancerCommented:
NO points please (this solution is from awking00 above ID: 39256820)

those datetime2 fields don't like direct subtraction, so casting these to datetime permits the above approach to proceed
``````DECLARE @StartDate DATETIME = '01-Jun-2012 00:00:00'
DECLARE @EndDate DATETIME = '31-May-2013 23:59:59'

SELECT
ClientID,
CareCluster,
IPDischargeDate,
CASE WHEN (
)
AND (
ipDischargeDate >= @StartDate
AND ipDischargeDate <= @EndDate
)
THEN floor(cast((cast(ipdischargeDate as datetime) - cast(ipAdmissionDate as datetime)) AS FLOAT))
AND (
ipDischargeDate >= @StartDate
AND ipDischargeDate <= @EndDate
)
THEN floor(cast((cast(ipdischargeDate as datetime) - @StartDate) AS FLOAT))
WHEN (
)
AND (ipDischargeDate > @EndDate)
THEN floor(cast((@EndDate - cast(ipAdmissionDate as datetime)) AS FLOAT))
WHEN (
AND ipDischargeDate > @EndDate
)
THEN floor(cast((@EndDate - @StartDate) AS FLOAT))
ELSE 0
END AS OccupiedBedDays
``````
http://sqlfiddle.com/#!6/59231/5
0

Author Commented:
My final solution used some of @awking00 code.

Also, I released that I had gaps and islands in the main query that I had to resolve, so thanks for your assistance!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.