时间间隔计算

zhangle861010 2013-10-08 03:42:44
start_time end_time
2013-09-11 17:26:02.382 2013-09-24 10:38:01.41
2013-09-18 17:02:40.444 2013-09-22 15:27:58.984
2013-09-18 08:21:32.036 2013-09-22 15:31:52.499
2013-09-13 16:28:29.832 2013-09-16 09:41:47.988
2013-09-09 10:59:59.835 2013-09-10 14:06:21.223


要求计算这两个列的时间差 但是要去除9月份的正常休假并且只计算正常工作时间（上午8:30--12:00 下午14:00--18:00）

计算结果如下：

start_time end_time diff_time（小时）
2013-09-11 17:26:02.382 2013-09-24 10:38:01.41 55.1
2013-09-18 17:02:40.444 2013-09-22 15:27:58.984 5.9
2013-09-18 08:21:32.036 2013-09-22 15:31:52.499 12.5
2013-09-13 16:28:29.832 2013-09-16 09:41:47.988 2.7
2013-09-09 10:59:59.835 2013-09-10 14:06:21.223 1.1

请各位大大帮忙看看这个时间差应该怎么计算 谢谢了
...全文
326 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
吉普赛的歌 版主 2013-10-11
  • 打赏
  • 举报
 回复
以前做过一个类似的，　你可以参考一下我的想法 http://blog.csdn.net/yenange/article/details/8433293
LongRui888 2013-10-09
  • 打赏
  • 举报
 回复
引用 14 楼 zhangle861010 的回复:
[quote=引用 12 楼 yupeigu 的回复:] 借用上面的建表和插入数据的语句： 




if object_id('tab') is not null 
drop table tab

if object_id('holiday') is not null
drop table holiday

go
create table tab(start_time datetime,end_time datetime)

insert into tab
select '2013-09-11 17:26:02.382','2013-09-24 10:38:01.41'  union
select '2013-09-18 17:02:40.444','2013-09-22 15:27:58.984' union
select '2013-09-18 08:21:32.036','2013-09-22 15:31:52.499' union
select '2013-09-13 16:28:29.832','2013-09-16 09:41:47.988' union
select '2013-09-09 10:59:59.835','2013-09-09 14:06:21.223'
 
create table holiday(h_date datetime)

insert into holiday
select '2013-09-01' union 
select '2013-09-07'  union 
select '2013-09-08' union 
select '2013-09-14'union 
select '2013-09-15'union 
select '2013-09-19'union 
select '2013-09-20'union 
select '2013-09-21'union 
select '2013-09-29'
花了2个小时写的查询代码如下： 


WITH calendar    --产生日历
AS
(
SELECT CAST('2013-09-01' as varchar(10)) AS r  --月份的开始日期

UNION ALL

SELECT convert(VARCHAR(10),dateadd(day,1,r),120)
FROM calendar
WHERE r < '2013-09-30'    --月份的结束日期
),

