我已经在这方面工作了一段时间。我想知道如何获取这个表格:
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
要看起来像这个表格:
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
我尝试使用游标,但它并不是必需的。此外,它必须被分组。只要连续的日子中断,就会出现一个新的分组。任何帮助都将不胜感激。
此查询将生成上述示例数据。
;
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
11/4/2011 7:00AM - 6:00PM
会发生什么? - Aaron Bertrand