SQL Temporary Table Operations Overview

Code Breakdown and Explanation

The code provided primarily deals with inserting, updating, and deleting records in a temporary table #wip_status_changes, as well as performing a final select with some transformations.

Temporary Table Creation

While the CREATE TABLE statement for the temporary table #wip_status_changes is not provided, it's implied that such a table exists. Below are the critical steps of the provided code:

Insert Data into Temporary Table

Insert Statement

INSERT #wip_status_changes
(
    enc_id,
    change_nbr,
    change_timestamp,
    from_status,
    to_status,
    from_status_order,
    to_status_order,
    status_out_of_order,
    enc_type,
    date_of_service,
    wip_conversion_date,
    billed_date,
    check_socket_status,
    primary_payer,
    financial_class,
    enc_nbr,
    clinician_name,
    patient_nbr,
    age,
    Zone,
    Region,
    PCC_name
)
SELECT WSC.enc_id,
       0 change_nbr,
       WSC.change_timestamp,
       from_status,
       'Pending' to_status,
       WSC.from_status_order,
       0 to_status_order,
       WSC.status_out_of_order,
       WSC.enc_type,
       WSC.date_of_service,
       WSC.wip_conversion_date,
       WSC.billed_date,
       WSC.check_socket_status,
       WSC.primary_payer,
       WSC.financial_class,
       WSC.enc_nbr,
       WSC.clinician_name,
       WSC.patient_nbr,
       WSC.age,
       WSC.Zone,
       WSC.Region,
       WSC.PCC_name
FROM #wip_status_changes WSC
WHERE WSC.from_status = ''
      AND WSC.to_status <> 'Pending';

• Functionality: This statement inserts records into the #wip_status_changes temporary table by selecting records from itself where the from_status is empty, and the to_status is not 'Pending'.
• Purpose: To prepare the data for further manipulations by setting default values such as change_nbr to 0 and changing to_status to 'Pending'.

Update Records

Update Statement

UPDATE #wip_status_changes
SET from_status = 'Pending'
WHERE from_status = ''
      AND to_status <> 'Pending';

• Functionality: This statement updates records in the #wip_status_changes table where from_status is empty, and to_status is not 'Pending', setting from_status to 'Pending'.
• Purpose: Ensure that any record that initially had an empty from_status now has 'Pending' as the from_status.

Delete Records

Delete Statement

DELETE #wip_status_changes
WHERE from_status = to_status
      AND change_nbr > 1;

• Functionality: This statement deletes records from #wip_status_changes where from_status is equal to to_status and change_nbr is greater than 1.
• Purpose: Remove redundant or erroneous records that do not indicate an actual status change but might have been erroneously processed.

Final Select with Transformations

Select Statement

SELECT WSC.enc_nbr,
       WSC.change_timestamp,
       ROW_NUMBER() OVER (PARTITION BY WSC.enc_id ORDER BY WSC.change_nbr) change_nbr,
       WSC.from_status,
       WSC.to_status,
       WSC.days_in_status,
       WSC.enc_type,
       WSC.date_of_service,
       WSC.wip_conversion_date,
       WSC.billed_date,
       WSC.primary_payer,
       WSC.financial_class,
       WSC.patient_nbr,
       WSC.age,
       IIF(WSC.age < 18, 'Yes', 'No') is_minor,
       WSC.clinician_name,
       WSC.Zone,
       WSC.Region,
       WSC.PCC_name,
       WSC.check_socket_status
FROM
(
    SELECT WSC.enc_id,
           WSC.enc_nbr,
           WSC.change_timestamp,
           WSC.change_nbr,
           WSC.from_status,
           WSC.to_status,
           ISNULL(
               DATEDIFF(
                   DAY,
                   LAG(WSC.change_timestamp) OVER (PARTITION BY WSC.enc_id ORDER BY WSC.change_timestamp),
                   WSC.change_timestamp
               ),
               0
           ) days_in_status,
           WSC.enc_type,
           WSC.date_of_service,
           WSC.wip_conversion_date,
           WSC.billed_date,
           WSC.check_socket_status,
           WSC.primary_payer,
           WSC.financial_class,
           WSC.patient_nbr,
           WSC.age,
           WSC.clinician_name,
           WSC.Zone,
           WSC.Region,
           WSC.PCC_name
    FROM #wip_status_changes WSC
) WSC
WHERE WSC.from_status <> ''
ORDER BY WSC.enc_nbr, WSC.change_timestamp;

• Functionality: This statement selects and transforms the required columns from the #wip_status_changes table.
  • Uses ROW_NUMBER() to enumerate each status change (change_nbr) partitioned by enc_id.
  • Calculates days_in_status by using DATEDIFF and LAG functions to find the difference in days between the current and the previous change_timestamp.
  • Uses IIF to determine if the patient is a minor (age < 18).
• Purpose: This final step prepares the data for reporting or further analysis by structuring the transformed data in the desired format and order.

Key Concepts Explained

1. Temporary Table (#wip_status_changes): A temporary table that stores intermediate data used for processing within the session.
2. ROW_NUMBER() Window Function: Used for assigning a unique sequential integer to rows within a partition of a dataset.
3. DATEDIFF and LAG: Used to calculate the difference between dates and retrieve data from the previous row within the same partition.
4. IIF: Conditional function returning one value if a condition evaluates to true, and another value if it evaluates to false.

Additional Example for ROW_NUMBER()

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

• In this example, ROW_NUMBER() assigns a sequential number to each employee within the same department, ordered by name.

Closing Notes

The overall code structure ensures that the data in the temporary table is cleaned, updated, and transformed systematically. The final select query provides a structured dataset useful for downstream tasks such as reporting and analysis. The explanation highlighted key SQL functionalities and provided clarity on each step.

For further learning on advanced SQL concepts and practical examples, the Enterprise DNA Platform offers comprehensive courses to enhance your data processing skills.