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.
| 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' |
 |
|
|
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', 30We are the creators of our own reality! |
|
|
|
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', 30We 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. |
|
|
|
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,Englishsaji3 |
|
|
|
|
|
|
|
|
SQL to find total time without gaps