USE [PATIENT_DATA]
GO
/****** Object:  StoredProcedure [dbo].[sp_Med_24HoursDailyCensusByMonth]    Script Date: 06/16/2021 3:54:22 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[sp_Med_24HoursDailyCensusByMonth] --'01/01/2020'
@StartDate1 as VARCHAR(30)
  
as  
  
--Declare @StartDate as datetime  
  
--Set @StartDate ='01/01/2019'  
DECLARE @StartDate as datetime= Cast(@startdate1 as Datetime)  
declare @EndDate as datetime  
  
  
 set @EndDate = ltrim(rtrim(str(month(@StartDate)))) + '/' +  
 Patient_Data.dbo.Last_Day_Of_Month(@StartDate) + '/' +  
 ltrim(rtrim(str(Year(@StartDate)))) ;
 
 Declare @Remaining int,
		@Admitted int,
		@TotalInpatient int,
		@DischargedAlive int,
		@Died int,
		@TotalOut int,
		@RemainingTotalPx int,
		@AdmDcrTheSameDay int,
		@TIPSD int,
		@Cummulative int,
		@BOR decimal,
		@Month varchar(10),
		@Day varchar(10),
		@Year varchar(10),
		@Yesterday varchar(25)

Truncate table PATIENT_DATA..tbMedrec_24Hours_Cencus  
newloop:  
  

SET @Remaining = 
				(
					SELECT COUNT(IdNum) Total FROM  PATIENT_DATA..tbpatient  
/*					WHERE AdmDate <= convert(varchar(20),@StartDate,101) + ' 23:59:59.000'  
						  AND ((DcrDate IS NULL)  OR (DcrDate > convert(varchar(20),@StartDate,101) + ' 23:59:59.000' ))*/

					 WHERE convert(varchar(10),admDate,101) < @StartDate
						  AND ((DcrDate IS NULL)  OR convert(varchar(10),DcrDate,101) >= @StartDate)


				)

SET @Admitted =
				(
					SELECT COUNT(IdNum) Total FROM  PATIENT_DATA..tbpatient  
					WHERE AdmDate BETWEEN @StartDate AND @StartDate + ' 23:59:59.000'
				)

SET @TotalInpatient=(@Remaining + @Admitted)

SET @DischargedAlive=
				(
					SELECT COUNT(Idnum) Total FROM PATIENT_DATA..tbPatient
					WHERE (DcrDate BETWEEN @StartDate AND @StartDate + ' 23:59:59.000')
					AND ResultID NOT IN ('4')		
				)

SET @Died=
				(
					SELECT COUNT(Idnum) Total FROM PATIENT_DATA..tbPatient
					WHERE (DcrDate BETWEEN @StartDate AND @StartDate + ' 23:59:59.000')
					AND ResultID IN ('4')		
				)

SET @TotalOut= (@DischargedAlive + @Died)
SET @RemainingTotalPx= (@TotalInpatient +@TotalOut)
SET @AdmDcrTheSameDay= 
				(
					SELECT COUNT(IdNum) Total FROM  PATIENT_DATA..tbpatient  
					WHERE (AdmDate BETWEEN @StartDate AND @StartDate + ' 23:59:59.000')
					AND (DcrDate BETWEEN @StartDate AND @StartDate + ' 23:59:59.000')
				)

SET @TIPSD = (@RemainingTotalPx + @AdmDcrTheSameDay)



SET @Month = ( SELECT SUBSTRING(convert(varchar(25),@StartDate,101),1,2) + '/' )
SET @Day =
		( SELECT  CASE WHEN LEN(CAST((cast(SUBSTRING(convert(varchar(25),@StartDate,101),4,2) as Int) -1) as varchar)) <2 THEN  
							CASE WHEN DAY(@StartDate)=1 THEN
							'01/'
							ELSE
							'0' + CAST((cast(SUBSTRING(convert(varchar(25),@StartDate,101),4,2) as Int) -1) as varchar) + '/'
							END
				  ELSE
						   CAST((cast(SUBSTRING(convert(varchar(25),@StartDate,101),4,2) as Int) -1) as varchar) + '/'
				  END 
		)

SET @Year =
		(
			SELECT SUBSTRING(convert(varchar(25),@StartDate,101),7,4)
		)
SET @Yesterday = 
				(
					@Month + @Day +@Year
				)

INSERT INTO PATIENT_DATA..tbMedrec_24Hours_Cencus 
(
	Remaining,
	Admission,
	Discharge,
	Deaths,
	AdmAndDcr,
	ReportDate
)
SELECT  
		@Remaining Remaining,  /*Remaining since the Date given*/
		@Admitted Admitted,  /* Total of Admitted patient */
		@DischargedAlive DischargeAlive, /* Total Discharges (Alive) */
		@Died Died, /* Total Discharges (Died) */
		@AdmDcrTheSameDay AdmDcrTheSameDay, /* Admitted and Discharged on the same day */
		Convert(varchar(10),@StartDate, 101) ReportDate
		
if datepart(day,@Enddate) <> datepart(day,cast(@StartDate as datetime))  
begin  
 set @StartDate = DateAdd(day,1,cast(@StartDate as datetime))  
  goto newloop  
end;

SELECT * FROM PATIENT_DATA..tbMedrec_24Hours_Cencus