我有一个表ID（workers_id） ，Name，time_worked，time_to_work，。Contract_Start_Date Date_of_Entry该表保存工人每天的条目。我想计算他到目前为止收集的加班费。我每天都有该表中每个合同的相同条目，其中条目之间的唯一区别是Contract_STart_Date和time_to_work。他一获得一份新合同，便每天在该表中获得新的入场券（我必须纠正这一天但没有时间的atm，因此对于这个问题来说就不那么灵活了）。
我有下表
| ID | Name | time_worked | time_to_work | Contract_Start_Date | Date_of_Entry |
| -- | ---- | ----------- | ------------ | ------------------- | ------------- |
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-01-01 |
| 11 | Jack | 8 | 8 | 2013-04-01 | 2013-01-01 |
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-01-02 |
| 11 | Jack | 8 | 8 | 2013-04-01 | 2013-01-02 |
...
| 11 | Jack | 6 | 8 | 2013-01-01 | 2013-04-15 |
| 11 | Jack | 6 | 4 | 2013-04-15 | 2013-04-15 |
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-04-16 |
| 11 | Jack | 8 | 4 | 2013-04-15 | 2013-04-16 |
我想为杰克加计相关合同的加班费。
我想我找到了一种方法（在逻辑上）解决此问题，但无法将我的想法转化为代码。这是方法：
我SeqNumber根据合同为每天设置了一个数字（）（已通过下面的代码完成）。
| ID | Name | time_worked | time_to_work | Contract_Start_Date | Date_of_Entry | SeqNumber
| -- | ---- | ----------- | ------------ | ------------------- | ------------- |----------
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-01-01 |1
| 11 | Jack | 8 | 8 | 2013-04-01 | 2013-01-01 |2
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-01-02 |1
| 11 | Jack | 8 | 8 | 2013-04-01 | 2013-01-02 |2
...
| 11 | Jack | 6 | 8 | 2013-01-01 | 2013-04-15 |1
| 11 | Jack | 6 | 4 | 2013-04-15 | 2013-04-15 |2
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-04-16 |1
| 11 | Jack | 8 | 4 | 2013-04-15 | 2013-04-16 |2
现在设置了ConSeqNumberdate_of_entry所属的contract_start_date的数字（）
| ID | Name | time_worked | time_to_work | Contract_Start_Date | Date_of_Entry | SeqNumber| ConSeqNumber
| -- | ---- | ----------- | ------------ | ------------------- | ------------- |----------| ------------
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-01-01 |1 |1
| 11 | Jack | 8 | 8 | 2013-04-01 | 2013-01-01 |2 |1
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-01-02 |1 |1
| 11 | Jack | 8 | 8 | 2013-04-01 | 2013-01-02 |2 |1
...
| 11 | Jack | 6 | 8 | 2013-01-01 | 2013-04-15 |1 |2
| 11 | Jack | 6 | 4 | 2013-04-15 | 2013-04-15 |2 |2
| 11 | Jack | 8 | 8 | 2013-01-01 | 2013-04-16 |1 |2
| 11 | Jack | 8 | 4 | 2013-04-15 | 2013-04-16 |2 |2
解决方案是对SeqNumber和ConSeqNumber相等的每个条目求和。
我的输出将是（根据计算time_worked-time_to_work并汇总值。（8-8）+（8-8）+（6-4）+（8-4）= 6
| Overtime |
| -------- |
| 6 |
我的完整代码是：
select ID, Name,(sum(time_worked)-sum(time_to_work)) as 'overtime'
from (
Select *,
ROW_NUMBER() over (partition by Date_of_Entry order by Contract_Start_Date asc) as seqnum
from MyTable where Contract_Start_Date <= Date_of_Entry
)
MyTable
WHERE seqnum = 1
AND YearA = DATEPART(YEAR, GETDATE()) -1
AND DATE_of_Entry <= GETDATE()
AND DATEPART(MONTH, Date_of_Entry) BETWEEN 4 and 9
GROUP BY ID, Name
好的，看来我找到了解决方案：
数据样本
CREATE TABLE #test(WorkerID int,
TimeWorked int,
TimeToWork int,
ContractStartDate datetime,
DateOfEntry datetime
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 8, '2013-01-01', '2013-01-01');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 4, '2013-04-15', '2013-01-01');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 6, '2013-08-15', '2013-01-01');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 8, '2013-01-01', '2013-01-02');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 4, '2013-04-15', '2013-01-02');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 6, '2013-08-15', '2013-01-02');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 7, 8, '2013-01-01', '2013-04-15');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 6, 4, '2013-04-15', '2013-04-15');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 6, 6, '2013-08-15', '2013-04-15');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 4, 8, '2013-01-01', '2013-04-16');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 8, 4, '2013-04-15', '2013-04-16');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 4, 6, '2013-08-15', '2013-04-16');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 2, 8, '2013-01-01', '2013-08-16');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 2, 6, '2013-04-15', '2013-08-16');
INSERT INTO #test (WorkerID, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 2, 5, '2013-08-15', '2013-08-16');
这样我就得到了我想要的。非常感谢这里的所有帮助！
---select WorkerID,(sum(TimeWorked)-sum(TimeToWork)) as 'overtime'
select * ---sum(timeworked - timetowork)
from (
Select *,
ROW_NUMBER() over (partition by DateOfEntry order by ContractStartDate desc) as seqnum
from #test
where ContractStartDate <= DateOfEntry)
#test
where seqnum = 1
drop table #test
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句