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 .
Closed yesterday.
Improve this question
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.
2条答案
按热度按时间ax6ht2ek1#
The 4 scenarios can be handled by SQL window function with the following sorting method and case statement .
Sorting method (per user):
Following case statement:
Here is the query:
5cnsuln72#
You need to find the row that you want to keep. One approach is to sort by priority and date:
Similar logic that just looks for the most recent date and accomplishes the same goal. It might possibly be faster:
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.