Daily Nurse Schedule Report

This comprehensive report generates a daily nursing schedule organized by shift times (day, evening, and night shifts) for specific nursing departments. The report combines scheduled employees with open shifts to provide a complete view of staffing coverage.

Parameters

@wg1 - Work group 1 identifier (default: 1)
@wg2 - Work group 2 identifier (optional, commented out in current version)
@min - Start date for the report range (format: {mindate})
@max - End date for the report range (format: {maxdate})

Data Components

The report consists of 3 main shift sections:

Day Shifts (dayys) - Covers shifts from 06:00:00 to 13:59:00
Evening Shifts (evenings) - Covers shifts from 14:00:00 to 21:59:00
Night Shifts (nights) - Covers shifts from 22:00:00 to 05:59:00 (spanning two days)

Each section includes:

Scheduled employees with their assigned shifts
Open shifts from unfulfilled schedules (QTYREMAIN 1, 2, and 3)
Work group categorization with special handling for Training, Restorative, and Admissions roles

Output Format

The report displays three columns side by side showing staffing for each shift:

Day Shift	Evening Shift	Night Shift
Department - Position	Department - Position	Department - Position
Employee Name	Employee Name	Employee Name
Shift Time	Shift Time	Shift Time

Technical Implementation

The script uses:

Common Table Expressions (CTEs) for organizing data by shift type
UNION ALL operations to combine scheduled and open shifts
ROW_NUMBER() window functions for proper alignment across shifts
FULL JOIN operations to align shifts by row number across all three shift types
Time range filtering with CONVERT(TIME) for precise shift categorization
Special case handling for Training (wg3: 94,92,90), Restorative, and Admissions positions
Cross-day logic for night shifts that span midnight

Work Groups Included

Work Group 3 numbers: 7, 9, 11, 90, 92, 94, 18
Schedule type: 0 (regular schedules)
Excludes benefit schedules and other non-standard schedule types

T-SQL

DailyNurseSched.sql

declare @wg1 as int,@wg2 as int, @min as datetime, @max as datetime

set @wg1 = 1
--set @wg2 =1
set @min = {mindate}
set @max = {maxdate}
;
with dayys as

(
select t.wg3 as wg3,t.schdate as schdate,t.shifttime as shifttime,t.empname as empname,row_number() over (order by case when t.wg3 like 'Restorative'
then 'CNA Restorative'
when t.wg3 like 'Admissions'
then 'Nurse Admissions'
when t.wg3 like 'Training%'
then substring(t.wg3, charindex(' ',t.wg3)+1, 20)
else t.wg3
end,t.schdate)as numero
from 
(
select distinct wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.starttime,108)+' - '+ convert(varchar(5),sch.endtime,108) as shifttime,
e.firstname+' '+e.lastname as empname
/*row_number() over (order by case when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end,sch.starttime) numero
*/
from schedules sch
inner join employees e
on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.starttime) between '06:00:00' and '13:59:00'
and sch.schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0

--dayysu as
union all

select distinct wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname
/*row_number() over (order by case when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end,sch.mindate) numero
*/
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '06:00:00' and '13:59:00'
and sch.schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.QTYREMAIN = 1
union all
select distinct wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname
/*row_number() over (order by case when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end,sch.mindate) numero
*/
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '06:00:00' and '13:59:00'
and sch.schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
and sch.QTYREMAIN = 2
union all
select distinct wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname
/*row_number() over (order by case when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end,sch.mindate) numero
*/
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '06:00:00' and '13:59:00'
and sch.schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
and sch.QTYREMAIN = 3

)t
),



--select *
--from dayys
evenings as 
(
select t.wg3 as wg3,t.schdate as schdate,t.shifttime as shifttime,t.empname as empname,row_number() over (order by case when t.wg3 like 'Restorative'
then 'CNA Restorative'
when t.wg3 like 'Admissions'
then 'Nurse Admissions'
when t.wg3 like 'Training%'
then substring(t.wg3, charindex(' ',t.wg3)+1, 20)
--added new 
else t.wg3
end,t.schdate)as  numero
from 
(
select wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.starttime,108)+' - '+ convert(varchar(5),sch.endtime,108) as shifttime,
e.firstname+' '+e.lastname as empname,
row_number() over (order by case when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end,sch.starttime) numero
from schedules sch
inner join employees e
on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.starttime) between '14:00:00' and '21:59:00'
and schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0

