>我有以下问题,我试图解决(我已经尝试过 PIVOT,但没有走得太远......
列出的是从一个日期流向另一个日期的 3 个过程(此处从 4 月 1 日到 4 月 3 日)
<Process> <StartTask1> <StartTask2> <StartTask3> <StartTask4>
1 01.04.2014 23:30 02.04.2014 00:30 02.04.2014 02:30 02.04.2014 04:30
2 02.04.2014 05:30 02.04.2014 07:30 02.04.2014 12:30 02.04.2014 14:30
3 02.04.2014 17:30 02.04.2014 21:30 03.04.2014 03:30 03.04.2014 05:30
最好是4月2日的报告,如下所示:2.04.2014 (仅限4月2日00:00至23:59:59之间)
任务 1 和任务 2 之间的总时间:30 + 120 + 240 = 390 分钟
任务 2 和任务 3 之间的总时间:120 + 300 + 150 = 570 分钟
任务 3 和任务 4 之间的总时间:120 + 120 + 0 = 240 分钟
任务4 和任务 1 之间的总时间:60 + 180 = 240 分钟
这样我们每天总共有 1440 分钟。(在这种情况下,仅限 4 月 2 日 00:00:00 和 23:59:59 之间的日期)
Task1-Task2 Task2-Task3 Task3-Task4 Task4-Task1
390 minutes 570 minutes 240 minutes 240 minutes
create table #temp
(
id int IDENTITY(1,1),
task1 datetime,
task2 datetime,
task3 datetime,
task4 datetime,
)
insert into #temp values ('20140401 23:30','20140402 00:30','20140402 02:30','20140402 04:30'),
('20140402 05:30','20140402 07:30','20140402 12:30','20140402 14:30'),
('20140402 17:30','20140402 21:30','20140403 03:30','20140403 05:30')
declare @refdate datetime = '20140402'
declare @refdate2 datetime = '20140403'
select sum(case when totaltimes.minutes1 < 0 then 0 else totaltimes.minutes1 end) as totalminutes1,
sum(case when totaltimes.minutes2 < 0 then 0 else totaltimes.minutes2 end) as totalminutes2,
sum(case when totaltimes.minutes3 < 0 then 0 else totaltimes.minutes3 end) as totalminutes3
from #temp
cross apply(values(case when task1 < @refdate then @refdate else task1 end,case when task1 > @refdate2 then @refdate2 else task1 end)) as a1(tstart,tend)
cross apply(values(case when task2 < @refdate then @refdate else task2 end,case when task2 > @refdate2 then @refdate2 else task2 end)) as a2(tstart,tend)
cross apply(values(case when task3 < @refdate then @refdate else task3 end,case when task3 > @refdate2 then @refdate2 else task3 end)) as a3(tstart,tend)
cross apply(values(case when task4 < @refdate then @refdate else task4 end,case when task4 > @refdate2 then @refdate2 else task4 end)) as a4(tstart,tend)
cross apply(values(DATEDIFF(minute,a1.tstart,a2.tend), DATEDIFF(minute,a2.tstart,a3.tend), DATEDIFF(minute,a3.tstart,a4.tend))) as totaltimes(minutes1,minutes2,minutes3)
drop table #temp