Declare @YourTable Table ([TransDate] date,[Amount] int) Insert Into @YourTable Values
('2020-06-01',27000)
,('2020-06-02',27000)
,('2020-06-03',27000)
,('2020-06-04',27000)
,('2020-06-05',32000)
,('2020-06-06',37000)
,('2020-06-07',27000)
,('2020-06-08',47000)
Select [Amount]
,StartDate = min(TransDate)
,EndDate = max(TransDate)
From (
Select *
,Grp = datediff(DAY,'1900-01-01',TransDate) - row_number() over (partition by Amount order by TransDate)
From @YourTable
) A
Group By Amount,Grp
Order By min(TransDate)
1条答案
按热度按时间slwdgvem1#
你好像在找一个标准
Gaps-and-Islands
.然而,我不确定我是否同意你所期望的结果。也许我遗漏了一个要求。
另外,今后请不要将样本数据粘贴为图片。。。大多数人宁愿复制粘贴。
例子
退货