SQL Server Patient Census Average By Day and Hour
<p>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.  </p>

<p>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 <a href="http://www.sqlservercentral.com/Forums/Topic939818-338-1.aspx" rel="nofollow noreferrer">http://www.sqlservercentral.com/Forums/Topic939818-338-1.aspx</a>).  </p>

<pre><code>--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] &lt; d.HoDend 
  and w.[ThruDateTime] &gt;= 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'
</code></pre>

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

<pre><code>Hour  PatientCount
0     2
1     3
2     3
</code></pre>