HRM-10/DiscontinueEmployeeReport_DB_Changes.sql

28 lines
1.3 KiB
MySQL
Raw Permalink Normal View History

2026-11-09 14:41:04 +06:00
/* ============================================================================
Discontinue Employee Details report - supporting index (OPTIONAL)
----------------------------------------------------------------------------
The report (Manage Employee > Report > Discontinue Employee Details,
DiscontinueEmployeeReportDA) filters dbo.EMPLOYEE by
STATUS = 2 (Discontinued) AND PAYROLLTYPEID = @x
AND ENDOFCONTRACTDATE >= @FromDate AND ENDOFCONTRACTDATE < @ToDate+1
The report works without this index (no code depends on it). The index
lets SQL Server seek straight to the discontinued employees of one payroll
type in the date range instead of scanning every employee, which keeps the
count and each 100-row page fast as the EMPLOYEE table grows.
Run order: any time, before or after the code deploys. Guarded, safe to
re-run. It only adds an index: no table, column or data is changed.
============================================================================ */
IF NOT EXISTS (SELECT 1 FROM sys.indexes
WHERE object_id = OBJECT_ID('dbo.EMPLOYEE')
AND name = 'IX_Employee_Status_PayrollType_EndOfContract')
BEGIN
CREATE NONCLUSTERED INDEX IX_Employee_Status_PayrollType_EndOfContract
ON dbo.EMPLOYEE (STATUS, PAYROLLTYPEID, ENDOFCONTRACTDATE)
INCLUDE (DEPARTMENTID);
END;
GO