SQL query to pick the next change Date from the next row based on same Brand

Question

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.

Answer 1

You could use a correlated subquery, does the following help?

select *, IsNull((
    select top(1) RetailDate
    from t t2
    where t2.RetailDate > t.RetailDate
    and (t2.brand = t.brand or t2.brand = 'ALL')
    order by t2.RetailDate
), GetDate()) NextChange
from t;

See this fiddle demo