I have a table below with a list of transactions for each policy. One of the rows for each policy has a 'Y' as being a policy that had a certain 'issue'. I would like to create a new column to mark the row for 'Y' and the rows following for the same policy with a 1.
Basically for each policy, an issue occurred at some stage within the life cycle of the policy and I want to identify the PolicyVersion which is marked as a 'Y' and any subsequent rows for the same policy after the row marked 'Y'.
I can't quite figure out the correct partition
| PolicyNum | TransactionDate | PolicyVersion | IssueFoundOnThisVersion | DesiredResult |
|---|---|---|---|---|
| A12345 | 2023-01-01 | 1 | N | 0 |
| A12345 | 2023-06-01 | 2 | Y | 1 |
| A12345 | 2023-07-01 | 3 | N | 1 |
| A12345 | 2024-08-01 | 4 | N | 1 |
| A12346 | 2023-02-01 | 1 | N | 0 |
| A12346 | 2023-03-01 | 2 | N | 0 |
| A12346 | 2023-07-01 | 3 | N | 0 |
| A12346 | 2023-08-01 | 4 | Y | 1 |
| A12346 | 2024-01-01 | 5 | N | 1 |
We can use SUM() as a window function here:
SELECT
PolicyNum,
TransactionDate,
PolicyVersion,
IssueFoundOnThisVersion,
CASE WHEN SUM(CASE WHEN IssueFoundOnThisVersion = 'Y' THEN 1 ELSE 0 END)
OVER (PARTITION BY PolicyNum ORDER BY PolicyVersion) > 0
THEN 1 ELSE 0 END AS DesiredResult
FROM yourTable
ORDER BY
PolicyNum,
TransactionDate;
The SUM() logic above does a rolling sum, per policy, incrementing by one each time there is an issue with a certain policy.