6

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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
pdog
  • 61
  • 2
  • 1
    What happens if there is a row `11/4/2011 7:00AM - 6:00PM`? – Aaron Bertrand Aug 17 '11 at 21:28
  • it would be another group the min and max date would be 11/04/2011 – pdog Aug 17 '11 at 21:32
  • 1
    So the grouping is by the value of ops_hrs AND whether the previous day is the day before or not. – Aaron Bertrand Aug 17 '11 at 21:33
  • yes -- consecutive days are grouped together – pdog Aug 17 '11 at 21:34
  • 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](http://www.infoa2z.com/sqlserver/how-to-make-and-use-temporary-tables-in-sql-server) [Click here to read Cursor in SQL server on infoA2z.com](http://www.infoa2z.com/sqlserver/how-to-make-or-use-cursor-in-sql-server) – Rakesh Dhiman Dec 16 '12 at 19:46

2 Answers2

5
;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
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
  • 1
    +1 Very concise and tidy, but I had a real hard time wrapping my head around the DATEDIFF - ROW_NUMBER() logic on first glance. – Aaron Bertrand Aug 18 '11 at 01:47
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;
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490