我有一个像下面这样的表，到今天为止在EntryTime列中都有条目。
表格1
PersonID|StartTime|EndTime|EntryTime
1 |10:00 |12:00 |2014-01-01
1 |10:00 |12:00 |2014-01-02
1 |10:00 |12:00 |2015-01-03
...
和合同表：
表2
PersonID|ContractID| ContractStart| ContractType|Hours
1 |1 |2014-01-01 |Student |8
1 |2 |2015-01-01 |Trainee |40
1 |3 |2015-03-01 |Student |20
1 |4 |2015-05-01 |Student |12
现在我要总结的时间(datediff(minute, StartTime, Endtime))为当前contractType使用结果如下表所述。的contracttype必须是连续的从最后一个。因此，在此示例中，合同3 + 4的所有条目都是相关的。
我的想法是使用aloop来选择相关合同。
WHILE (Select ContractType from Table2) = 'Student'
Begin
Select ContractStart from Table2
Order By ContractStart
End
不幸的是，循环还无法正常工作，如果我不知道如何将其实现到where子句中。
我尝试获取的结果表是的subset，Table1所有条目都始于2015-03-01
没有示例很难知道您想要什么，但这是一个尝试。还要对您的类型进行很多猜测。
注意：避免 while循环和游标，坚持设置逻辑=）
declare @Table1 as table
(
PersonID int
,StartTime datetime
,EndTime datetime
,EntryTime date
)
insert into @Table1
values
(1 ,'10:00', '12:00', '2014-01-01')
,(1 ,'10:00', '12:00', '2014-01-02')
,(1 ,'10:00', '12:00', '2015-01-03')
,(2 ,'10:00', '12:00', '2015-01-03')
,(2 ,'10:00', '12:00', '2015-01-04')
,(3 ,'10:00', '12:00', '2015-01-03')
,(3 ,'10:00', '12:00', '2015-01-05')
declare @Table2 as table
(
PersonID int
,ContractID int
,ContractStart date
,ContractType varchar(10)
,[Hours] float
)
insert into @Table2
values
(1 ,1 ,'2014-01-01', 'Student', 8)
,(1 ,2 ,'2015-01-01', 'Trainee', 40)
,(1 ,3 ,'2015-03-01', 'Student', 20)
,(1 ,4 ,'2015-05-01', 'Student', 12)
,(2 ,1 ,'2014-01-01', 'Student', 8)
,(2 ,2 ,'2015-01-01', 'Trainee', 40)
,(2 ,3 ,'2015-03-01', 'Student', 20)
,(2 ,4 ,'2015-05-01', 'Student', 12)
,(3 ,1 ,'2014-01-01', 'Student', 8)
,(3 ,2 ,'2015-01-01', 'Trainee', 40)
,(3 ,3 ,'2015-03-01', 'Student', 20)
,(3 ,4 ,'2015-05-01', 'Student', 12)
--Current contrat (for PersonId = 1)
declare @CurrentType varchar(10) =
(select top 1 ContractType from @Table2 where PersonID = 1 order by ContractStart desc)
--Last different type start (for PersonId = 1)
declare @LastDiff date =
(select top 1 ContractStart from @Table2 where PersonID = 1 and @CurrentType != ContractType order by ContractStart desc)
--Current type span (for PersonId = 1)
select datediff(minute, min(StartTime + cast(EntryTime as datetime)), max(EndTime + cast(EntryTime as datetime))) as [PesonId = 1 TimeSpan]
from @Table1
where PersonID = 1
and EntryTime > @LastDiff
-- Puting everything togheter
select t1.PersonId, datediff(minute, min(t1.StartTime + cast(t1.EntryTime as datetime)), max(t1.EndTime + cast(t1.EntryTime as datetime))) as TimeSpan
from @Table1 t1
where t1.EntryTime >
(
select top 1 t2.ContractStart
from @Table2 t2
where t2.PersonID = t1.PersonID
and t2.ContractType !=
(
select top 1 t2b.ContractType from @Table2 t2b where t2b.PersonID = t2.PersonID order by t2b.ContractStart desc
)
order by t2.ContractStart desc
)
group by t1.PersonId
该代码段是不言自明的，它给了我：
PersonId TimeSpan
-------- --------
1 120
2 1560
3 3000
注意我添加了一些数据，以便做出更好的测试示例
本文收集自互联网，转载请注明来源。
如有侵权，请联系 [email protected] 删除。
我来说两句