Daily Posting Report

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

DECLARE @MIN DATE, @MAX DATE
/*
SET @MIN={mindate}
SET @MAX={maxdate}
*/
SET @MIN='6/11/2023'
SET @MAX='6/11/2023'

DECLARE @Temp Table (Zone varchar(30),Count Int, Sch_Hours DECIMAL(38,2), Wk_Hours DECIMAL(38,2),RowOrder Int)
DECLARE @ShiftTimes Table (ShiftTime varchar(30), Starttime Varchar(30), Row_num int)
DECLARE @SchStaff Table (Count int,WG3 int,Position varchar(30),shifttime varchar(30),SchHours DECIMAL(38,2),Row_Num int)
DECLARE @WkHours Table (Filekey int, WG3 varchar(30), WkHours DECIMAL(38,2), Row_Num int)
DECLARE @P_Hours Table (Position varchar(30),Sum DECIMAL(38,2),Row_Num int)
DECLARE @UnSchCount int
DECLARE	@Facility int
DECLARE @POSITIONS Table(Num Smallint)
Set @Facility=9
insert into @POSITIONS(Num) Values (90),(107),(113),(139),(166),(167),(170),(171),(172),(185)

Set @UnSchCount = (
					Select COUNT(Distinct(filekey))
					from EMPLOYEES e
					where e.FILEKEY not in (Select FILEKEY from SCHEDULES where SCHDATE between @MIN and @MAX and convert(time,starttime) between '00:00:00' and '23:59:00' and WG1=@Facility and WG3=139 and SCHTYPE = 0)
					and e.WG1=@Facility
					and e.WG3=139
					and e.ACTIVESTATUS=0
				)

Insert into @ShiftTimes
	
	Select st.shifttime, st.Starttime, ROW_NUMBER() over (order by st.starttime) as Row_Num
		
	from (
	Select 
		Right(convert(varchar(20),starttime,100),7)+' - '+ Right(convert(varchar(20),endtime,100),7) as shifttime,
		cast(starttime as time) as Starttime
	From SCHEDULES
	where
		convert(time,starttime) between '00:00:00' and '23:59:00'
		and SCHDATE between @MIN and @MAX
		and WG1=@Facility
		and WG3 in (Select num from @POSITIONS)
		and SCHTYPE = 0
	)st
	group by st.shifttime, st.Starttime

Insert into @SchStaff

	Select s.Count,s.WGNum,s.Position,s.shifttime, s.SchHours, st.Row_Num

	From (
	Select 
		COUNT(Distinct(sch.filekey)) as Count,
		sch.WG3 as WGNum,
		wg3.name as Position,
		Right(convert(varchar(20),sch.starttime,100),7)+' - '+ Right(convert(varchar(20),sch.endtime,100),7) as shifttime,
		isnull(sum(sch.HOURS)/60.0,0) as SchHours
	From SCHEDULES sch
		inner join workgroup3 wg3
		on wg3.wgnum = sch.wg3
	where
		convert(time,sch.starttime) between '00:00:00' and '23:59:00'
		and sch.SCHDATE between @MIN and @MAX
		and sch.WG1=@Facility
		and sch.WG3 in (Select num from @POSITIONS)
		and sch.SCHTYPE = 0
		--and (Select Sum(hours) from PAYDESHIST where FILEKEY=sch.FILEKEY and EVENTDATE between @MIN and @MAX and PAYDESNUM in (1,2,17,23,24,25,28,29) )>0
	Group by sch.WG3, wg3.NAME, sch.SCHDATE, sch.STARTTIME, sch.ENDTIME
	)s
	Inner Join @ShiftTimes st
	on s.shifttime = st.shifttime


Insert into @WkHours
	
	Select
		e.FILEKEY,
		pd.WG3,
		isnull(Round(sum(pd.hours),2),0),
		st.Row_Num
	from (
	Select 
		s.filekey,
		Right(convert(varchar(20),s.starttime,100),7)+' - '+ Right(convert(varchar(20),s.endtime,100),7) as shifttime
	from SCHEDULES s
	where
		convert(time,s.starttime) between '00:00:00' and '23:59:00'
		and s.SCHDATE between @MIN and @MAX
		and s.WG1=@Facility
		and s.WG3 in (Select num from @POSITIONS)
		and s.SCHTYPE = 0
	)e
	Inner Join PAYDESHIST pd
	on e.FILEKEY = pd.FILEKEY
	Inner Join @ShiftTimes st
	on e.shifttime = st.shifttime
	Where pd.EVENTDATE between @min and @max
        And pd.PAYDESNUM in (1,2,17,23,24,25,28,29)
	Group by e.FILEKEY, pd.WG3, st.Row_Num

Insert into @P_Hours

	Select 
		w3.NAME, 
		isnull(SUM(wk.WkHours),0), 
		wk.Row_Num
	From @WkHours wk
	Inner Join WORKGROUP3 w3
	on wk.WG3 = w3.WGNUM
	Group by w3.NAME, wk.Row_Num

Insert into @Temp Select Distinct(s1.shifttime),null ,null ,null ,s1.Row_Num From @ShiftTimes s1
Insert into @Temp Select 'Census: '+ Convert(varchar,ITEMDATA1),null,null,null,0  from COVBUDGETS c2 where c2.EVENTDATE between @MIN and @MAX and c2.COVERAGETYPE=2 and c2.WG1=@Facility
Insert into @Temp Select Position,Count,SchHours,null,Row_Num from @SchStaff

Insert into @Temp Select null,null,null,null,1
Insert into @Temp Select null,null,null,null,999
Insert into @Temp Select 'Un-Scheduled RNs:',@UnSchCount,null,null,9999
Update @Temp Set Wk_Hours =  (Select SUM from @P_Hours where Zone=Position and RowOrder = Row_Num)

Select * from @Temp order by 5,1