USE DWDashboardSource ;
GO
CREATE VIEW dbo.vPatientVolumeByPosition_ActualHours AS
WITH ctePatientVolumns (Center,DSStartDate,DSEndDate,TotalPatients) AS
(
	SELECT
	Center = 
		CASE LEN(ds.CENTER)
			WHEN  1 THEN '10' + CAST(ds.CENTER AS VARCHAR(2))
			WHEN  2 THEN '1' + CAST(ds.CENTER AS VARCHAR(2))
		END
	,DSStartDate = CONVERT(CHAR(10),MAX([DATE]) - 6,101)
	,DSEndDate = CONVERT(CHAR(10),MAX([DATE]),101)
	,TotalPatients = CAST(AVG(ds.[TOTAL PATIENTS]) AS DECIMAL(8,2))
	FROM
	dbo.DailySummary ds
	INNER JOIN
	(
		SELECT
		Center
		,StartDSDate = CONVERT(CHAR(10),MAX([DATE]) - 6,101)
		,EndDSDate = CONVERT(CHAR(10),MAX([DATE]),101)
		FROM
		dbo.DailySummary
		WHERE
		CENTER IS NOT NULL
		GROUP BY
		CENTER
	) x
		ON ds.CENTER = x.CENTER
	WHERE
	ds.[DATE] BETWEEN x.StartDSDate AND x.EndDSDate
	GROUP BY
		CASE LEN(ds.CENTER)
			WHEN  1 THEN '10' + CAST(ds.CENTER AS VARCHAR(2))
			WHEN  2 THEN '1' + CAST(ds.CENTER AS VARCHAR(2))
		END
)
SELECT
StartDateRange = pv.DSStartDate
,EndDateRange = pv.DSEndDate
,Center = std.center
,std.Position
,PatientVolume = CAST(pv.TotalPatients AS INT)
,ActualHours = std.AvgFloorHours
FROM
(
	SELECT
	[Date] = x.MaxDate
	,x.Center
	,x.Position
	,AvgFloorHours = CAST(AVG(x.FloorHours) AS DECIMAL(8,2))
	FROM
	(
		SELECT
		x.MaxDate
		,Center = std.center
		,Position =
			CASE
				WHEN sp.Position IN (3,6) THEN 'Nurse/DMS/MA'
				ELSE sp.Department
			END
		,FloorHours = CAST(SUM(ISNULL(CAST([floor hours] AS DECIMAL(8,2)),0)) AS DECIMAL(8,2))
		FROM
		dbo.StaffTimeDetails std
		INNER JOIN
		dbo.StaffDepartments sp
			ON std.position = sp.Position
		INNER JOIN
		(
			SELECT MaxDate = CONVERT(CHAR(10),MAX(CAST([date] AS SMALLDATETIME)),101)
			FROM dbo.StaffTimeDetails
		) x
			ON 1 = 1
		WHERE
		CAST(std.[date] AS SMALLDATETIME) > CAST(x.MaxDate AS SMALLDATETIME) - 7
		--CONVERT(CHAR(10),CAST(std.[date] AS SMALLDATETIME),101) > CONVERT(CHAR(10),CAST(x.MaxDate AS SMALLDATETIME) - 7,101)
		GROUP BY
		std.[date]
		,x.MaxDate
		,std.center
		,	CASE
				WHEN sp.Position IN (3,6) THEN 'Nurse/DMS/MA'
				ELSE sp.Department
			END
	) x
	GROUP BY
	x.MaxDate
	,x.Center
	,x.Position
) std
INNER JOIN
ctePatientVolumns pv
	ON std.center = pv.Center
	AND std.[Date] = pv.DSEndDate
GROUP BY
pv.DSStartDate
,pv.DSEndDate
,std.center
,std.Position
,pv.TotalPatients
,std.AvgFloorHours
;