基本休息时间表:
  StopName    StartTime       EndTime
     早        07:50           08:20
     中        11:30           12:20
     晚        17:50           18:30
     夜        12:30           01:30 
  
  生产表：
   col1           TimeIn            
   A001        2011-01-21 07:48:03 
   A002        2011-01-21 07:48:33
   A003        2011-01-21 07:49:23
   A004        2011-01-21 08:20:30
   A005        2011-01-21 08:21:00
   A006        2011-01-21 08:22:36
   A007        2011-01-21 08:23:22
   A008        2011-01-21 08:24:03   
   
时间差统计：
   col1           TimeIn                       NetTime          StandardTime     
   A001        2011-01-21 07:48:03                0                  30
   A002        2011-01-21 07:48:33               20                  30
   A003        2011-01-21 07:49:23                0                  30
   A004        2011-01-21 08:20:30                0                  30
   A005        2011-01-21 08:21:00                6                  30
   A006        2011-01-21 08:22:36               16             30
   A007        2011-01-21 08:23:22                9                  30      
   A008        2011-01-21 08:24:01                0                  30
                                                  51   

;with cte as (
select *,row_number(order by col1) as num
from 生产表
)
select a.col1
  ,a.TimeIn
  ,NetTime = isnull(datediff(second,a.TimeIn,b.TimeIn) - 30,0) 
      - isnull(datediff(second,cast(convert(varchar(10),a.TimeIn,120) + ' ' + c.StartTime  as datetime),cast(convert(varchar(10),a.TimeIn,120) + ' ' + c.EndTime  as datetime))
  ,StandardTime = 30
from cte a left join cte b
on a.num = b.num - 1
left join 基本休息时间表 c
on cast(convert(varchar(10),a.TimeIn,120) + ' ' + c.StartTime  as datetime) between a.TimeIn and b.TimeIn

------解决方案--------------------
探讨

SQL code
;with cte as (
select *,row_number(order by col1) as num
from 生产表
)
select a.col1
  ,a.TimeIn
  ,NetTime = isnull(datediff(second,a.TimeIn,b.TimeIn) - 30,0) 
      - isnull(datediff(s……


------解决方案--------------------
探讨

SQL code
;with cte as (
select *,row_number(order by col1) as num
from 生产表
)
select a.col1
  ,a.TimeIn
  ,NetTime = isnull(datediff(second,a.TimeIn,b.TimeIn) - 30,0) 
      - isnull(datediff(s……


------解决方案--------------------
探讨

SQL code
;with cte as (
select *,row_number(order by col1) as num
from 生产表
)
select a.col1
  ,a.TimeIn
  ,NetTime = isnull(datediff(second,a.TimeIn,b.TimeIn) - 30,0) 
      - isnull(datediff(s……


------解决方案--------------------
探讨

引用:
SQL code
;with cte as (
select *,row_number(order by col1) as num
from 生产表
)
select a.col1
,a.TimeIn
,NetTime = isnull(datediff(second,a.TimeIn,b.TimeIn) - 30,0)
- isnull(d……