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.

What is your question here exactly? ["Can someone help me?" isn't an actual question](https://meta.stackoverflow.com/q/284236/2029983). What is the difficult you are having achieving your goal? — Thom A, Apr 06 '23 at 10:00

Stu · Accepted Answer · 2023-04-06T10:19:21.467

0

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

edited Apr 06 '23 at 10:19

answered Apr 06 '23 at 10:04

Stu

30,392
6
14
33

score 0 · Answer 2 · answered Apr 06 '23 at 10:38

This is gaps and islands problem, one option is to use a difference between two row_numbers to define Brand groups :

MIN() used here to return the next Brand='ALL'

WITH CTE AS (
  SELECT RetailDate, Brand,
       MIN(case when Brand = 'ALL' then RetailDate end) over (order by RetailDate rows between 1 following and unbounded following) as next_success_time,
       LEAD(RetailDate) OVER(PARTITION BY IdBusinessUnit, Brand ORDER BY RetailDate) AS NextChange,
       ROW_NUMBER() OVER(PARTITION BY IdBusinessUnit ORDER BY RetailDate) - ROW_NUMBER() OVER(PARTITION BY IdBusinessUnit, Brand ORDER BY RetailDate) as grp
  FROM  tbl_Params P
),
CTE2 as (
  select *, row_number() over (partition by grp order by RetailDate) as rn
  from CTE
)
SELECT RetailDate, Brand, CASE WHEN next_success_time is null and NextChange is null then GetDate()
               WHEN rn = 2 THEN next_success_time
               ELSE NextChange END AS NextChange
from CTE2
ORDER BY RetailDate

Result :

RetailDate  Brand       NextChange
2023-02-05  B Series    2023-02-07 00:00:00.000
2023-02-07  B Series    2023-02-21 00:00:00.000
2023-02-07  Equity      2023-02-15 00:00:00.000
2023-02-15  Equity      2023-02-21 00:00:00.000
2023-02-21  ALL         2023-04-06 10:34:31.673
2023-03-10  B Series    2023-03-11 00:00:00.000
2023-03-11  B Series    2023-04-06 10:34:31.673

Demo here

score 0 · Answer 3 · answered Apr 07 '23 at 16:57

Try this one, with interleaves of brands: https://dbfiddle.uk/Sx61yBnt

with data(RetailDate, Brand, IdBusinessUnit) as (
    select convert(DATETIME, '2023-02-05',102), 'B Series', 2 union all
    select convert(DATETIME,'2023-02-07',102), 'B Series', 2 union all
    select convert(DATETIME,'2023-02-07',102), 'Equity', 2 union all
    select convert(DATETIME,'2023-02-08',102), 'B Series', 2 union all
    select convert(DATETIME,'2023-02-15',102), 'Equity', 2 union all
    select convert(DATETIME,'2023-02-21',102), 'ALL', 2 union all
    select convert(DATETIME,'2023-03-10',102), 'B Series', 2 
)
select d.*,
    isnull(least(
        min (case when Brand = 'ALL' then RetailDate end) 
            over(partition by IdBusinessUnit order by RetailDate rows between 1 following and unbounded following),
        isnull(
            lead(RetailDate)  over(partition by IdBusinessUnit, brand order by retaildate),
            getdate()
        )
    ),getdate()) as nextchange
from data d
  order by RetailDate, brand
;

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

Need to pick the Next Change Date as new column as follows: Desired Output

3 Answers3