Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
I want to aggregate a single date column to a range (begin,end) per userId, but only for dates that sequenced to each other.
So in the example below, for 7844 the range should be aggregaded to 2021-04-26 - 2021-04-48.
Tried already with over clause, but with no luck.
Any idea on that?
DECLARE @Have TABLE (anf DATE,persnr int)
INSERT INTO @Have
VALUES
('2021-04-26',7844)
,('2021-04-27',7844)
,('2021-04-28',7844)
,('2021-05-15',7844)
,('2021-04-26',6847)
,('2021-05-17',7844)
DECLARE @Want TABLE(anf DATE, ende DATE, persnr int)
INSERT INTO @Want VALUES
('2021-04-26', '2021-04-28', 7844)
, ('2021-04-26', '2021-04-26', 6847)
, ('2021-05-15', '2021-05-15', 7844)
, ('2021-05-17', '2021-05-17', 7844)
SELECT * FROM @Want
This is an "Islands and Gaps" problem. You can learn more about solutions to this kind of problem by googling that term.
You want
;With cte As
(Select anf, persnr,
DateDiff(day, '19000101', anf) - Row_Number() Over(Partition By persnr Order By anf) As Island
From @Have)
Select Min(anf) As anf, Max(anf) As ende, persnr
From cte
Group By persnr, Island;
Tom