CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MCW3u
MCW3u

Reputation: 13

SQL Server Patient Census Average By Day and Hour

I need to create a patient census report that shows average number patients present per hour and per day of a week over a given time period. This would allow me to show, for example, over the last 6 months there was an average of 4 people in the ER on Mondays. I have a table valued function that will show the following for patients: VisitID, FromDateTime, ThruDateTime, LocationID.

I was able to show the number of patients in, for example, the ER for a given day using the code below. But it is limited to one day only. (Adapted from http://www.sqlservercentral.com/Forums/Topic939818-338-1.aspx). 

--Census Count by Date Range--
DECLARE @BeginDateParameter DateTime, @EndDateParameter DateTime
SET @BeginDateParameter = '20160201'
SET @EndDateParameter = '2016-02-01 23:59:59.000'
----------------------------------------------------
-- Create a temp table to hold the necessary values
-- plus an extra "x" field to track processing
----------------------------------------------------
IF OBJECT_ID('tempdb..#Temp') IS NOT NULL  DROP TABLE #Temp
CREATE TABLE #Temp (ID INT Identity NOT NULL, VisitID VarChar(100), SourceID VarChar(100),
  FromDateTime DateTime, ThruDateTime DateTime, x INT)

----------------------------------------------------
-- Populate the temp table with values from the
-- the actual table in the database
----------------------------------------------------
INSERT INTO #Temp
SELECT VisitID, FromDateTime, ThruDateTime
FROM PatientFlowTable(BeginDateParameter,@EndDateParameter)
WHERE (FromDateTime BETWEEN @BeginDateParameter AND @EndDateParameter +1
OR ThruDateTime BETWEEN @BeginDateParameter AND @EndDateParameter +1)
AND LocationID = 'ER'


-- Given Period is taken as inclusive of given hours in the input (eg. 15:25:30 will be taken as 15:00:00)
-- frist make sure that the minutes, seconds and milliseconds are removed from input range for clarity
set @BeginDateParameter = dateadd(hh, datepart(hh,@BeginDateParameter), convert(varchar(12),@BeginDateParameter,112))
set @EndDateParameter = dateadd(hh, datepart(hh,@EndDateParameter), convert(varchar(12),@EndDateParameter,112))

-- you may create this CTE by other ways (eg. from permanent Tally table)...
;with dh
as
(
select top 24 
    DATEADD(hour,ROW_NUMBER() OVER (ORDER BY [Object_id])-1,convert(varchar(12),@BeginDateParameter,112)) as HoDstart
   ,DATEADD(hour,ROW_NUMBER() OVER (ORDER BY [Object_id]),convert(varchar(12),@BeginDateParameter,112)) as HoDend
   ,ROW_NUMBER() OVER (ORDER BY Object_id)-1 as DayHour  
from sys.columns -- or any other (not very big) table which have more than 24 raws, just remamber to change 
                 -- [Object_id] in OVER (ORDER BY [Object_id]... to some existing column 
)
select d.DayHour, count(w.VisitID) as PatientCount
from dh d  
left join #Temp w
on    w.[FromDateTime] < d.HoDend 
  and w.[ThruDateTime] >= d.HoDstart  
where d.HoDstart between @BeginDateParameter and @EndDateParameter
group by d.DayHour
order by d.DayHour

SELECT VisitID, FromDateTime, ThruDateTime
FROM PatientFlowTable(BeginDateParameter,@EndDateParameter)
WHERE (FromDateTime BETWEEN @BeginDateParameter AND @EndDateParameter +1
OR ThruDateTime BETWEEN @BeginDateParameter AND @EndDateParameter +1)
AND LocationID = 'ER'

Output example for the first three hours show patients that were present in the ER by taking into account their departure time.

Hour  PatientCount
0     2
1     3
2     3

Upvotes: 0

Views: 389

Answers (1)

fbcomps
fbcomps

Reputation: 169

For querying short time periods, I would create a table-valued function that generates the hour entries. The results table can be joined into your query.

CREATE FUNCTION [dbo].[f_hours] (@startDateTime DATETIME,
                                 @endDateTime   DATETIME)
RETURNS @result TABLE (
  [dateTime] DATETIME PRIMARY KEY
)
AS
BEGIN
  DECLARE
    @dateTime DATETIME = @startDateTime,
    @hours    INT      = DATEDIFF(hour, @startDateTime, @endDateTime)

  WHILE (@dateTime <= @endDateTime)
  BEGIN
    INSERT  
      INTO @result
      VALUES (@dateTime)

    SET @dateTime = DATEADD(hour, 1, @dateTime)
  END

  RETURN
END

GO

The time required by the function can be output with SET STATISTICS TIME ON. For the generation of over 6000 records needs my computer 53 ms.

SET STATISTICS TIME ON

SELECT *
  FROM [dbo].[f_hours]('2016-02-01', '2016-02-10 16:00')

SET STATISTICS TIME OFF

Upvotes: 2

Related Questions