How to aggregate single date column per userid

krischtn 21 Reputation points
2021-04-08T13:48:15.243+00:00

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  

  
  
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
0 comments No comments
{count} votes

Accepted answer
  1. Tom Cooper 8,481 Reputation points
    2021-04-08T14:03:48.233+00:00

    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


0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.