Code Explainer | SQL

Out-of-Order Status Change Handler

This SQL script manages out-of-order status changes in a temporary table by updating statuses, deleting erroneous records, refreshing change numbers, and recalculating the out-of-order status in a loop until all changes are resolved.

Prompt

DECLARE @out_of_order_changes INT;

SELECT @out_of_order_changes = COUNT(*)
  FROM #wip_status_changes WSC
 WHERE WSC.status_out_of_order = 1;

WHILE @out_of_order_changes > 0
BEGIN
    PRINT 'remove out of order status changes';

    UPDATE WSC
       SET WSC.from_status = A.from_status,
           WSC.from_status_order = A.from_status_order
      FROM #wip_status_changes WSC
           INNER JOIN
           (
               SELECT WSC.enc_id,
                      WSC.change_nbr,
    --WSC.create_timestamp,
                      WSC.from_status,
                      WSC.from_status_order
                 FROM #wip_status_changes WSC
                WHERE WSC.status_out_of_order = 1
                      AND WSC.to_status_order = 1
           )                   A
               ON A.enc_id = WSC.enc_id
                  AND A.change_nbr + 1 = WSC.change_nbr;

    UPDATE WSC
       SET WSC.to_status = A.to_status,
           WSC.to_status_order = A.to_status_order
      FROM #wip_status_changes WSC
           INNER JOIN
           (
               SELECT WSC.enc_id,
                      WSC.change_nbr,
                      WSC.to_status,
                      WSC.to_status_order
                 FROM #wip_status_changes WSC
                WHERE WSC.status_out_of_order = 1
                      AND WSC.to_status_order <> 1
           )                   A
               ON A.enc_id = WSC.enc_id
                  AND A.change_nbr - 1 = WSC.change_nbr;

    DELETE FROM #wip_status_changes
     WHERE status_out_of_order = 1;

    UPDATE WSC
       SET WSC.change_nbr = A.new_change_nbr
      FROM #wip_status_changes WSC
           INNER JOIN
           (
               SELECT WSC2.enc_id,
                      WSC2.change_nbr,
                      ROW_NUMBER() OVER (PARTITION BY WSC2.enc_id ORDER BY WSC2.change_nbr) new_change_nbr
                 FROM #wip_status_changes WSC2
           )                   A
               ON A.enc_id = WSC.enc_id
                  AND A.change_nbr = WSC.change_nbr;

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

    SELECT @out_of_order_changes = COUNT(*)
      FROM #wip_status_changes WSC
     WHERE WSC.status_out_of_order = 1;
END;

Answer

DECLARE @out_of_order_changes INT;

SELECT @out_of_order_changes = COUNT(*)
  FROM #wip_status_changes WSC
 WHERE WSC.status_out_of_order = 1;

WHILE @out_of_order_changes > 0
BEGIN
    PRINT 'remove out of order status changes';

    /* Update the 'from_status' and 'from_status_order' */
    UPDATE WSC
       SET WSC.from_status = A.from_status,
           WSC.from_status_order = A.from_status_order
      FROM #wip_status_changes WSC
           INNER JOIN
           (
               SELECT WSC.enc_id,
                      WSC.change_nbr,
                      WSC.from_status,
                      WSC.from_status_order
                 FROM #wip_status_changes WSC
                WHERE WSC.status_out_of_order = 1
                      AND WSC.to_status_order = 1
           ) A
               ON A.enc_id = WSC.enc_id
                  AND A.change_nbr + 1 = WSC.change_nbr;

    /* Update the 'to_status' and 'to_status_order' */
    UPDATE WSC
       SET WSC.to_status = A.to_status,
           WSC.to_status_order = A.to_status_order
      FROM #wip_status_changes WSC
           INNER JOIN
           (
               SELECT WSC.enc_id,
                      WSC.change_nbr,
                      WSC.to_status,
                      WSC.to_status_order
                 FROM #wip_status_changes WSC
                WHERE WSC.status_out_of_order = 1
                      AND WSC.to_status_order <> 1
           ) A
               ON A.enc_id = WSC.enc_id
                  AND A.change_nbr - 1 = WSC.change_nbr;

    /* Delete the out-of-order records */
    DELETE FROM #wip_status_changes
     WHERE status_out_of_order = 1;

    /* Refresh 'change_nbr' */
    UPDATE WSC
       SET WSC.change_nbr = A.new_change_nbr
      FROM #wip_status_changes WSC
           INNER JOIN
           (
               SELECT WSC2.enc_id,
                      WSC2.change_nbr,
                      ROW_NUMBER() OVER (PARTITION BY WSC2.enc_id ORDER BY WSC2.change_nbr) new_change_nbr
                 FROM #wip_status_changes WSC2
           ) A
               ON A.enc_id = WSC.enc_id
                  AND A.change_nbr = WSC.change_nbr;

    /* Recalculate 'status_out_of_order' */
    UPDATE WSC
       SET WSC.status_out_of_order = 1
      FROM #wip_status_changes WSC
     WHERE from_status_order > to_status_order;

    /* Recalculate the number of out of order changes */
    SELECT @out_of_order_changes = COUNT(*)
      FROM #wip_status_changes WSC
     WHERE WSC.status_out_of_order = 1;
