基于相同品牌从下一行选择下一个更改日期的SQL查询

Question

需求是从下面的表中选择下一个更改日期作为

tbody> <<tr>

RetailDate	Brand	IdBusinessUnit
2023-02-05	B系列	2
2023-02-07	B系列	2
2023-02-07	股本	2
2023-02-15	股本	2
2023-02-21	所有	2
2023-03-10	B系列	2

您可以使用相关子查询，下面有帮助吗?

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;

查看这个小提琴演示

这是gaps and islands的问题，一种选择是使用两个row_numbers之间的差异来定义品牌组:

MIN()用于返回下一个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

结果:

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

演示

试试这个，与品牌交错: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
;