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 2005 Forums
 Transact-SQL (2005)
 SQL to find total time without gaps

Author  Topic 

PakMan786
Starting Member

4 Posts

Posted - 2014-12-20 : 10:14:48
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.


Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-12-23 : 05:13:10
You need to serach for something like:
'Interval Packing in SQL'
Go to Top of Page

sz1
Aged Yak Warrior

555 Posts

Posted - 2014-12-23 : 09:23:08
How are you working out your gaps?
are you saying John did and hour from 8 - 9 and then a half hour 9 - 9.30? have you no end time?
How do you want it displayed!

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

We are the creators of our own reality!
Go to Top of Page

PakMan786
Starting Member

4 Posts

Posted - 2014-12-24 : 21:45:23
quote:
Originally posted by sz1

How are you working out your gaps?
are you saying John did and hour from 8 - 9 and then a half hour 9 - 9.30? have you no end time?
How do you want it displayed!

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

We are the creators of our own reality!



Yes as the two shifts add up to 90 mins and there is no gap between them. End time of a shift can easily be determined (start time + duration).
Display just the shifts that add up to 90 or more mins.
Go to Top of Page

mrusman7
Starting Member

1 Post

Posted - 2015-02-21 : 07:52:51
I have one table called std and another is std_sub. Now I have to update subject_detail column of STD table like Math,English


saji3
Go to Top of Page
   

- Advertisement -