SQL Server 2008, temp tables, cursor

Question

I've been working at this for a while. I was wondering how I could 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 a cursor but it is not necessary. Also, it has to be grouped. As soon as consecutive days break a new grouping occurs. 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 

Kegler

What happens if there is a row 11/4/2011 7:00AM - 6:00PM? – Hooke

it would be another group the min and max date would be 11/04/2011 – Kegler

So the grouping is by the value of ops_hrs AND whether the previous day is the day before or not. – Hooke

yes -- consecutive days are grouped together – Kegler

I found two articles that was very helpful to me to make the cursor and temporary tables in SQL Server Click here to read Temporary in SQL server Table on infoA2z.com Click here to read Cursor in SQL server on infoA2z.com – Foyer

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

Speleology

+1 Very concise and tidy, but I had a real hard time wrapping my head around the DATEDIFF - ROW_NUMBER() logic on first glance. – Hooke

Answer 2

Definitely slightly more convoluted logic than @Martin's solution, but I should at least get a point because he used my @x table - so his solution looks that much tidier. :-)

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;

Hooke

Except for variable names, that script looks suspiciously like what's on my monitor – Ofay