Set action flag based on different scenarios in SQL Server [closed]

mbzjlibv 于 2023-03-07 发布在 iOS

关注(0)|答案(2)|浏览(138)

Closed. This question needs to be more focused . It is not currently accepting answers.

Want to improve this question? Update the question so it focuses on one problem only by editing this post .

I tried different solutions from Google but couldn't find any. Can anyone help me with the SQL select query for the below scenarios.

Scenario 1
| MasterData | Usage | CreatedDate | Action Flag |
| ------------ | ------------ | ------------ | ------------ |
| Peter | Not Used | 2020-03-04 | Delete |
| Peter | Not Used | 2023-01-05 | Keep |
| Peter | Not Used | 2019-04-12 | Delete |

If the master data columns have the same name, and all of them have Not Used flag, then we will set the "Keep" in the Action flag for the masterdata which is created on the latest date and Delete flag for the one which was created previously.

Scenario 2
| MasterData | Usage | CreatedDate | Action Flag |
| ------------ | ------------ | ------------ | ------------ |
| John | Not Used | 2015-05-04 | Delete |
| John | Used | 2016-06-05 | Keep |

If the master data have same names, 1 has Not used flag and the other one has "Used" flag then we will have the "Keep" flag for the one which is "Used" and the delete flag for the one which is not used, irrespective of date.

Scenario 3
| MasterData | Usage | CreatedDate | Action Flag |
| ------------ | ------------ | ------------ | ------------ |
| Tony | Used | 2020-05-04 | Delete and Replace |
| Tony | Used | 2021-03-01 | Keep |

If master data have same names, both have Used flag, then we will have the "Keep" flag for the one which is created on the latest date and "Delete and Replace" action for the one which was created previously.

Scenario 4
| MasterData | Usage | CreatedDate | Action Flag |
| ------------ | ------------ | ------------ | ------------ |
| Rachel | Not Used | 2020-12-10 | Delete |
| Rachel | Used | 2022-09-25 | Keep |
| Rachel | Used | 2019-09-05 | Delete and Replace |

If the masterdata column has the same name, there are 2 used flag and 1 not used, then.

“Not used” will have the Delete action.
“Used” with the latest date will have the Keep action.
“Used” with the previous date will have the Delete and Replace Action Flag

Note:

Keep Flag will always be assiged to the Latest date masterdata
The number of masterdata may differ but I have used 2 or 3 for a reference

Example data
| MasterData | Usage | CreatedDate | Action Flag |
| ------------ | ------------ | ------------ | ------------ |
| Rachel | Not Used | 2020-12-10 | Delete |
| Rachel | Used | 2022-09-25 | Keep |
| Rachel | Used | 2019-09-05 | Delete and Replace |
| Tony | Used | 2020-05-04 | Delete and Replace |
| Tony | Used | 2021-03-01 | Keep |
| John | Not Used | 2015-05-04 | Delete |
| John | Used | 2016-06-05 | Keep |
| Peter | Not Used | 2020-03-04 | Delete |
| Peter | Not Used | 2023-01-05 | Keep |
| Peter | Not Used | 2019-04-12 | Delete |

I want this to be achieved in SQL Server.

sql-server

来源：https://stackoverflow.com/questions/75638184/set-action-flag-based-on-different-scenarios-in-sql-server

2条答案

按热度按时间

ax6ht2ek1#

The 4 scenarios can be handled by SQL window function with the following sorting method and case statement .

Sorting method (per user):

'Used' row has high order compared to 'Not Used' row
Within the same usage type, row with newer createddate has higher order.

Following case statement:

Always set row with highest order per user as 'Keep'
For other rows, if it is 'Used', set the row as 'Delete and Replace'; if it is 'Not Used', set the row as 'Delete'

Here is the query:

with sorted_data AS (
select
    masterdata,
    usage,
    createddate,
    row_number() over (partition by masterdata order by (case when usage = 'Used' then 1 else 0 end) desc, createddate desc) as row_num
from
    table1
)
select
    masterdata,
    usage,
    createddate,
    case when row_num = 1 then 'Keep'
         when row_num > 1 and usage = 'Used' then 'Delete and Replace'
         else 'Delete'
    end as actionflag
from
    sorted_data

masterdata	usage	createddate	actionflag
John	Used	2016-06-05	Keep
John	Not Used	2015-05-04	Delete
Peter	Not Used	2023-01-05	Keep
Peter	Not Used	2020-03-04	Delete
Peter	Not Used	2019-04-12	Delete
Rachel	Used	2022-09-25	Keep
Rachel	Used	2019-09-05	Delete and Replace
Rachel	Not Used	2020-12-10	Delete
Tony	Used	2021-03-01	Keep
Tony	Used	2020-05-04	Delete and Replace

赞(0）回复(0）举报 2023-03-07

5cnsuln72#

You need to find the row that you want to keep. One approach is to sort by priority and date:

with data as (
    select *,
        row_number() over (partition by masterdata
                           order by case usage when 'Used' then 1 else 2 end,
                                    createddate desc) as rn
    from T
)
select masterdata, usage, createddate,
    case when rn = 1 then 'Keep'
         when usage = 'Used' then 'Delete and Replace'
         when usage = 'Not Used' then 'Delete'
    end as actionflag
from data;

Similar logic that just looks for the most recent date and accomplishes the same goal. It might possibly be faster:

with data as (
    select *,
        case when createddate = max(createddate) over (partition by masterdata)
             then 1 end as keep
    from T
)
select masterdata, usage, createddate,
    case when keep = 1 then 'Keep'
         when usage = 'Used' then 'Delete and Replace'
         when usage = 'Not Used' then 'Delete'
    end as actionflag;

There's some potential for a tie in the second query is dates are not unique. Likewise the first might not be deterministic for the same reason.

赞(0）回复(0）举报 2023-03-07

我来回答

Set action flag based on different scenarios in SQL Server [closed]

2条答案

相关问题

热门标签

最新问答