--Eveningsu as
union
select wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname,
row_number() over (order by case when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end,sch.mindate) numero
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '14:00:00' and '21:59:00'
and schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
union 
select wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname,
row_number() over (order by case when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end,sch.mindate) numero
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '14:00:00' and '21:59:00'
and schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
and sch.qtyremain = 2
union
select wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname,
row_number() over (order by case when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end,sch.mindate) numero
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '14:00:00' and '21:59:00'
and schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
and sch.qtyremain = 3
)t
),
Nights as

(

select t.wg3 as wg3,t.schdate as schdate,t.shifttime as shifttime,t.empname as empname,row_number() over (order by case when t.wg3 like 'Restorative'
then 'CNA Restorative'
when t.wg3 like 'Admissions'
then 'Nurse Admissions'
when t.wg3 like 'Training%'
then substring(t.wg3, charindex(' ',t.wg3)+1, 20)
else t.wg3
end,t.schdate,t.starttime desc)  as numero
from 
(
select  wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,
convert(varchar(5),sch.starttime,108)+' - '+ convert(varchar(5),sch.endtime,108) as shifttime,
e.firstname+' '+e.lastname as empname,
sch.starttime as starttime


from schedules sch
inner join employees e
on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.starttime) between '22:00:00' and '23:59:00'
and schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.wg4 not in (41)
or convert(time,sch.starttime) between '00:00:00' and '05:59:00'
and schdate between dateadd(dd,+1,@min) and dateadd(dd,+1,@max)
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.wg4 not in (41)

--Nightsu as
union
select  wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,

convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname,
sch.mindate as starttime
--row_number() over (order by wg4.name,sch.schdate,sch.mindate desc) numero
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '22:00:00' and '23:59:00'
and schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.wg4 not in (41)
or convert(time,sch.mindate) between '00:00:00' and '05:59:00'
and schdate between dateadd(dd,+1,@min) and dateadd(dd,+1,@max)
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.wg4 not in (41)
union
select  wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,

convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname,
sch.mindate as starttime
--row_number() over (order by wg4.name,sch.schdate,sch.mindate desc) numero
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '22:00:00' and '23:59:00'
and schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.wg4 not in (41)
or convert(time,sch.mindate) between '00:00:00' and '05:59:00'
and schdate between dateadd(dd,+1,@min) and dateadd(dd,+1,@max)
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.wg4 not in (41)
and sch.qtyremain = 2
union 
select  wg3.name+' - '+case when wg3.wgnum in (94,92,90)
		then 'Training'+' '+wg4.name
		when wg4.name like 'Restorative'
then 'CNA Restorative'
when wg4.name like 'Admissions'
then 'Nurse Admissions'
else wg4.name 
end as wg3,
sch.schdate as schdate,

convert(varchar(5),sch.mindate,108)+' - '+ convert(varchar(5),sch.maxdate,108) as shifttime,
'OPEN SHIFT' as empname,
sch.mindate as starttime
--row_number() over (order by wg4.name,sch.schdate,sch.mindate desc) numero
from unfulfilledschs sch
--inner join employees e
--on e.filekey = sch.filekey
inner join workgroup3 wg3
on wg3.wgnum = sch.wg3
inner join workgroup4 wg4
on wg4.wgnum = sch.wg4
where convert(time,sch.mindate) between '22:00:00' and '23:59:00'
and schdate between @min and @max
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.wg4 not in (41)
or convert(time,sch.mindate) between '00:00:00' and '05:59:00'
and schdate between dateadd(dd,+1,@min) and dateadd(dd,+1,@max)
and sch.wg1 = @wg1
--and sch.wg2 = @wg2
and sch.wg3 in (7,9,11,90,92,94,18)
and sch.schtype = 0
--and sch.wg4 not in (41)
and sch.QTYREMAIN = 3

)t
)


select   d.wg3 as wg3,
	d.empname as empname, 
	d.shifttime as shft,
--	d.numero as dnumero
--	,
--'blank' as blank,
	e.wg3 as ewg3,
	e.empname as eempname,
	e.shifttime as eshft,
--	e.numero as enumero
--, 
--'blank' as eblank,
	n.wg3 as nwg3,
	n.empname as nempname,
	n.shifttime as nshft
--	n.numero as nnumero
	
from dayys d
full join evenings e
on e.schdate = d.schdate
and e.numero = d.numero

full join nights n
--on n.schdate = d.schdate
on d.numero = n.numero
and n.numero = e.numero

Content Inventory

Doc File: content/docs/reports/schedule/daily_nurse_schedule.mdx
SQL Script: SQL/reports/schedule/DailyNurseSched.sql