How to calculate the break time from a second tabl

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to calculate the break time from a second tabl

Author

Topic

rvgeffen
Starting Member

5 Posts

Posted - 2015-04-29 : 08:24:41

I modified the question. Now a sample table is included and an expected result list. I also added a piece of the sql to obtain the results.

The remaining question is how to calculate the total break time in minutes. The first two columns of the results are already in the code.

Can anyone please help me?
Thanks in advance

Sample of table clock

employee check_in check_out
----------- ---------------- ----------------
1 08:00:00.0000000 11:00:00.0000000
2 08:30:00.0000000 12:14:00.0000000
2 12:25:00.0000000 16:00:00.0000000
1 11:30:00.0000000 14:00:00.0000000

sample of table breakt
startt endt
---------------- ----------------
09:45:00.0000000 10:00:00.0000000
12:00:00.0000000 12:30:00.0000000

The result I am searching for:
| Employee | Timemin| Breakmin|
+----------+--------+---------+
| 1 | 180 | 15 |
| 2 | 224 | 19 |
| 2 | 215 | 5 |
| 1 | 150 | 30 |
+----------+--------+---------+


DROP TABLE breakt;
CREATE TABLE breakt(
  startt TIME
, endt TIME 
);
INSERT INTO breakt(startt,endt) VALUES ('09:45:00','10:00:00');
INSERT INTO breakt(startt,endt) VALUES ('12:00:00','12:30:00');
select * from breakt

DROP TABLE clock;
CREATE TABLE clock(
  employee integer 
, check_in TIME 
, check_out TIME 
);
INSERT INTO clock(employee,check_in,check_out) VALUES (1,'08:00:00','11:00:00');
INSERT INTO clock(employee,check_in,check_out) VALUES (2,'08:30:00','12:14:00');
INSERT INTO clock(employee,check_in,check_out) VALUES (2,'12:25:00','16:00:00');
INSERT INTO clock(employee,check_in,check_out) VALUES (1,'11:30:00','14:00:00');
select * from clock

select employee,
DATEDIFF(MINUTE,check_in,check_out) as timemin
from clock

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-04-29 : 09:47:10

Please follow these guidelines and repost your question:

http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2015-04-30 : 14:58:25

[code]select c.employee
,datediff(mi,c.check_in,c.check_out)
,sum(datediff(mi
,case when b.startt<c.check_in then c.check_in else b.startt end
,case when b.endt>c.check_out then c.check_out else b.endt end
)
)
from clock as c
left outer join breakt as b
on b.startt<c.check_out
and b.endt>c.check_in
group by c.employee
,c.check_in
,c.check_out[/code]

rvgeffen
Starting Member

5 Posts

Posted - 2015-04-30 : 15:45:24

Bitsmed and gbritton, Thanks. The code works perfect and I do understand how the code is working (but couldn't find out myself).
Super the help on this forum.

RvGeffen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources