USE [PATIENT_DATA]
GO
/****** Object:  StoredProcedure [dbo].[sp_Med_Insert_24hour_Census]    Script Date: 06/16/2021 2:13:02 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
--
ALTER Procedure [dbo].[sp_Med_Insert_24hour_Census]
@startDate as varchar(30),
@EndDate as varchar(30)=''


as

delete tbMedrec_24Hours_Cencus where ReportDate = @startDate
Insert tbMedrec_24Hours_Cencus
		
		select 
			sum(case when AdmDate < @StartDate and
					 IsNull(DcrDate, GetDate()) >= @StartDate 
/*			sum(case when convert(varchar(10),AdmDate,101) < @StartDate
						  AND ((DcrDate IS NULL)  OR convert(varchar(10),DcrDate,101) >= @StartDate)
			 
	*/				 
					 then
						1 else 0 end) Remaining,
						
			sum(case when convert(varchar(10), AdmDate, 101) = @StartDate
					 then
						1 else 0 end) Admission, 
						
			sum(case when  convert(varchar(10), DcrDate, 101) = @StartDate and P.ResultID <> '4'
					 then
						1 else 0 end) Discharge, 
			sum(case when convert(varchar(10), DcrDate, 101) = @StartDate  and
					 P.ResultID = '4' then
						1 else 0 end) Deaths,
			sum(case when convert(varchar(10), DcrDate, 101) = @StartDate  and
						convert(varchar(10), admdate, 101) = @StartDate  then
						1 else 0 end) AdmandDcr,
			@startDate
		from Patient_Data..tbPatient P