Hourly Status Change Near Holiday Report

Report that identifies employees whose hourly status changed near holidays within a specified date range, helping administrators identify potential issues with status changes that coincide with holidays

Parameters

{mindate} - Start date for the query (example: '12/20/2021')
{maxdate} - End date for the query (example: '12/31/2021')

Data Components

The report consists of two main sections:

Holiday Check - Verifies if there are holidays within the specified date range
Status Changes - If holidays exist, displays:
- Holiday dates in the period (with headers and separators)
- Employee status changes that occurred within the date range

Output Format

If holidays exist in the period, the report outputs a combined result set with holiday information and employee status changes:

Column1	Column2	Column3	Column4	Column5
0	Holiday Dates	null	null	null
1	[Holiday Date]	null	null	null
...	...	...	...	...
2	null	null	null	null
3	Employee Name	ID Number	Hourly Status	Status Effective Date
...	...	...	...	...

If no holidays exist in the period, the report only outputs the message: "No Holiday Date in search period"

Technical Implementation

The script uses:

SET NOCOUNT ON and SET ANSI_WARNINGS OFF to improve performance
Conditional execution based on whether holidays exist in the period
UNION operations to combine headers, holiday dates, separator, and employee records
Ordering by the first and second columns to maintain the proper report structure
Date formatting with CONVERT to display dates in a consistent format (MM/DD/YYYY)

T-SQL

hourly_status_change_near_holiday.sql

SET NOCOUNT ON
SET ANSI_WARNINGS OFF

Declare @min date, @max Date

Set @min = '12/20/2021'
set @max = '12/31/2021'
/*
Set @min = {mindate}
Set @max = {maxdate}
*/
If Exists (Select EFFDATE from HOLIDAYS h where h.EFFDATE between @min and @max group by EFFDATE having COUNT(*)>0)
	Begin

		Select '0', 'Holiday Dates',null,null,null
		UNION
		Select '1', Convert(varchar(60),EFFDATE,101),null,null,null from HOLIDAYS h where h.EFFDATE between @min and @max group by EFFDATE
		UNION
		Select '2',null,null,null,null
		UNION
		Select '3', e.LASTNAME+', '+e.FIRSTNAME as EMP, e.IDNUM, st.NAME as HourlyStatus, e.STATUSEFFDATE
		
		from EMPLOYEES e 
		inner join STATUSTYPES st
		on e.STATUS = st.NUM
		
		where e.STATUSEFFDATE between @min and @max

		order by 1,2

	End

	Else

	Begin

		Select 'No Holiday Date in search period'

	End

Content Inventory

Doc File: content/docs/reports/employee_information/hourly_status_change_near_holiday.mdx
SQL Script: SQL/reports/employee_information/hourly_status_change_near_holiday.sql