SQL Script for Tracking Work in Progress Status

Code Analysis

The provided code snippet is a SQL script that involves creating a temporary table #wip_status_changes and updating it in several steps. The script has the following main components:

1. Table Creation: Creates a temporary table and populates it with data from two joined tables.
2. Status Transformation: Updates the temporary table to refine status fields.
3. Order Validation: Marks records where the status order is out of sequence.

Simplified Explanation

The code performs the following steps:

1. Creation of Temporary Table #wip_status_changes

The first part of the script selects and inserts data into the temporary table #wip_status_changes.

SELECT 
    EL.enc_id,
    ROW_NUMBER() OVER (PARTITION BY AU.wip ORDER BY AU.create_timestamp) AS change_nbr,
    AU.create_timestamp AS change_timestamp,
    CAST(NULL AS VARCHAR(300)) AS from_status,
    CASE
        WHEN AU.txt_status_detail = 'Pending' THEN 'Pending, '
        WHEN AU.txt_status_detail = 'Delivered' THEN 'Delivered, '
        WHEN AU.chk_admin_complete = 1 THEN 'Admin Complete, '
        ELSE 'Pending: ' + CONCAT(
                            IIF(AU.chk_prescription = 1, 'Prescription, ', ''), 
                            IIF(AU.chk_authorization = 1, 'Authorization, ', '')
                         )
    END AS to_status,
    CAST(NULL AS INT) AS from_status_order,
    CASE AU.txt_status_detail
        WHEN 'Pending' THEN 1
        WHEN 'Admin Complete' THEN 3
        WHEN 'Delivered' THEN 4
        ELSE 2
    END AS to_status_order,
    CAST(0 AS BIT) AS status_out_of_order,
    EL.enc_type,
    EL.date_of_service,
    EL.wip_conversion_date,
    EL.billed_date,
    CAST(0 AS BIT) AS check_socket_status,
    EL.primary_payer,
    EL.financial_class,
    EL.enc_nbr,
    EL.clinician_name,
    EL.patient_nbr,
    EL.age,
    EL.Zone,
    EL.Region,
    EL.PCC_name
INTO #wip_status_changes
FROM #enc_list EL
INNER JOIN dbo.HNG_Provider_Worklog_au_ AU
ON AU.wip = CONVERT(VARCHAR(36), EL.enc_id)
WHERE (
   AU.chk_prescription = 1
   OR AU.chk_authorization = 1
   OR AU.chk_admin_complete = 1
   OR AU.txt_status_detail IN ( 'Pending', 'Delivered' )
)
ORDER BY EL.enc_nbr, AU.create_timestamp;

• PARTITION BY AU.wip ORDER BY AU.create_timestamp: This clause partitions the data by AU.wip and orders them by AU.create_timestamp, assigning a unique row number (change_nbr) to each record within the partition.
• CASE Statements: The CASE constructs are used to define to_status and to_status_order based on different conditions.
• FROM and JOIN: Joins the #enc_list table with dbo.HNG_Provider_Worklog_au_ to filter and transform data.

2. Updating #wip_status_changes for to_status

The first update operation trims the trailing comma from to_status and sets values for from_status and from_status_order based on the previous row's to_status and to_status_order.

UPDATE WSC
SET WSC.to_status = LEFT(WSC.to_status, LEN(WSC.to_status) - 1),
    WSC.from_status = L.last_status,
    WSC.from_status_order = L.last_status_order
FROM #wip_status_changes WSC
INNER JOIN (
    SELECT 
        WSC2.enc_id,
        WSC2.change_nbr,
        LAG(LEFT(WSC2.to_status, LEN(WSC2.to_status) - 1), 1, '') OVER (PARTITION BY WSC2.enc_id ORDER BY WSC2.change_nbr) AS last_status,
        LAG(WSC2.to_status_order, 1, NULL) OVER (PARTITION BY WSC2.enc_id ORDER BY WSC2.change_nbr) AS last_status_order
    FROM #wip_status_changes WSC2
) L
ON L.enc_id = WSC.enc_id AND L.change_nbr = WSC.change_nbr;

• LEFT(WSC.to_status, LEN(WSC.to_status) - 1): Removes the last character (comma) from to_status.
• LAG Function: Uses the LAG window function to fetch values from the previous row in the defined order.

3. Marking Status Order out of Sequence

The final update marks records as out of order if from_status_order is greater than to_status_order.

UPDATE WSC
SET WSC.status_out_of_order = 1
FROM #wip_status_changes WSC
WHERE from_status_order > to_status_order;

Key Concepts

1. WINDOW Functions: Functions like ROW_NUMBER() and LAG() are vital in creating and managing sequences and looking up previous row data.
2. CASE Statements: Used to implement conditional logic within SQL queries.
3. INNER JOIN: Combines records from two tables based on a related column, used here to integrate data from #enc_list and HNG_Provider_Worklog_au_.

Additional Examples

Simple ROW_NUMBER() Example

SELECT 
   name, 
   ROW_NUMBER() OVER (ORDER BY name) AS row_num
FROM employees;

This assigns a unique incrementing number to each row ordered by the name column.

Usage of LAG()

SELECT 
   employee_id, 
   salary, 
   LAG(salary, 1) OVER (ORDER BY employee_id) AS previous_salary
FROM employees;

This fetches the previous row's salary value for each employee_id.

Conclusion

This SQL script sets up and manipulates a temporary table to track changes in status and ensure data consistency. The use of advanced SQL features such as window functions and conditional logic enables detailed data tracking and validation. For further learning, explore data transformation courses on the Enterprise DNA Platform.