CREATE    Procedure spCensusByHour (@StartDate datetime, @EndDate datetime) AS

/* Create by Acmeware, Inc., All Rights Reserved					*/
/*--------------------------------------------------------------------------------------*/
/* This Stored Procedure computes the average inpatient census during a given hour of   */
/* the day over a user defined period.  This can be used to identify the hour-of-day 	*/
/* where the census is the maximim and the actual maximum daily census, as opposed	*/
/* to the census at midnight. The hourly census detail, and monthly averages can also be*/
/* displayed by changing the "commented out" section at the end of this SP. 		*/
/*											*/
/* Parameters: 										*/
/*	@StartDate - beginning date on which the average is to be computed		*/
/*	@EndDate - ending date on which the average is to be computed			*/
/*											*/
/*	Output: 24 records for hours 0 - 23 with the average census during that hour	*/
/*		of the day								*/
/*											*/
/* Testing: EXEC spCensusByHour '7/1/2006','12/31/2006'					*/
/*--------------------------------------------------------------------------------------*/
/*											*/
/* THIS SOFTWARE IS PROVIDED "AS IS" AND WITHOUT ANY EXPRESS OR IMPLIED WARRANTIES, 	*/
/* INCLUDING, WITHOUT LIMITATION, THE IMPLIED WARRANTIES OF MERCHANTIBILITY AND 	*/
/* FITNESS FOR A PARTICULAR PURPOSE.							*/


SELECT @EndDate AS Date
INTO #TempDate
DECLARE @cnt int
SET @cnt = 1
WHILE @cnt < DateDiff(day, @StartDate, @EndDate)
BEGIN
	INSERT #TempDate Values(DateAdd(day, (-1 * @cnt), @EndDate))
	SET @cnt= @cnt + 1
END

SELECT 0 AS Hour
INTO #TempHour
SET @cnt=1
WHILE @cnt < 24
	BEGIN
	INSERT #TempHour Values(@cnt) 
	SET @cnt= @cnt + 1
END


SELECT Date, Hour
INTO #TempDateTime
FROM #TempDate
CROSS JOIN #TempHour


SELECT DateAdd(day, 1, ASS.DateTime) AS Date,
	TH.Hour,
	Sum(PatientDays) AS Census
INTO #TempCensus
FROM livedb.dbo.AdmStatsService ASS
CROSS JOIN #TempHour TH
WHERE 	DateTime BETWEEN @StartDate AND @EndDate
GROUP BY 
	DateTime, 
	TH.Hour


SELECT 
	TD.Date AS Date,
	TD.Hour As Hour, 
	Count(DischargeDateTime) AS Discharges,
	0 AS RunningDis
INTO #TempDischarges
FROM #TempDateTime TD
LEFT JOIN livedb.dbo.BarVisits BV
	ON (TD.Hour = DatePart(Hour, DischargeDateTime))
	AND (TD.Date = Convert(datetime, Convert(char, DischargeDateTime, 12), 12))
	AND BV.InpatientOrOutpatient = 'I'
GROUP BY 
	TD.Date,
	TD.Hour
ORDER BY 
	Date, 
	Hour

UPDATE #TempDischarges
SET RunningDis =(SELECT Sum(Discharges) 
	FROM #TempDischarges T2 
	WHERE T1.Date = T2.Date
		And (T1.Hour >= T2.Hour))
FROM #TempDischarges T1

SELECT TD.Date AS Date,
	TD.Hour As Hour, 
	Count(AdmitDateTime) AS Admissions,
	0 AS RunningAdm
INTO #TempAdmissions
FROM #TempDateTime TD
LEFT JOIN livedb.dbo.BarVisits BV
	ON (TD.Hour = DatePart(Hour, AdmitDateTime))
	AND (TD.Date = Convert(datetime, Convert(char, AdmitDateTime, 12), 12))
	AND BV.InpatientOrOutpatient = 'I'
GROUP BY 
	TD.Date,
	TD.Hour
ORDER BY 
	Date, 
	Hour

UPDATE #TempAdmissions
SET RunningAdm =(SELECT Sum(Admissions) 
	FROM #TempAdmissions T2 
	WHERE T1.Date = T2.Date
		And (T1.Hour >= T2.Hour))
FROM #TempAdmissions T1

SELECT
	TC.Date, 
	TC.Hour, 
	TC.Census AS StartCensus,
	TA.RunningAdm,
	TD.RunningDis,
	ISNULL(TC.Census,0)  +  ISNULL(TA.RunningAdm, 0)  -  ISNULL(TD.RunningDis,0) AS Census
INTO #CensusByHour
	FROM #TempCensus TC
	INNER JOIN #TempDischarges TD
		ON (TC.Date = TD.Date)
		AND(TC.Hour = TD.Hour)
	INNER JOIN #TempAdmissions TA
		ON (TC.Date = TA.Date)
		AND(TC.Hour = TA.Hour)
ORDER BY 
	TC.Date, 
	TC.Hour



/*

SELECT * 
FROM #CensusByHour
*/

/*
SELECT 
	Month(Date) AS [Month], 
	Year(Date) AS [Year], 
	Hour, 
	Avg(Census)
FROM #CensusByHour
GROUP BY 	
	Month(Date),
	Year(Date),
	Hour
ORDER BY [Year], [Month], Hour
*/


SELECT Hour, Avg(Cast (Census as decimal)) AS Census
FROM #CensusByHour
GROUP BY Hour
ORDER BY Hour