Solved

Query to Count Days Within a Fixed Date Range

Posted on 2013-06-17
9
533 Views
Last Modified: 2013-06-25
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

If IPAdmissionDate is before @StartDate,
THEN use @StartDate
ELSE IPAdmissionDate


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,
IPAdmissionDate,
IPDischargeDate,
DATEDIFF(day,IPAdmissionDate,ISNULL(IPDischargeDate,@EndDate)) AS OccupiedBedDays	

FROM InpatientAdmissions

Open in new window

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?

Thanks in advance!

JohnAeris
0
Comment
Question by:JohnAeris
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 32

Expert Comment

by:awking00
ID: 39253809
Can you post what the occupied bed days should be per your sample?
0
 
LVL 8

Expert Comment

by:didnthaveaname
ID: 39253817
How does this work for you:

if( object_id( 'tempdb..#inpatientAdmissions_TEMP' ) is not null )
	drop table #inpatientAdmissions_TEMP;

create table #inpatientAdmissions_TEMP
(
	rowNum int not null,
	clientID bigint not null,
	careCluster varchar( 3 ),
	clusterAssessmentDate datetime2 not null,
	ipAdmissionDate datetime2 not null,
	ipDischargeDate datetime2 null
);

insert into #inpatientAdmissions_TEMP( rowNum, clientID, careCluster, clusterAssessmentDate, ipAdmissionDate, ipDischargeDate )
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' );


insert into #inpatientAdmissions_TEMP( rowNum, clientID, careCluster, clusterAssessmentDate, ipAdmissionDate, ipDischargeDate )
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,
	ipAdmissionDate,
	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
FROM #InpatientAdmissions_TEMP;	

Open in new window


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
 
LVL 32

Expert Comment

by:awking00
ID: 39253875
>>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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:JohnAeris
ID: 39254262
@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 Comment

by:JohnAeris
ID: 39254315
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
 
LVL 24

Expert Comment

by:DBAduck - Ben Miller
ID: 39256670
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

I tested it in your examples in your spreadsheet and it returned the correct number of actual bed days as you had in your calculations.

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

Accepted Solution

by:
awking00 earned 500 total points
ID: 39256820
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39261676
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,
	ipAdmissionDate,
	IPDischargeDate,
    CASE WHEN (
                  ipAdmissionDate >= @StartDate
                  AND ipAdmissionDate < @EndDate
                  )
              AND (
                  ipDischargeDate >= @StartDate
                  AND ipDischargeDate <= @EndDate
                  ) 
             THEN floor(cast((cast(ipdischargeDate as datetime) - cast(ipAdmissionDate as datetime)) AS FLOAT)) 
       WHEN (ipAdmissionDate < @StartDate)
              AND (
                  ipDischargeDate >= @StartDate
                  AND ipDischargeDate <= @EndDate
                  ) 
             THEN floor(cast((cast(ipdischargeDate as datetime) - @StartDate) AS FLOAT)) 
       WHEN (
                  ipAdmissionDate >= @StartDate
                  AND ipAdmissionDate < @EndDate
                  )
              AND (ipDischargeDate > @EndDate) 
             THEN floor(cast((@EndDate - cast(ipAdmissionDate as datetime)) AS FLOAT)) 
       WHEN (
                  ipAdmissionDate < @StartDate
                  AND ipDischargeDate > @EndDate
                  ) 
             THEN floor(cast((@EndDate - @StartDate) AS FLOAT))
       ELSE 0 
  END AS OccupiedBedDays
FROM InpatientAdmissions

Open in new window

http://sqlfiddle.com/#!6/59231/5
0
 

Author Closing Comment

by:JohnAeris
ID: 39274462
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

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question