2

I have a 2-column table named Assignment. The table contains assignment of Person (nvarchar(20)) and Day (date), like here:

Person      Day
------------------
John    2011-05-23
John    2011-05-24
John    2011-05-25
John    2011-05-27
John    2011-05-28
John    2011-05-29
Anna    2011-05-02
Anna    2011-05-03
Anna    2011-05-06

I need to extract the date ranges when there is a sequence of adjacent days. The output should be a single string value for every row:

------------------
John    2011-05-23 - 2011-05-25
John    2011-05-27 - 2011-05-29
Anna    2011-05-02 - 2011-05-03
Anna    2011-05-06 - 2011-05-06

I tried grouping and then

   CAST(min(Day) AS VARCHAR) + ' - ' + CAST(max(Day) AS VARCHAR)

but it does not work correctly.

Any advice is welcome. Thanks.

Func
  • 443
  • 3
  • 5
  • 10
  • This answer can be adapted to fit your table. http://stackoverflow.com/questions/6099279/need-help-finding-the-correct-t-sql-query/6099433#6099433 – Mikael Eriksson Jun 02 '11 at 21:12
  • @Mikael Eriksson Thank you that worked, please write your comment as an answer and i will accept it. – Func Jun 02 '11 at 21:28
  • 1
    From the above mentioned answer... ;With Cte as( select *, DATEDIFF(D, 0, A.Day) - ROW_NUMBER() OVER (PARTITION BY A.Person ORDER BY A.Day) Grp from Assignment A ) Select Person, MIN(Day), MAX(Day) from Cte group by Person, Grp – amit_g Jun 02 '11 at 21:30

1 Answers1

3
declare @T table(Person nvarchar(20), [Day] date)

insert into @T values
('John',    '2011-05-23'),
('John',    '2011-05-24'),
('John',    '2011-05-25'),
('John',    '2011-05-27'),
('John',    '2011-05-28'),
('John',    '2011-05-29'),
('Anna',    '2011-05-02'),
('Anna',    '2011-05-03'),
('Anna',    '2011-05-06')


;WITH cte AS
(
SELECT *,
        DATEDIFF(DAY,0,[Day])-
        ROW_NUMBER() OVER (PARTITION BY Person ORDER BY [Day]) AS Grp
FROM @T  
)
SELECT Person, 
       MIN([Day]) AS DateFrom, 
       MAX([Day]) AS DateTo  
FROM cte 
GROUP BY Person, Grp
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • +1; nice answer, anyway why I can see at this post above your name `community wiki` ? –  Jun 02 '11 at 21:36
  • @daemon_x – Community wiki post does not give reputation to the poster and since this answer is a direct copy of Martin's answer http://stackoverflow.com/questions/6099279/need-help-finding-the-correct-t-sql-query/6099433#6099433 I thought is was the right thing to do. – Mikael Eriksson Jun 02 '11 at 21:40
  • @Mikael - so you mark this anwser as a community wiki somehow ? I don't know about this magic. You flagged yourself ? –  Jun 02 '11 at 21:46
  • @daemon_x – Yes I did. It is a checkbox down to the right of the screen when you answer questions. http://stackoverflow.com/privileges/community-wiki – Mikael Eriksson Jun 02 '11 at 21:51
  • @Mikael - I see; I overlooked the check box even if it's directly in front of me :) Thanks for the info. –  Jun 02 '11 at 22:00
  • 1
    @daemon_x: From the page linked by Mikael: `Once a post is made community wiki […], that mode cannot be reversed.` Just wanted to highlight it for you to make sure you wouldn't overlook it. :) – Andriy M Jun 03 '11 at 07:37