SQL行在两个时间戳之间的时间

Question

我有一个包含两个时间戳的记录的表，每个时间戳与一个代理应该执行如下任务的时间块相关联。

ID  AgentName  Date     Task        Start           End
-----------------------------------------------------------------
1   Doris   3/12/2018   Available   3/12/18 12:30   3/12/18 14:15
2   Doris   3/12/2018   Break       3/12/18 14:15   3/12/18 14:30
3   Doris   3/12/2018   Available   3/12/18 14:30   3/12/18 16:45
4   Doris   3/12/2018   Lunch       3/12/18 16:45   3/12/18 17:15
5   Doris   3/12/2018   Available   3/12/18 17:15   3/12/18 19:00
6   Doris   3/12/2018   Break       3/12/18 19:00   3/12/18 19:15
7   Doris   3/12/2018   Available   3/12/18 19:15   3/12/18 21:00

我需要把每条记录都分成15分钟的间隔时间，比如.

ID  AgentName   Date    Task        Start           End             Interval
-----------------------------------------------------------------------------
1   Doris   3/12/2018   Available   3/12/18 12:30   3/12/18 14:15   12:30
1   Doris   3/12/2018   Available   3/12/18 12:30   3/12/18 14:15   12:45
1   Doris   3/12/2018   Available   3/12/18 12:30   3/12/18 14:15   13:00
1   Doris   3/12/2018   Available   3/12/18 12:30   3/12/18 14:15   13:15
1   Doris   3/12/2018   Available   3/12/18 12:30   3/12/18 14:15   13:30
1   Doris   3/12/2018   Available   3/12/18 12:30   3/12/18 14:15   13:45
1   Doris   3/12/2018   Available   3/12/18 12:30   3/12/18 14:15   14:00
2   Doris   3/12/2018   Break       3/12/18 14:15   3/12/18 14:30   14:15
3   Doris   3/12/2018   Available   3/12/18 14:30   3/12/18 16:45   14:30
3   Doris   3/12/2018   Available   3/12/18 14:30   3/12/18 16:45   14:45
3   Doris   3/12/2018   Available   3/12/18 14:30   3/12/18 16:45   15:00

我有一个15分钟间隔的参考表，但是我现在不知道该去哪里。我试过了，没时间再回来了。

select * 
from dbo.Tbl_timeSlot t 
full outer join dbo.Schedule s on t.Slot = convert(time, s.Start)
where convert(date, Start) = '3/12/18'
  and AgentName = 'Doris'

Answer 1

您可以使用CTE试用此解决方案，并在这里进行现场演示( http://rextester.com/live/UVG28279 )。

declare @myclock table (ID int, AgentName varchar(100), LogDate Date, Task varchar(20),StartTime DateTime,EndTime DateTime)


insert into @myclock
values 
(1   ,'Doris','3/12/2018','Available','3/12/18 12:30','3/12/18 14:15'),
(2   ,'Doris','3/12/2018','Break','3/12/18 14:15','3/12/18 14:30'),
(3   ,'Doris','3/12/2018','Available','3/12/18 14:30','3/12/18 16:45'),
(4   ,'Doris','3/12/2018','Lunch','3/12/18 16:45','3/12/18 17:15'),
(5   ,'Doris','3/12/2018','Available','3/12/18 17:15','3/12/18 19:00'),
(6   ,'Doris','3/12/2018','Break','3/12/18 19:00','3/12/18 19:15'),
(7   ,'Doris','3/12/2018','Available','3/12/18 19:15','3/12/18 21:00')

--select * from @myclock;

;with myCteNewClock as (
  select *,StartTime NewStartTime from @myclock
  union all
  select x.ID,x.AgentName,x.LogDate,x.Task,x.StartTime,x.EndTime, dateadd(minute,15,x.NewStartTime) NewStartTime from myCteNewClock x
     inner join @myclock c 
       on x.ID = c.ID  
     where
        dateadd(minute,15,x.NewStartTime) <= x.EndTime 

 )
select ID,AgentName,LogDate,Task,StartTime,EndTime, convert(time(0),NewStartTime) [Interval]
 from myCteNewClock order by ID