Insert Missing Periods

This script inserts the missing weekly pay period records into the ARCHIVES table for a specific employee. This is used to correct data gaps identified by the missing_periods_find.sql script.

Data-Modifying Script

This script permanently modifies the ARCHIVES table. It is highly recommended to first run the missing_periods_insert_safety_check.sql script to preview the changes before executing this one.

Description

The script calculates the missing weekly periods for a single employee (identified by FILEKEY) and inserts placeholder records for them into the ARCHIVES table. These new records will have zeroed-out values for hours and pay, establishing the correct data structure for future updates.

Key functionalities include:

Data Insertion: Creates new records in the ARCHIVES table.
Employee-Specific: Requires a FILEKEY to target a single employee for data correction.
Confirms Action: Reports the number of records that were successfully inserted.

How to Use

Run Safety Check First: Before using this script, run the missing_periods_insert_safety_check.sql script to confirm which records will be created.
Set Employee FILEKEY:
- Open this script in your SQL editor.
- Find the line DECLARE @FILEKEY INT = 6043;.
- Replace 6043 with the FILEKEY of the employee whose records you want to fix.
Execute the Script:
- Run the script. This will insert the missing records.
Verify the Result:
- The script will output a message confirming the number of inserted records. You can also re-run the missing_periods_find.sql script for that employee to confirm the gaps have been filled.

Download Alternative Formats

📥 Browser Export Format:

Download Export File - Download this export for external processing

missing_periods_insert.sql

SET NOCOUNT ON;

DECLARE @FILEKEY INT = 6043;

-- Replace with the specific employee's FILEKEY
WITH
    WeeklyPeriods
    AS
    (
        -- Generate all weekly periods from 4/20/2025 to current date
        SELECT
            DATEADD(WEEK, n, '2025-04-20') AS PERIODBEGIN,
            DATEADD(DAY, 6, DATEADD(WEEK, n, '2025-04-20')) AS PERIODEND
        FROM
            (
            SELECT
                TOP (DATEDIFF(WEEK, '2025-04-20', GETDATE()) + 1)
                ROW_NUMBER() OVER (
                    ORDER BY
                        (
                            SELECT
                                NULL
                        )
                ) - 1 AS n
            FROM
                sys.all_objects a
                CROSS JOIN sys.all_objects b
        ) Numbers
    ),
    EmployeeInfo
    AS
    (
        -- Get employee info and calculate first valid period
        SELECT
            @FILEKEY AS FILEKEY,
            e.ACTIVESTATUSEFFDATE,
            DATEADD(
            WEEK,
            -1,
            DATEADD(
                DAY,
                -(DATEPART(WEEKDAY, e.ACTIVESTATUSEFFDATE) - 1),
                CAST(e.ACTIVESTATUSEFFDATE AS DATE)
            )
        ) AS FirstValidPeriodStart
        FROM
            EMPLOYEES e
        WHERE
        e.FILEKEY = @FILEKEY
            AND e.ACTIVESTATUS = 0
    ),
    MissingPeriods
    AS
    (
        -- Find missing periods for this specific employee
        SELECT
            ei.FILEKEY,
            wp.PERIODBEGIN,
            wp.PERIODEND
        FROM
            EmployeeInfo ei
        CROSS JOIN WeeklyPeriods wp
            LEFT JOIN ARCHIVES a ON ei.FILEKEY = a.FILEKEY
                AND wp.PERIODBEGIN = a.PERIODBEGIN
                AND wp.PERIODEND = a.PERIODEND
        WHERE
        a.FILEKEY IS NULL
            AND wp.PERIODBEGIN >= ei.FirstValidPeriodStart
            AND wp.PERIODBEGIN <= GETDATE()
    )
-- Insert missing periods
INSERT INTO
    ARCHIVES
    (
    [FILEKEY],
    [EVENTDATE],
    [PERIODBEGIN],
    [PERIODEND],
    [GROSS],
    [HOURS],
    [DOP],
    [TRANSCOUNT],
    [EXFLAGS],
    [INDIFF],
    [OUTDIFF],
    [LUN],
    [LLUN],
    [SLUN],
    [LBRK],
    [SBRK],
    [SCHDATE],
    [SCHSTART],
    [SCHEND],
    [SCHHOURS],
    [SCHTYPE],
    [SCHWG1],
    [SCHWG2],
    [SCHWG3],
    [SCHINDEX],
    [SCHBEN],
    [SHIFTNUM],
    [STYLE],
    [STYLE3]
    )
SELECT
    FILEKEY,
    PERIODBEGIN as EVENTDATE,
    PERIODBEGIN,
    PERIODEND,
    0 as GROSS,
    0 as HOURS,
    0 as DOP,
    0 as TRANSCOUNT,
    0 as EXFLAGS,
    0 as INDIFF,
    0 as OUTDIFF,
    0 as LUN,
    0 as LLUN,
    0 as SLUN,
    0 as LBRK,
    0 as SBRK,
    '1899-12-30 00:00:00.000' as SCHDATE,
    '1899-12-30 00:00:00.000' as SCHSTART,
    '1899-12-30 00:00:00.000' as SCHEND,
    0 as SCHHOURS,
    0 as SCHTYPE,
    0 as SCHWG1,
    0 as SCHWG2,
    0 as SCHWG3,
    0 as SCHINDEX,
    0 as SCHBEN,
    0 as SHIFTNUM,
    0 as STYLE,
    0 as STYLE3
FROM
    MissingPeriods
ORDER BY
    PERIODBEGIN;

-- Show what was inserted
SELECT
    'Inserted ' + CAST(@@ROWCOUNT AS VARCHAR(10)) + ' missing pay periods for employee ' + CAST(@FILEKEY AS VARCHAR(10)) AS Result;

SET NOCOUNT OFF;

Content Inventory

Doc File: content/docs/utilities/missing_periods_insert.mdx
SQL Script: SQL/utilities/missing_periods_insert.sql
Browser Export: public/aod/browser_exports/utilities/missing_periods_insert.browser_export

Insert Missing Periods

Description

How to Use

Download Alternative Formats

Content Inventory

On this page