I'm working on a SQL Server database where I need to track the progression of outcomes for production operations. Each operation (OP) is attempted until it succeeds (outcome=1). If an operation fails (outcome=0), it's retried for a given amount of times, if it passes, after which we proceed to the next operation.
The operations are stored in a table named Operations like the following:
| id | OP | Outcome |
|---|---|---|
| 1 | 1 | 0 |
| 2 | 1 | 0 |
| 3 | 1 | 1 |
| 4 | 2 | 1 |
| 5 | 3 | 1 |
| 6 | 4 | 0 |
| 7 | 4 | 0 |
| 8 | 4 | 1 |
| 9 | 5 | 0 |
| 10 | 5 | 1 |
I want to create a view that displays the outcome of each operation across rows in a specific format. If an operation fails, the subsequent operations in that row should display as NULL until the operation succeeds. Here's an example of the desired output format for 5 operations:
| OP 1 Outcome | OP 2 Outcome | OP 3 Outcome | OP 4 Outcome | OP 5 Outcome |
|---|---|---|---|---|
| 0 | NULL | NULL | NULL | NULL |
| 0 | NULL | NULL | NULL | NULL |
| 1 | 1 | 1 | 0 | NULL |
| 1 | 1 | 1 | 0 | NULL |
| 1 | 1 | 1 | 1 | 0 |
| 1 | 1 | 1 | 1 | 1 |
I've attempted to structure a query using window functions and conditional aggregation to pivot the data, but I'm struggling with correctly propagating the outcomes and handling the NULLs for subsequent operations after a failure.
The best result I get so far is showed in this fiddle, but I can't figure it out ho to coalesce the NULL value for the already succeeded operation.
Every zero is the end of a logical group (with an implied zero at the end.) Count off the zeroes in reverse order. The aggregates are a little more complicated than I originally thought but it works:
with dataX as (
select *,
-count(case when outcome = 0 then 1 end) over (order by id desc) as grp
from ops
)
select
grp,
case when max(op) > 1 then 1 else max(case when op = 1 then outcome end) end as op1outcome,
case when max(op) > 2 then 1 else max(case when op = 2 then outcome end) end as op2outcome,
case when max(op) > 3 then 1 else max(case when op = 3 then outcome end) end as op3outcome,
case when max(op) > 4 then 1 else max(case when op = 4 then outcome end) end as op4outcome,
case when max(op) > 5 then 1 else max(case when op = 5 then outcome end) end as op5outcome
from dataX
group by grp;
OP realized a variation of my first attempt would successfully produce the same result via an analytic max(). I myself had realized that that query wouldn't run without wrapping some of the bare columns with aggregates (or adding to the group by as OP did. The version here avoids that in favor of aggregates and case expressions. This second approach might have an advantage if operations could be skipped over but I think another intention was to avoid the use of select distinct and produce a simpler query plan.