admin管理员组

文章数量:1023744

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

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
  • sql-server
  • t-sql
Share Improve this question edited Nov 19, 2024 at 17:48 Dale K 27.6k15 gold badges58 silver badges83 bronze badges asked Nov 19, 2024 at 14:16 Denzo37Denzo37 413 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 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.

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

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
  • sql-server
  • t-sql
Share Improve this question edited Nov 19, 2024 at 17:48 Dale K 27.6k15 gold badges58 silver badges83 bronze badges asked Nov 19, 2024 at 14:16 Denzo37Denzo37 413 bronze badges
Add a comment  | 

1 Answer 1

Reset to default 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.

本文标签： sql serverFill down from a specific row based on criteriaStack Overflow

版权声明：本文标题：sql server - Fill down from a specific row based on criteria - Stack Overflow 内容由热心网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://it.en369.cn/questions/1745555670a2155861.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。