END;

Explanation:

Overview

This SQL script addresses out-of-order status changes in a temporary table #wip_status_changes and attempts to reorganize them. The primary steps are to update the from_status and to_status, delete out-of-order records, refresh the change numbers, and recalculate the status order.

Breakdown of the code:

Variable Declaration
```
DECLARE @out_of_order_changes INT;
```
- Declares an integer variable to count the out-of-order status changes.
Initial Count of Out-of-Order Changes
```
SELECT @out_of_order_changes = COUNT(*)
  FROM #wip_status_changes WSC
 WHERE WSC.status_out_of_order = 1;
```
- Counts and assigns the number of out-of-order status changes to the variable.

While Loop

WHILE @out_of_order_changes > 0
BEGIN
    PRINT 'remove out of order status changes';

Continues to loop as long as there are out-of-order changes.

Update from_status and from_status_order

UPDATE WSC
   SET WSC.from_status = A.from_status,
       WSC.from_status_order = A.from_status_order
  FROM #wip_status_changes WSC
       INNER JOIN
       (
           SELECT WSC.enc_id,
                  WSC.change_nbr,
                  WSC.from_status,
                  WSC.from_status_order
           FROM #wip_status_changes WSC
          WHERE WSC.status_out_of_order = 1
                AND WSC.to_status_order = 1
       ) A
           ON A.enc_id = WSC.enc_id
              AND A.change_nbr + 1 = WSC.change_nbr;

Updates from_status and from_status_order for out-of-order changes where to_status_order is 1.

Update to_status and to_status_order

UPDATE WSC
   SET WSC.to_status = A.to_status,
       WSC.to_status_order = A.to_status_order
  FROM #wip_status_changes WSC
       INNER JOIN
       (
           SELECT WSC.enc_id,
                  WSC.change_nbr,
                  WSC.to_status,
                  WSC.to_status_order
           FROM #wip_status_changes WSC
          WHERE WSC.status_out_of_order = 1
                AND WSC.to_status_order <> 1
       ) A
           ON A.enc_id = WSC.enc_id
              AND A.change_nbr - 1 = WSC.change_nbr;

Updates to_status and to_status_order for out-of-order changes where to_status_order is not 1.

Delete Out-of-Order Records
```
DELETE FROM #wip_status_changes
 WHERE status_out_of_order = 1;
```
- Deletes records marked as out-of-order.

Refresh Change Numbers

UPDATE WSC
   SET WSC.change_nbr = A.new_change_nbr
  FROM #wip_status_changes WSC
       INNER JOIN
       (
           SELECT WSC2.enc_id,
                  WSC2.change_nbr,
                  ROW_NUMBER() OVER (PARTITION BY WSC2.enc_id ORDER BY WSC2.change_nbr) new_change_nbr
           FROM #wip_status_changes WSC2
       ) A
           ON A.enc_id = WSC.enc_id
              AND A.change_nbr = WSC.change_nbr;

Reassigns change_nbr based on a new sequential ordering using ROW_NUMBER().

Recalculate status_out_of_order

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

Marks records as out-of-order if from_status_order is greater than to_status_order.

Recalculate the Number of Out-of-Order Changes
```
SELECT @out_of_order_changes = COUNT(*)
  FROM #wip_status_changes WSC
 WHERE WSC.status_out_of_order = 1;
```
- Updates the count of out-of-order changes to determine if the loop should continue.
End of Loop
```
END;
```
- Ends the WHILE loop if there are no more out-of-order changes.