A SQL Server database table describes the execution progress of multiple operations in a production process. When the Outcome of an OP is 0, it indicates execution failure and should continue until the Outcome is 1. This indicates successful execution and the next OP should be executed in sequence.

Now we need to track the execution progress of operations: each operation occupies a field, and the field order represents the execution progress of the operations; When an operation fails to execute, it should enter the next tracking stage, which is to generate a new record; Copy the progress 1 (if any) of the previous successful operations before continuing with this operation; If this operation is successful this time, the next operation will continue in the current tracking phase.

SQL：

  SELECT  CASE WHEN max(OP) > 1 THEN 1 ELSE MAX(CASE WHEN OP = 1 THEN Outcome END) END AS OP1
,   CASE WHEN max(OP) > 2 THEN 1 ELSE MAX(CASE WHEN OP = 2 THEN Outcome END) END AS OP2
,   CASE WHEN max(OP) > 3 THEN 1 ELSE MAX(CASE WHEN OP = 3 THEN Outcome END) END AS OP3
,   CASE WHEN max(OP) > 4 THEN 1 ELSE MAX(CASE WHEN OP = 4 THEN Outcome END) END AS OP4
,   CASE WHEN max(OP) > 5 THEN 1 ELSE MAX(CASE WHEN OP = 5 THEN Outcome END) END AS OP5
FROM    (
    SELECT  *
    ,   sum(flag) OVER(ORDER BY OP, Outcome rows BETWEEN unbounded preceding AND CURRENT row) AS counter
    
    FROM    (
        SELECT  *
        ,   CASE WHEN (lag(outcome) OVER(ORDER BY op,outcome) = outcome AND outcome = 1) OR (lag(outcome) OVER(ORDER BY op,outcome) <> outcome AND outcome = 0)
                THEN 0
                ELSE 1
            END AS flag
        FROM    Operations
        ) x
    ) x
GROUP BY x.counter

SQL does not have a mechanism for orderly grouping based on conditions, and instead divides the original records into multiple tracking stages, requiring the use of nested subqueries and multiple window functions for marking. Ordinary SQL also lacks the syntax for dynamically generating fields, and can only rigidly write a case when for each field; Dynamically generating fields requires the use of dynamic SQL.

SPL code is simple and easy to understand:

A1： Load data.

A2： Dynamically generate a two-dimensional table.

A3： Grouping by conditions, when the last execution result failed, a new group was formed, where [-1] represents the previous one. Note that there is no immediate aggregation here.

A4： Loop the data of each group and add one record to the two-dimensional table at a time according to the rules. Fill in some 1s at the beginning and the sequence of the Outcome of this group at the end.

Question source：https://stackoverflow.com/questions/78232342/sql-query-to-track-production-operations-outcome-progression-with-conditional-nu