tt   --计算时间间隔，单位为秒
as
(
SELECT t.start_time,
       t.end_time,
       
       c.r,
       h.h_date,
       
       /*
       通过tab表和calendar表的关联，就能把开始时间到结束时间，多对应的多天，
       都给关联出来，
       比如开始时间 2013-09-18 08:21:32.037	结束时间 2013-09-22 15:31:52.500，
       其实就是，18、19、20、21、22这一共5天，会由原来的1条记录，现在变为5条记录。
       
       如果h_date为null，说明这一天不是假日,
       就需要计算时间间隔，有几种可能性：
       1.开始时间和结束时间，在同一天的
       2.当前日期和开始日期相同
       3.当前日期和结束日期相同
       4.当前日期是在开始日期和结束日期之间的某天
       
       如果h_date是null，那么返回0，说明是节假日，就不用计算时间间隔了 
       */
       case when h_date IS null and
                 convert(varchar(10),t.start_time_temp,120) = c.r and
                 CONVERT(varchar(10),t.end_time_temp,120) = c.r
                 then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                and not (convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
                                then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00')
                           else 0
                      end +
                      case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                and not (convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
                                then DATEDIFF(second,c.r+' 14:00:00',t.end_time_temp)
                           else 0
                      end +
                      case when (convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                 and convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
                                or
                                (convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                 and convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
                                then DATEDIFF(SECOND,t.start_time_temp,t.end_time_temp)
                           else 0
                      end     
            
            /*
            注意下面的计算逻辑是，如果这天不是假日，同时与开始日期相同
            那么就要计算时间间隔，如果时间是在上午的工作时间范围内，
            那么用当前日期的12点，减去开始日期，就是时间间隔，但还必须要加上下午的工作时间，
            也就是4个小时，转化为秒数，就是4*3600
            */                    
            when h_date IS null and
                 convert(varchar(10),t.start_time_temp,120) = c.r 
                 then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00') + 4 * 3600
                           else 0
                      end +
                      case when convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00'
                                then DATEDIFF(second,t.start_time_temp,c.r +' 18:00:00')
                           else 0
                      end                      
                 
            when h_date IS null and
                 CONVERT(varchar(10),t.end_time_temp,120) = c.r
                 then case when convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00'
                                then DATEDIFF(second,c.r +' 08:30:00',t.end_time_temp) 
                           else 0
                      end +
                      case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                then DATEDIFF(second,c.r +' 14:00:00',t.end_time_temp) + 3.5 * 3600
                           else 0
                      end
            
            when h_date is null and
                 convert(varchar(10),t.start_time_temp,120) < c.r and
                 CONVERT(varchar(10),t.end_time_temp,120) > c.r     
                 then  7.5 * 3600
                 
            when h_date IS null
                 then 0
       end   as seconds 
FROM 
(
/*
这里之所以要转换，是由于有些时间比如 start_time为2013-09-18 08:21:32.037,
不在正常工作时间（上午8:30--12:00 下午14:00--18:00）内，
所以要先转化为正常工作时间，否则后面的case when的逻辑判断就太复杂了。
*/
SELECT start_time,
       end_time,
       
       case when CONVERT(varchar(5),start_time,114) < '08:30'
                 then cast(CONVERT(varchar(10),start_time,120) + ' 08:30:00' AS datetime)
                 
            when CONVERT(varchar(5),start_time,114) between '12:00' and '14:00'
                 then cast(CONVERT(varchar(10),start_time,120) + ' 12:00:00' AS datetime)
            
            else start_time
       end as start_time_temp,

       case when CONVERT(varchar(5),end_time,114) between '12:00' and '14:00'
                 then cast(CONVERT(varchar(10),end_time,120) + ' 12:00:00' AS datetime)
            
            when CONVERT(varchar(5),end_time,114) > '18:00'
                 then cast(CONVERT(varchar(10),end_time,120) + ' 18:00:00' AS datetime)
            
            else end_time
       end as end_time_temp  
FROM tab
) t
inner join calendar c
        on convert(varchar(10),t.start_time,120) <= c.r
           and convert(varchar(10),t.end_time,120) >= c.r 
left join holiday h
       on c.r = h.h_date

--OPTION(MAXRECURSION 1000)  --限制最大递归次数
)

--select * from tt

select start_time,
       end_time,
       
       --汇总秒数，同时转化为小时
       cast(round(SUM(seconds) / 3600 ,1,1) as numeric(10,1)) as diff_time
from tt
group by start_time,
         end_time
         
/*
start_time				end_time				diff_time
2013-09-09 10:59:59.837	2013-09-09 14:06:21.223	1.1
2013-09-13 16:28:29.833	2013-09-16 09:41:47.987	2.7
2013-09-18 17:02:40.443	2013-09-22 15:27:58.983	5.9
2013-09-18 08:21:32.037	2013-09-22 15:31:52.500	12.5
2013-09-11 17:26:02.383	2013-09-24 10:38:01.410	55.1
*/
[/quote] 希望对你有用哈，呵呵
zhangle861010 2013-10-09
  • 打赏
  • 举报
 回复
引用 12 楼 yupeigu 的回复:
借用上面的建表和插入数据的语句： 




if object_id('tab') is not null 
drop table tab

if object_id('holiday') is not null
drop table holiday

go
create table tab(start_time datetime,end_time datetime)

insert into tab
select '2013-09-11 17:26:02.382','2013-09-24 10:38:01.41'  union
select '2013-09-18 17:02:40.444','2013-09-22 15:27:58.984' union
select '2013-09-18 08:21:32.036','2013-09-22 15:31:52.499' union
select '2013-09-13 16:28:29.832','2013-09-16 09:41:47.988' union
select '2013-09-09 10:59:59.835','2013-09-09 14:06:21.223'
 
create table holiday(h_date datetime)

insert into holiday
select '2013-09-01' union 
select '2013-09-07'  union 
select '2013-09-08' union 
select '2013-09-14'union 
select '2013-09-15'union 
select '2013-09-19'union 
select '2013-09-20'union 
select '2013-09-21'union 
select '2013-09-29'
花了2个小时写的查询代码如下： 


WITH calendar    --产生日历
AS
(
SELECT CAST('2013-09-01' as varchar(10)) AS r  --月份的开始日期

UNION ALL

SELECT convert(VARCHAR(10),dateadd(day,1,r),120)
FROM calendar
WHERE r < '2013-09-30'    --月份的结束日期
),

tt   --计算时间间隔，单位为秒
as
(
SELECT t.start_time,
       t.end_time,
       
       c.r,
       h.h_date,
       
       /*
       通过tab表和calendar表的关联，就能把开始时间到结束时间，多对应的多天，
       都给关联出来，
       比如开始时间 2013-09-18 08:21:32.037	结束时间 2013-09-22 15:31:52.500，
       其实就是，18、19、20、21、22这一共5天，会由原来的1条记录，现在变为5条记录。
       
       如果h_date为null，说明这一天不是假日,
       就需要计算时间间隔，有几种可能性：
       1.开始时间和结束时间，在同一天的
       2.当前日期和开始日期相同
       3.当前日期和结束日期相同
       4.当前日期是在开始日期和结束日期之间的某天
       
       如果h_date是null，那么返回0，说明是节假日，就不用计算时间间隔了 
       */
       case when h_date IS null and
                 convert(varchar(10),t.start_time_temp,120) = c.r and
                 CONVERT(varchar(10),t.end_time_temp,120) = c.r
                 then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                and not (convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
                                then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00')
                           else 0
                      end +
                      case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                and not (convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
                                then DATEDIFF(second,c.r+' 14:00:00',t.end_time_temp)
                           else 0
                      end +
                      case when (convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                 and convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
                                or
                                (convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                 and convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
                                then DATEDIFF(SECOND,t.start_time_temp,t.end_time_temp)
                           else 0
                      end     
            
            /*
            注意下面的计算逻辑是，如果这天不是假日，同时与开始日期相同
            那么就要计算时间间隔，如果时间是在上午的工作时间范围内，
            那么用当前日期的12点，减去开始日期，就是时间间隔，但还必须要加上下午的工作时间，
            也就是4个小时，转化为秒数，就是4*3600
            */                    
            when h_date IS null and
                 convert(varchar(10),t.start_time_temp,120) = c.r 
                 then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00') + 4 * 3600
                           else 0
                      end +
                      case when convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00'
                                then DATEDIFF(second,t.start_time_temp,c.r +' 18:00:00')
                           else 0
                      end                      
                 
            when h_date IS null and
                 CONVERT(varchar(10),t.end_time_temp,120) = c.r
                 then case when convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00'
                                then DATEDIFF(second,c.r +' 08:30:00',t.end_time_temp) 
                           else 0
                      end +
                      case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                then DATEDIFF(second,c.r +' 14:00:00',t.end_time_temp) + 3.5 * 3600
                           else 0
                      end
            
            when h_date is null and
                 convert(varchar(10),t.start_time_temp,120) < c.r and
                 CONVERT(varchar(10),t.end_time_temp,120) > c.r     
                 then  7.5 * 3600
                 
            when h_date IS null
                 then 0
       end   as seconds 
FROM 
(
/*
这里之所以要转换，是由于有些时间比如 start_time为2013-09-18 08:21:32.037,
不在正常工作时间（上午8:30--12:00 下午14:00--18:00）内，
所以要先转化为正常工作时间，否则后面的case when的逻辑判断就太复杂了。
*/
SELECT start_time,
       end_time,
       
       case when CONVERT(varchar(5),start_time,114) < '08:30'
                 then cast(CONVERT(varchar(10),start_time,120) + ' 08:30:00' AS datetime)
                 
            when CONVERT(varchar(5),start_time,114) between '12:00' and '14:00'
                 then cast(CONVERT(varchar(10),start_time,120) + ' 12:00:00' AS datetime)
            
            else start_time
       end as start_time_temp,

       case when CONVERT(varchar(5),end_time,114) between '12:00' and '14:00'
                 then cast(CONVERT(varchar(10),end_time,120) + ' 12:00:00' AS datetime)
            
            when CONVERT(varchar(5),end_time,114) > '18:00'
                 then cast(CONVERT(varchar(10),end_time,120) + ' 18:00:00' AS datetime)
            
            else end_time
       end as end_time_temp  
FROM tab
) t
inner join calendar c
        on convert(varchar(10),t.start_time,120) <= c.r
           and convert(varchar(10),t.end_time,120) >= c.r 
left join holiday h
       on c.r = h.h_date

--OPTION(MAXRECURSION 1000)  --限制最大递归次数
)

--select * from tt

select start_time,
       end_time,
       
       --汇总秒数，同时转化为小时
       cast(round(SUM(seconds) / 3600 ,1,1) as numeric(10,1)) as diff_time
from tt
group by start_time,
         end_time
         
/*
start_time				end_time				diff_time
2013-09-09 10:59:59.837	2013-09-09 14:06:21.223	1.1
2013-09-13 16:28:29.833	2013-09-16 09:41:47.987	2.7
2013-09-18 17:02:40.443	2013-09-22 15:27:58.983	5.9
2013-09-18 08:21:32.037	2013-09-22 15:31:52.500	12.5
2013-09-11 17:26:02.383	2013-09-24 10:38:01.410	55.1
*/
zhangle861010 2013-10-09
  • 打赏
  • 举报
 回复
这个SQL只要间隔一天计算出来的时间还是不准确呀
LongRui888 2013-10-09
  • 打赏
  • 举报
 回复
借用上面的建表和插入数据的语句： 




if object_id('tab') is not null 
drop table tab

if object_id('holiday') is not null
drop table holiday

go
create table tab(start_time datetime,end_time datetime)

insert into tab
select '2013-09-11 17:26:02.382','2013-09-24 10:38:01.41'  union
select '2013-09-18 17:02:40.444','2013-09-22 15:27:58.984' union
select '2013-09-18 08:21:32.036','2013-09-22 15:31:52.499' union
select '2013-09-13 16:28:29.832','2013-09-16 09:41:47.988' union
select '2013-09-09 10:59:59.835','2013-09-09 14:06:21.223'
 
create table holiday(h_date datetime)

insert into holiday
select '2013-09-01' union 
select '2013-09-07'  union 
select '2013-09-08' union 
select '2013-09-14'union 
select '2013-09-15'union 
select '2013-09-19'union 
select '2013-09-20'union 
select '2013-09-21'union 
select '2013-09-29'
花了2个小时写的查询代码如下： 


WITH calendar    --产生日历
AS
(
SELECT CAST('2013-09-01' as varchar(10)) AS r  --月份的开始日期

UNION ALL

SELECT convert(VARCHAR(10),dateadd(day,1,r),120)
FROM calendar
WHERE r < '2013-09-30'    --月份的结束日期
),

tt   --计算时间间隔，单位为秒
as
(
SELECT t.start_time,
       t.end_time,
       
       c.r,
       h.h_date,
       
       /*
       通过tab表和calendar表的关联，就能把开始时间到结束时间，多对应的多天，
       都给关联出来，
       比如开始时间 2013-09-18 08:21:32.037	结束时间 2013-09-22 15:31:52.500，
       其实就是，18、19、20、21、22这一共5天，会由原来的1条记录，现在变为5条记录。
       
       如果h_date为null，说明这一天不是假日,
       就需要计算时间间隔，有几种可能性：
       1.开始时间和结束时间，在同一天的
       2.当前日期和开始日期相同
       3.当前日期和结束日期相同
       4.当前日期是在开始日期和结束日期之间的某天
       
       如果h_date是null，那么返回0，说明是节假日，就不用计算时间间隔了 
       */
       case when h_date IS null and
                 convert(varchar(10),t.start_time_temp,120) = c.r and
                 CONVERT(varchar(10),t.end_time_temp,120) = c.r
                 then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                and not (convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
                                then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00')
                           else 0
                      end +
                      case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                and not (convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
                                then DATEDIFF(second,c.r+' 14:00:00',t.end_time_temp)
                           else 0
                      end +
                      case when (convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                 and convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00')
                                or
                                (convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                 and convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00')
                                then DATEDIFF(SECOND,t.start_time_temp,t.end_time_temp)
                           else 0
                      end     
            
            /*
            注意下面的计算逻辑是，如果这天不是假日，同时与开始日期相同
            那么就要计算时间间隔，如果时间是在上午的工作时间范围内，
            那么用当前日期的12点，减去开始日期，就是时间间隔，但还必须要加上下午的工作时间，
            也就是4个小时，转化为秒数，就是4*3600
            */                    
            when h_date IS null and
                 convert(varchar(10),t.start_time_temp,120) = c.r 
                 then case when convert(varchar(5),t.start_time_temp,114) between '08:30' and '12:00'
                                then DATEDIFF(second,t.start_time_temp,c.r +' 12:00:00') + 4 * 3600
                           else 0
                      end +
                      case when convert(varchar(5),t.start_time_temp,114) between '14:00' and '18:00'
                                then DATEDIFF(second,t.start_time_temp,c.r +' 18:00:00')
                           else 0
                      end                      
                 
            when h_date IS null and
                 CONVERT(varchar(10),t.end_time_temp,120) = c.r
                 then case when convert(varchar(5),t.end_time_temp,114) between '08:30' and '12:00'
                                then DATEDIFF(second,c.r +' 08:30:00',t.end_time_temp) 
                           else 0
                      end +
                      case when convert(varchar(5),t.end_time_temp,114) between '14:00' and '18:00'
                                then DATEDIFF(second,c.r +' 14:00:00',t.end_time_temp) + 3.5 * 3600
                           else 0
                      end
            
            when h_date is null and
                 convert(varchar(10),t.start_time_temp,120) < c.r and
                 CONVERT(varchar(10),t.end_time_temp,120) > c.r     
                 then  7.5 * 3600
                 
            when h_date IS null
                 then 0
       end   as seconds 
FROM 
(
/*
这里之所以要转换，是由于有些时间比如 start_time为2013-09-18 08:21:32.037,
不在正常工作时间（上午8:30--12:00 下午14:00--18:00）内，
所以要先转化为正常工作时间，否则后面的case when的逻辑判断就太复杂了。
*/
SELECT start_time,
       end_time,
       
       case when CONVERT(varchar(5),start_time,114) < '08:30'
                 then cast(CONVERT(varchar(10),start_time,120) + ' 08:30:00' AS datetime)
                 
            when CONVERT(varchar(5),start_time,114) between '12:00' and '14:00'
                 then cast(CONVERT(varchar(10),start_time,120) + ' 12:00:00' AS datetime)
            
            else start_time
       end as start_time_temp,

       case when CONVERT(varchar(5),end_time,114) between '12:00' and '14:00'
                 then cast(CONVERT(varchar(10),end_time,120) + ' 12:00:00' AS datetime)
            
            when CONVERT(varchar(5),end_time,114) > '18:00'
                 then cast(CONVERT(varchar(10),end_time,120) + ' 18:00:00' AS datetime)
            
            else end_time
       end as end_time_temp  
FROM tab
) t
inner join calendar c
        on convert(varchar(10),t.start_time,120) <= c.r
           and convert(varchar(10),t.end_time,120) >= c.r 
left join holiday h
       on c.r = h.h_date

--OPTION(MAXRECURSION 1000)  --限制最大递归次数
)

--select * from tt

select start_time,
       end_time,
       
       --汇总秒数，同时转化为小时
       cast(round(SUM(seconds) / 3600 ,1,1) as numeric(10,1)) as diff_time
from tt
group by start_time,
         end_time
         
/*
start_time				end_time				diff_time
2013-09-09 10:59:59.837	2013-09-09 14:06:21.223	1.1
2013-09-13 16:28:29.833	2013-09-16 09:41:47.987	2.7
2013-09-18 17:02:40.443	2013-09-22 15:27:58.983	5.9
2013-09-18 08:21:32.037	2013-09-22 15:31:52.500	12.5
2013-09-11 17:26:02.383	2013-09-24 10:38:01.410	55.1
*/
习惯性蹭分 2013-10-08
  • 打赏
  • 举报
 回复 

use test
if object_id('tab') is not null 
drop table tab
if object_id('holiday') is not null
drop table holiday
go
create table tab(start_time datetime,end_time datetime)
insert into tab
select '2013-09-11 17:26:02.382','2013-09-24 10:38:01.41'  union
select '2013-09-18 17:02:40.444','2013-09-22 15:27:58.984' union
select '2013-09-18 08:21:32.036','2013-09-22 15:31:52.499' union
select '2013-09-13 16:28:29.832','2013-09-16 09:41:47.988' union
select '2013-09-09 10:59:59.835','2013-09-09 14:06:21.223'

create table holiday(h_date datetime)
insert into holiday
select '2013-09-01' union 
select '2013-09-07'  union 
select '2013-09-08' union 
select '2013-09-14'union 
select '2013-09-15'union 
select '2013-09-19'union 
select '2013-09-20'union 
select '2013-09-21'union 
select '2013-09-29'

--;with sel as(select start_time oldstart,end_time oldend,case when  exists(select 1 from holiday where
--h_date=convert(varchar(10),t.start_time,114)) then convert(varchar(10),t.start_time,121)+' 19:00' end as start_time,
--case when   exists(select 1 from holiday where h_date=convert(varchar(10),t.end_time,114)) then
-- convert(varchar(10),t.end_time,121)+' 19:00' end as end_time
--from tab t)
select start_time,end_time ,diff_time=
case when datediff(d,start_time,end_time)=0
then (
case when convert(varchar(10),start_time,114)<='08:30' then 7.5
when convert(varchar(10),start_time,114) between '08:31' and '12:00'  then 4+datediff(n,start_time,
convert(varchar(10),start_time,121)+' 12:00')/60.0
when  convert(varchar(10),start_time,114) between '12:01' and '14:00' then 4
when  convert(varchar(10),start_time,114) between '14:01' and '18:00' then
datediff(n,start_time,convert(varchar(10),start_time,121)+' 18:00')/60.0
else 0 end)-
(case when convert(varchar(10),end_time,114)<='08:30' then 7.5
when convert(varchar(10),end_time,114) between '08:31' and '12:00'  then 4+datediff(n,end_time,
convert(varchar(10),end_time,121)+' 12:00')/60.0
when  convert(varchar(10),end_time,114) between '12:01' and '14:00' then 4
when  convert(varchar(10),end_time,114) between '14:01' and '18:00' then
datediff(n,end_time,convert(varchar(10),end_time,121)+' 18:00')/60.0
else 0 end)
when datediff(d,start_time,end_time)>0
then (case when convert(varchar(10),start_time,114)<='08:30' then 7.5
when convert(varchar(10),start_time,114) between '08:31' and '12:00'  then 4+datediff(n,start_time,
convert(varchar(10),start_time,121)+' 12:00')/60.0
when  convert(varchar(10),start_time,114) between '12:01' and '14:00' then 4
when  convert(varchar(10),start_time,114) between '14:01' and '18:00' then
datediff(n,start_time,convert(varchar(10),start_time,121)+' 18:00')/60.0
else 0 end)+
(case when convert(varchar(10),end_time,114)<='08:30' then 7.5
when convert(varchar(10),end_time,114) between '8:31' and '12:00'  then 4+datediff(n,end_time,
convert(varchar(10),end_time,121)+' 12:00')/60.0
when  convert(varchar(10),end_time,114) between '12:01' and '14:00' then 4
when  convert(varchar(10),end_time,114) between '14:01' and '18:00' then
datediff(n,end_time,convert(varchar(10),end_time,121)+' 18:00')/60.0
else 0 end)+(
datediff(d,start_time,end_time)-1-(select count(1) from holiday where 
h_date between tab.start_time and tab.end_time))*7.5
end
from tab
写了半天，要下班了，还有一种开如日期跟结束日期在假期的情况，楼主自己改改
zhangle861010 2013-10-08
  • 打赏
  • 举报
 回复
SQL还是不会写
--小F-- 2013-10-08
  • 打赏
  • 举报
 回复
你这样设置当然更加简单了 如果你不这样设置的话 可以只设置那些比较特殊的假期 比如中秋，国庆这些。
zhangle861010 2013-10-08
  • 打赏
  • 举报
 回复
b h_time 2013-09-01 2013-09-07 2013-09-08 2013-09-14 2013-09-15 2013-09-19 2013-09-20 2013-09-21 2013-09-29 这样设置假期表吗？
--小F-- 2013-10-08
  • 打赏
  • 举报
 回复
给个思路吧。 首先判断假期在不在 start_time和end_time之内 如果在的话 计算出来的差值就减少假期对应的小时数（7.5*天数） 如果不在 则Datediff计算上午8:30--12:00 下午14:00--18:00中的小时数
zhangle861010 2013-10-08
  • 打赏
  • 举报
 回复
如果是把假期放入 b表中的 a列 那这个SQL应该怎么写？
quchen520 2013-10-08
  • 打赏
  • 举报
 回复
select Datediff(h,start_time,end_time)
--小F-- 2013-10-08
  • 打赏
  • 举报
 回复
引用 2 楼 zhangle861010 的回复:
F姐 如果要设置成表的话 应该只把假期列里面就可以了吗？
对的 周末是可以排除的。
zhangle861010 2013-10-08
  • 打赏
  • 举报
 回复
F姐 如果要设置成表的话 应该只把假期列里面就可以了吗？
--小F-- 2013-10-08
  • 打赏
  • 举报
 回复
9月份的正常休假?这个不设置成一个表的话是没办法的。比如中秋节三天。你用什么去判断？
Linux系统编程第08期：时间管理和定时器编程
本期课程是《Linux系统编程》第08期，主要讲解在Linux下时间管理的基本概念、时间的获取和设置、定时器编程，包括简单的闹钟alarm、间隔定时器interval timer、POSIX timer编程。
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
日、月、年、时、分、秒之差及时间间隔计算。【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，...
linux时间间隔计算
项目中需要达到一定的时间要求指标 所以需要将时间间隔计算一下 函数 1、time() #include &lt;time.h&gt; time_t time(time_t *t); 主要的用法是两种 time_t begin = time(NULL) 或者 time_t ...
C#时间间隔计算
c# 时间比较|C#时间间隔计算 c#事件比较直接可以用减法，即TimeSpan ts1 = DateTime.Now - DateTime.Now.AddDays(-1); 它返回的是一个时间间隔。它有如下属性 Days 获取当前 TimeSpan 结构所表示的时间间隔的天数...
shell编程之，时间表示，时间间隔计算
today=`date -d +%Y%m%d%H%M%S` (结果是 20181218160101) today=`date -d +%Y年%m月%d日%H时%M分%S秒` (结果是 2018年12月18日16时01分01秒) today=`date -d &quot;...计算时间间隔，以秒为...
MS-SQL Server

34,590

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章