sql-serverlead

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

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.

Solution

  • 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