文章/答案/技术大牛

发布

社区首页 >问答首页 >按日期计算的有条件款项(按合同计算的加班费)

问按日期计算的有条件款项(按合同计算的加班费)
EN

Stack Overflow用户

提问于 2014-03-11 18:37:54

回答 4查看 345关注 0票数 2

我有一张有ID (workers_id)，Name，time_worked，time_to_work，Contract_Start_Date，Date_of_Entry的桌子。此表保存员工每天的条目。我想算一下他到现在为止的加班费。对于该表中的每个合同，我每天都有相同的条目，其中条目之间唯一的区别是Contract_STart_Date和time_to_work。一旦他得到了一份新的合同，他每天都会得到一份新的合同(有一天，我必须纠正这一点，但我没有时间提款机，因此，对于这个问题，我认为这是不灵活的)。

我有下表

| 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

现在设置一个数字(ConSeqNumber)，contract_start_date date_of_entry属于该数字

| 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

sql

sql-server

tsql

回答 4

Stack Overflow用户

回答已采纳

发布于 2014-03-15 15:49:41

好吧，看来我找到了解决办法：

数据样本

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

票数 0

Stack Overflow用户

发布于 2014-03-12 14:52:01

我仍然不太清楚你想要什么，所以我给你提供了以下几个不同的选项。如果你张贴你想要的结果集，我们可以确保我们的解决方案是你的想法。

DECLARE @Hours TABLE
(
    WorkerID int,
    WorkerName varchar(50),
    TimeWorked int,
    TimeToWork int,
    ContractStartDate datetime,
    DateOfEntry datetime
)

INSERT INTO @Hours (WorkerID, WorkerName, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 'Jack', 8, 8, '2013-01-01', '2013-01-01');
INSERT INTO @Hours (WorkerID, WorkerName, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 'Jack', 8, 8, '2013-01-01', '2013-01-02');
INSERT INTO @Hours (WorkerID, WorkerName, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (12, 'Norman', 7, 6, '2013-01-01', '2013-01-01');
INSERT INTO @Hours (WorkerID, WorkerName, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 'Jack', 6, 4, '2013-04-15', '2013-04-15');
INSERT INTO @Hours (WorkerID, WorkerName, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 'Jack', 7, 8, '2013-01-01', '2013-04-15');
INSERT INTO @Hours (WorkerID, WorkerName, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 'Jack', 8, 4, '2013-04-15', '2013-04-16');
INSERT INTO @Hours (WorkerID, WorkerName, TimeWorked, TimeToWork, ContractStartDate, DateOfEntry) VALUES (11, 'Jack', 4, 8, '2013-01-01', '2013-04-16');

-- If you want the total for the worker for the full contract period repeated for each line, you could use this PARTITION BY version:

SELECT
    *,
    SUM(TimeWorked-TimeToWork) OVER (PARTITION BY WorkerID, ContractStartDate) AS OverTimeForContract,
    -- if you don't want "undertime" to count against overtime, such that working 1 hour less one day doesn't absolve you from having worked 1 hour extra the previous day, you can do this fancy footwork:
    SUM(CASE WHEN TimeWorked > TimeToWork THEN TimeWorked-TimeToWork ELSE 0 END) OVER (PARTITION BY WorkerID, ContractStartDate) AS OverTimeOnlyForContract
FROM        @Hours
WHERE       DateOfEntry BETWEEN '2013-01-01' AND '2013-04-15'; -- choose whatever dates you want, of course

-- If you don't need the value repeated for each entry, you could of course do a simple GROUP BY

SELECT
    WorkerID,
    WorkerName,
    ContractStartDate,
    SUM(TimeWorked-TimeToWork) AS OverTimeForContract,
    SUM(CASE WHEN TimeWorked > TimeToWork THEN TimeWorked-TimeToWork ELSE 0 END) AS OverTimeOnlyForContract
FROM        @Hours
WHERE       DateOfEntry BETWEEN '2013-01-01' AND '2013-04-15'
GROUP BY    WorkerID,
            WorkerName,
            ContractStartDate;

票数 0

Stack Overflow用户

发布于 2014-03-14 13:38:21

我已经采取了相同的数据样本的@Riley.if，我拿你的样本数据，然后也是加班费是正确的，即6。

;with CTE as
(
select *,ROW_NUMBER() over (partition by workerid,DateofEntry order by ContractStartDate asc) as seqnum,
ROW_NUMBER() over (partition by workerid order by workerid asc) as seqnum1
 from @Hours 
)
,CTE1 as
(
select WorkerID,sum(timeworked - timetowork)overtime from cte where seqnum=1 group by WorkerID
)
select a.WorkerID,a.WorkerName,b.overtime from cte a inner join cte1 b on a.WorkerID=b.WorkerID
where a.seqnum1=1

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/22333726

复制

相似问题

问按日期计算的有条件款项(按合同计算的加班费)
EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问按日期计算的有条件款项(按合同计算的加班费)EN

回答 4

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问按日期计算的有条件款项(按合同计算的加班费)
EN