SQL Server 2008, temporary tables, cursor

Question

I have been working on this for a while. I was wondering how can I get this table:

id open_dt ops_hrs 1 10/31/2011 7:00AM - 5:30PM 2 11/1/2011 7:00AM - 5:00PM 3 11/2/2011 7:00AM - 5:00PM 4 11/3/2011 7:00AM - 5:00PM 5 11/6/2011 7:00AM - 7:00PM 6 11/8/2011 7:00AM - 5:00PM 

to look like this table:

 max_date min_date ops_hrs 10/31/2011 10/31/2011 7:00AM - 5:30PM 11/1/2011 11/3/2011 7:00AM - 5:00PM 11/6/2011 11/6/2011 7:00AM - 7:00PM 11/8/2011 11/8/2011 7:00AM - 5:00PM 

I tried using the cursor, but this is optional. In addition, it must be grouped. As soon as the following days are interrupted, a new grouping arises. Any help would be appreciated.

This query will generate the above sample data.

 ; WITH pdog (id, open_dt,ops_hrs) AS ( SELECT 1, CAST('10/31/2011' AS datetime), '7:00AM - 5:30PM' UNION ALL SELECT 2, CAST('11/1/2011' AS datetime),'7:00AM - 5:00PM' UNION ALL SELECT 3, CAST('11/2/2011' AS datetime),'7:00AM - 5:00PM' UNION ALL SELECT 4, CAST('11/3/2011' AS datetime),'7:00AM - 5:00PM' UNION ALL SELECT 5, CAST('11/6/2011' AS datetime),'7:00AM - 7:00PM' UNION ALL SELECT 6, CAST('11/8/2011' AS datetime),'7:00AM - 5:00PM' ) SELECT * FROM pdog 

Answer 1

 ;WITH CTE AS ( SELECT * , DATEDIFF(DAY, 0, open_dt) - ROW_NUMBER() OVER ( PARTITION BY ops_hrs ORDER BY open_dt ) AS Grp FROM @x ) SELECT MIN(open_dt) AS min_date , MAX(open_dt) AS max_date , ops_hrs FROM CTE GROUP BY ops_hrs , Grp ORDER BY min_date 

Answer 2

Definitely a bit more confusing logic than @Martin's solution, but I should at least get the point because it used my @x table, so its solution looks much more neat. :-)

 DECLARE @x TABLE(id INT IDENTITY(1,1), open_dt DATE, ops_hrs VARCHAR(32)); INSERT @x(open_dt, ops_hrs) VALUES ('2011-10-31', '7:00AM - 5:30PM'), ('2011-11-01', '7:00AM - 5:00PM'), ('2011-11-02', '7:00AM - 5:00PM'), ('2011-11-03', '7:00AM - 5:00PM'), ('2011-11-06', '7:00AM - 7:00PM'), ('2011-11-08', '7:00AM - 5:00PM'); ;WITH d AS ( SELECT open_dt, ops_hrs, max_date = COALESCE((SELECT MAX(open_dt) FROM @x AS b WHERE b.open_dt > a.open_dt AND NOT EXISTS (SELECT 1 FROM @x AS c WHERE c.open_dt >= a.open_dt AND c.open_dt < b.open_dt AND c.ops_hrs <> b.ops_hrs)), open_dt) FROM @x AS a ) SELECT min_date = MIN(open_dt), max_date, ops_hrs FROM d GROUP BY max_date, ops_hrs ORDER BY min_date;