Conditional sum based on date (sum overtime by contract)

Question

I have a Table with ID (workers_id), Name, time_worked, time_to_work, Contract_Start_Date, Date_of_Entry. This table holds the entries for every day for a worker. I want to calculate the overtime he collected up till now. I have the same entry for each contract in that table for every day where the only difference between the entries is the Contract_STart_Date and the time_to_work. As soon as he gets a new contract he gets a new entrie for every day in that table (I have to correct that one day but have no time atm, so take that as unflexible for that problem).

I have the following table

| 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    |

I want to add up the overtime for Jack for the relevant contract.

I think I found a way to solve this (logically) but cannot transfer my thoughts into code. This is the approach:

I set a number (SeqNumber) for each day by contract (already accomplished by my code below).

| 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

now is set a number (ConSeqNumber) to which contract_start_date the date_of_entry belongs

| 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

The solution would be to sum every entry where the SeqNumber and the ConSeqNumber are equal.

My output would be (according to the calculation time_worked - time_to_work and summarize the values. (8-8) + (8-8) + (6-4) + (8-4) = 6

| Overtime |
| -------- | 
| 6        |

My full code is:

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

If you are looking to do a running total and you have SQL Server 2012 you can [use the OVER keyword](http://stackoverflow.com/questions/860966/calculate-a-running-total-in-sqlserver/10309947#10309947). Otherwise, it might be helpful for you to post some sample data and some sample output you are trying to achieve. — Riley Major, Mar 11 '14 at 19:10
what if you try to group by time_to_work also, does it help? — fnightangel, Mar 11 '14 at 20:07
I don't see where's the problem. `time_to_work` value is the actual value as it was at `Date_of_Entry`, is it? If yes then it's trivial, if not then it doesn't make much sense. Moreover I'm completely missing the point of `seqnum` - it's not used in the query. I would suggest you edit the question and add a sample of your source data, current output and desired output. This way it will be much easier to understand what you are after. — TomT, Mar 11 '14 at 22:30
I edited the entry. (at)Riley: I use the over keyword in the partition by command. (at)Fnightangle: The group by time wouldn help since I need only one value as a result of the summary (sum(worked-towork)). (at)TomT: Your right, I just saw that I forgot to copy the seqnum statement into the query above. sorry for that! — ruedi, Mar 12 '14 at 09:09
Sorry, I should have been more specific. The OVER keyword, starting in 2012, can be applied to the SUM statement to get a running total. But it seems as if you just need a normal SUM with some specialized grouping. I will think about this further and post an answer if I come up with something. — Riley Major, Mar 12 '14 at 14:31
@ruedi,it will be great if you just show output you are looking for from given sample data. — KumarHarsh, Mar 14 '14 at 12:57
I edited my posting to show you how the output would look like and how to get it. — ruedi, Mar 14 '14 at 13:10
Is it possible that time_worked would be less than time_to_work on any day? If yes, how would you like to handle that entry? — Joseph B, Mar 15 '14 at 15:43
That is possible, so it is possible to have negative overtime. — ruedi, Mar 15 '14 at 19:49

score 0 · Answer 1 · answered Mar 12 '14 at 14:52

I'm still not quite clear what you'd like, so I provided a few different options for you below. If you post your desired result-set we can make sure our solutions are what you have in mind.

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;

Hi Riley. Thanks! Running your code gives me 0 OverTimeOnlyForContract (1) and 2 OverTimeOnlyForContract (2). But that is not right. In contract 1 he has (8-8)+(8-8) that is correct 0. but in contract 2 he has (6-4) + (8-4) that gives 6 instead of 2. Just because you said i didnt make clear what I want to accomplish I edited my post at the end to clarify that. — ruedi, Mar 13 '14 at 08:03
Check the date range. My example dates purposely didn't encompass all of the values you provided for demonstration purposes, because you were only interested in "for the last year from April to September for a specific person" (based on the original question which has been edited out). If you expand the date range in my code it will show 6 (I just verified). — Riley Major, Mar 13 '14 at 15:45
Hi Riley. Your are right. But since I also need undertime the column OverTimeForContract is relevant for me. Running the code i get (-5 and 6). Since the time for the first contract will be added also even when the second contract started already. The problem here is that for every DateOfEntry we have entries for every contract. Maybe i am thinking about it way too long but i cannot come up with a solution. Would be awesome if you could help me with it again! Cheers! — ruedi, Mar 14 '14 at 09:00

score 0 · Answer 2 · answered Mar 14 '14 at 13:38

i have taken the same data samples of @Riley.if I take your sample data then also overtime is right i.e. 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

If I run your code with Rileys sample data I get -5. I thought about it again and I think I have the solution but still have problems to program it. I will add the approach to my posting above now. — ruedi, Mar 14 '14 at 18:43

Joseph B · Answer 3 · 2014-03-15T15:45:01.510

The following approach gets the entries for the newest Contract (by checking that there is no other entry with a newer Contract Start Date), and then finding the difference between time_worked and time_to_work.

select
    ID, Name, SUM(time_worked - time_to_work) as overtime, MAX(Contract_Start_Date) AS Contract_Start_Date
from
    TimeEntry T1
WHERE
    NOT EXISTS
(
SELECT 1 
FROM
    TimeEntry T2
WHERE   T2.ID = T1.ID
AND T2.Date_of_Entry = T1.Date_of_Entry
AND T2.Contract_Start_Date > T1.Contract_Start_Date
)
GROUP BY ID, Name;

I'll create a SQL Fiddle soon.

score 0 · Accepted Answer · answered Mar 15 '14 at 15:49

OK, looks like I found the solution:

Data Sample

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');

and with this I get what I want. Thanks a lot to all helpin me here!

---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

Conditional sum based on date (sum overtime by contract)

4 Answers4