Requirement is to pick the next change date from the following table as
| RetailDate | Brand | IdBusinessUnit |
| ------------ | ------------ | ------------ |
| 2023-02-05 | B Series | 2 |
| 2023-02-07 | B Series | 2 |
| 2023-02-07 | Equity | 2 |
| 2023-02-15 | Equity | 2 |
| 2023-02-21 | ALL | 2 |
| 2023-03-10 | B Series | 2 |
Need to pick the Next Change Date as new column as follows: Desired Output
RetailDate | Brand | NextChange | IdBusinessUnit |
---|---|---|---|
2023-02-05 | B Series | 2023-02-07 | 2 |
2023-02-07 | B Series | 2023-02-21 | 2 |
2023-02-07 | Equity | 2023-02-15 | 2 |
2023-02-15 | Equity | 2023-02-21 | 2 |
2023-02-21 | ALL | GETDATE() | 2 |
2023-03-10 | B Series | GETDATE() | 2 |
Need to traverse Row by Row to get the next Change based on Brand, if ALL is the Brand need to Pick the ALL Retaildate as NextChange as shown for B-Series (2023-02-21).
I have used the following query to get the NextChange
SELECT RetailDate ,Brand ,LEAD(RetailDate) OVER(PARTITION BY IdBusinessUnit,Brand ORDER BY RetailDate) AS NextChange FROM tbl_Params P (NOLOCK) WHERE bStdActive = 1 ORDER BY RetailDate
| RetailDate | Brand | NextChange | IdBusinessUnit |
| ------------ | ------------ | ------------ | ------------ |
| 2023-02-05 | B Series | 2023-02-07 | 2 |
| 2023-02-07 | B Series | 2023-03-10 | 2 |
| 2023-02-07 | Equity | 2023-02-15 | 2 |
| 2023-02-15 | Equity | NULL | 2 |
| 2023-02-21 | ALL | NULL | 2 |
| 2023-03-10 | B Series | NULL | 2 |
Please help me out, I am struggling to get the result since last 3 days. Thanks in advance.
3条答案
按热度按时间olhwl3o21#
You could use a correlated subquery, does the following help?
See this fiddle demo
kjthegm62#
This is
gaps and islands
problem, one option is to use a difference between tworow_numbers
to define Brand groups :MIN()
used here to return the nextBrand='ALL'
Result :
Demo here
k3bvogb13#
Try this one, with interleaves of brands: https://dbfiddle.uk/Sx61yBnt