34,838
社区成员




WITH table1(id,规定完成日期,实际完成日期) AS (
SELECT 1, '2015-01-01 10:00:00', '2015-01-01 10:00:00' UNION ALL
SELECT 2, '2015-01-01 10:00:00', '2015-01-01 10:59:00' UNION ALL
SELECT 3, '2015-01-01 10:00:00', '2015-01-01 11:00:00' UNION ALL
SELECT 4, '2015-01-01 10:00:00', '2015-01-01 23:00:00' UNION ALL
SELECT 5, '2015-01-01 10:00:00', '2015-01-02 00:00:00' UNION ALL
SELECT 6, '2015-01-01 10:00:00', '2015-01-02 10:00:00' UNION ALL
SELECT 7, '2015-01-01 10:00:00', '2015-01-02 10:59:00' UNION ALL
SELECT 8, '2015-01-01 10:00:00', '2015-01-02 11:00:00'
)
SELECT *,
CASE WHEN 实际完成日期<=规定完成日期 THEN
0
ELSE
(DATEDIFF(hour,规定完成日期,实际完成日期)+23)/24
END 超期天数
FROM table1
id 规定完成日期 实际完成日期 超期天数
----------- ------------------- ------------------- -----------
1 2015-01-01 10:00:00 2015-01-01 10:00:00 0
2 2015-01-01 10:00:00 2015-01-01 10:59:00 0
3 2015-01-01 10:00:00 2015-01-01 11:00:00 1
4 2015-01-01 10:00:00 2015-01-01 23:00:00 1
5 2015-01-01 10:00:00 2015-01-02 00:00:00 1
6 2015-01-01 10:00:00 2015-01-02 10:00:00 1
7 2015-01-01 10:00:00 2015-01-02 10:59:00 1
8 2015-01-01 10:00:00 2015-01-02 11:00:00 2
/* 测试数据
WITH table1(id,规定完成日期,实际完成日期) AS (
SELECT 1, '2015-01-01 10:00:00', '2015-01-01 09:00:00' UNION ALL
SELECT 2, '2015-01-01 10:00:00', '2015-01-01 10:00:00' UNION ALL
SELECT 3, '2015-01-01 10:00:00', '2015-01-01 11:00:00' UNION ALL
SELECT 4, '2015-01-01 10:00:00', '2015-01-01 23:00:00' UNION ALL
SELECT 5, '2015-01-01 10:00:00', '2015-01-02 00:00:00' UNION ALL
SELECT 6, '2015-01-01 10:00:00', '2015-01-02 09:00:00' UNION ALL
SELECT 7, '2015-01-01 10:00:00', '2015-01-02 10:00:00' UNION ALL
SELECT 8, '2015-01-01 10:00:00', '2015-01-02 11:00:00'
) */
SELECT *,
CASE WHEN 实际完成日期<=规定完成日期 THEN
0
ELSE
(DATEDIFF(hour,规定完成日期,实际完成日期)/24)+1
END 超期天数
FROM table1
id 规定完成日期 实际完成日期 超期天数
----------- ------------------- ------------------- -----------
1 2015-01-01 10:00:00 2015-01-01 09:00:00 0
2 2015-01-01 10:00:00 2015-01-01 10:00:00 0
3 2015-01-01 10:00:00 2015-01-01 11:00:00 1
4 2015-01-01 10:00:00 2015-01-01 23:00:00 1
5 2015-01-01 10:00:00 2015-01-02 00:00:00 1
6 2015-01-01 10:00:00 2015-01-02 09:00:00 1
7 2015-01-01 10:00:00 2015-01-02 10:00:00 2
8 2015-01-01 10:00:00 2015-01-02 11:00:00 2
select *,DATEDIFF(HOUR,[规定完成日期],[实际完成日期])/24+1
from table1
select DATEDIFF(dd,DATEADD(hh,1,规定完成日期),实际完成日期)+1
from table
where 实际完成日期>=DATEADD(hh,1,规定完成日期)
[/quote]
不超期的也要统计,显示0
select DATEDIFF(dd,DATEADD(hh,1,规定完成日期),实际完成日期)+1
from table
where 实际完成日期>=DATEADD(hh,1,规定完成日期)
select *,DATEDIFF(HOUR,[规定完成日期],[实际完成日期])
from table1
where DATEDIFF(HOUR,[规定完成日期],[实际完成日期])>1
select DATEDIFF(dd,DATEADD(hh,1,规定完成日期),实际完成日期)+1
from table