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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Help with SQL

Author  Topic 

PakMan786
Starting Member

4 Posts

Posted - 2014-12-20 : 20:39:53
I am trying to find the SQL to return all rows where an employee has worked 90 minutes or more (without a gap) in a day. So far I haven't been successfull.

the table has 4 fields first is employee code, 2nd is the date, 3rd is the start time and the last is duration.

CREATE TABLE EMPLOYEE (E_CODE VARCHAR(10), [DATE] VARCHAR(10), S_TIME VARCHAR(5), DURATION INT );

INSERT INTO EMPLOYEE VALUES ( 'JOHN', '2014/12/20', '08:00', 60);
INSERT INTO EMPLOYEE VALUES ( 'JOHN', '2014/12/20', '09:00', 30);
INSERT INTO EMPLOYEE VALUES ( 'JOHN', '2014/12/20', '10:00', 45);
INSERT INTO EMPLOYEE VALUES ( 'JACK', '2014/12/20', '08:00', 120);
INSERT INTO EMPLOYEE VALUES ( 'JACK', '2014/12/20', '10:10', 60);
INSERT INTO EMPLOYEE VALUES ( 'JACK', '2014/12/20', '11:30', 60);


With this data the SQL should return the first two rows for john and third row for jack.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-21 : 14:46:47
HEre's a partial solution. It depends on two assumptions:

1. no overlapping time periods
2. no period crosses midnight

Note: This does not return original rows as it stands, just identifies days where the conditions are met


declare @e table (E_CODE VARCHAR(10), [DATE] VARCHAR(10), S_TIME VARCHAR(5), DURATION INT );

INSERT INTO @e VALUES ( 'JOHN', '2014/12/20', '08:00', 1);
INSERT INTO @e VALUES ( 'JOHN', '2014/12/20', '08:01', 1);
INSERT INTO @e VALUES ( 'JOHN', '2014/12/20', '08:02', 58);
INSERT INTO @e VALUES ( 'JOHN', '2014/12/20', '09:00', 30);
INSERT INTO @e VALUES ( 'JOHN', '2014/12/20', '10:00', 45);
INSERT INTO @e VALUES ( 'JACK', '2014/12/20', '08:00', 120);
INSERT INTO @e VALUES ( 'JACK', '2014/12/20', '10:10', 60);
INSERT INTO @e VALUES ( 'JACK', '2014/12/20', '11:30', 60);

SELECT DISTINCT e3.e_code
, e3.date
FROM(
SELECT e2.E_CODE
, e2.DATE
, e2.S_TIME
, CASE
WHEN e2.s_time = DATEADD(minute, e2.lag_dur, e2.lag_time)
OR e2.lag_time IS NULL THEN SUM(e2.duration)
OVER(PARTITION BY e2.e_code, e2.date
ORDER BY e2.s_time
ROWS UNBOUNDED PRECEDING)
ELSE 0
END AS sum_dur
FROM(
SELECT e1.e_code
, e1.date
, e1.s_time
, e1.DURATION
, lag(e1.s_time, 1, NULL) OVER(PARTITION BY e1.e_code, e1.date
ORDER BY s_time) AS lag_time
, lag(e1.duration, 1, 0) OVER(PARTITION BY e1.e_code, e1.date
ORDER BY s_time) AS lag_dur
FROM @e e1
) e2
) e3
WHERE e3.sum_dur >= 90;
Go to Top of Page

PakMan786
Starting Member

4 Posts

Posted - 2014-12-22 : 22:29:24
Thanks but can this be changed to work in previous versions also ie 2005.
Go to Top of Page
   

- Advertisement -