Get the datetime range of rows in one column

Good day!

I have table attendance
fields are:

EMP_NO
DTR

The DTR data has a data of In and Out of employee…

Like for example: Schedule 05:35:00 - 13:35:00

EMP_NO: 1001 DTR: 2011-10-24 05:35:10 //IN
EMP_NO: 1001 DTR: 2011-10-24 05:35:15 // IN
EMP_NO: 1001 DTR: 2011-10-24 13:35:00 // OUT
EMP_NO: 1001 DTR: 2011-10-24 13:40:20 // OUT

As you can see the employee twice IN and also he out twice. How can I get the first In and the last Out? And also how can I get the total hours of employee?

Thank you so much…

Take a look at MIN and MAX

You already asked that question in another thread, didn’t you?

It is new format, because this time the time in and timeout is in the same column.

I already resolved it using this code:


INSERT INTO dtr_total(EMP_NO, Total) SELECT a.EMP_NO, TIMEDIFF(max(b.ATTENDANCE), min(a.ATTENDANCE)) 
FROM test_att a 
LEFT JOIN test_att b ON a.EMP_NO = b.EMP_NO; 

table:test_att
columns: EMP_NO,DATE, ATTENDANCE

table: dtr_total
columns: EMP_NO, Total, Rendered

Now I don’t know how can I get the rendered, the rendered is timedifference between IN and OUT but only get the difference between their schedule

like for example my shift is 05:35:00 - 13:35:00

my IN = 05:35:00 and OUT = 14:35:00 Total will be = 9 hours because he out late. and Rendered should be = 8 , no matter he out late the rendered will still 8 hours, unless he timeout early or timein late the rendered will be minus.

I have 3 shifts, 21:35:00- 05:35:00 which is night shift, and morning shift 05:35:00 - 13:35:00, 13:35:00 - 21:35:00

Thank you so much

Thank you.

I’m sure you have 4 shifts. I remember from the other thread :slight_smile:

And that query is missing a GROUP BY.

And the problem with the shifts and the calculation of the ‘real’ total time is the same as in your previous thread.

I tried this code:


UPDATE dtr_total SET Rendered = (SELECT TIMEDIFF(max(b.ATTENDANCE), min(a.ATTENDANCE))
FROM test_att a
LEFT JOIN test_att b ON a.EMP_NO = b.EMP_NO);

I don’t know how can I add the condition to get only the hours between his schedule like for example 05:35:00 - 13:35:00

Thank you so much

I tried this query:


UPDATE dtr_total d, test_att a
LEFT JOIN test_att b ON a.EMP_NO = b.EMP_NO SET d.Rendered = sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(a.ATTENDANCE)) < time_to_sec('05:35:00') then 0
              else time_to_sec(time(a.ATTENDANCE)) - time_to_sec('05:35:00') 
            end +
            case 
              when time_to_sec(time(b.ATTENDANCE)) < time_to_sec('13:35:00') then 0
              else time_to_sec(time(b.ATTENDANCE)) - time_to_sec('13:35:00')
            end
           );

this query always based on the a.ATTENDANCE or time in.

I revised my code:


UPDATE dtr_total d, test_att a
LEFT JOIN test_att b ON a.EMP_NO = b.EMP_NO SET d.Rendered = sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(a.ATTENDANCE)) > time_to_sec('05:35:00') then 0
              else time_to_sec('05:35:00') - time_to_sec(time(a.ATTENDANCE))
            end +
            case 
              when time_to_sec(time(b.ATTENDANCE)) < time_to_sec('13:35:00') then 0
              else time_to_sec('13:35:00') - time_to_sec(time(b.ATTENDANCE))
            end
	);

when I run this code it only sum data between 05:35:00 - 13:35:00 and its correct, but even the shift is 13:35:00 - 21:35:00 also change, which is wrong, because in my case statement in it onyl for 05:35:00 - 13:35:00

Thank you

Check out the other thread. You are asking the same questions again, and we already answered them.

I tried his suggestion and still nothing has change…

Thank you so much…

I tried this:


UPDATE dtr_total d, test_att a
LEFT JOIN test_att b ON a.EMP_NO = b.EMP_NO SET d.Rendered = sec_to_time(time_to_sec('08:00:00') + 
            case 
              when time_to_sec(time(a.ATTENDANCE)) < time_to_sec('05:35:00') then 0
              else time_to_sec('05:35:00') - time_to_sec(time(a.ATTENDANCE))
            end +
            case 
              when time_to_sec(time(b.ATTENDANCE)) < time_to_sec('13:35:00') then 0
              else time_to_sec('13:35:00') - time_to_sec(time(b.ATTENDANCE))
            end 
           ) WHERE time(a.ATTENDANCE) BETWEEN '01:35:01' and '07:00:00';

all data has change even the shift is not 05:35:00 - 13:35:00

Thank you

Good day!

I import data to database and when I want to get the total hours per day, the output is wrong when i used this query:


INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))
FROM regular_dtr a
LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO;

I have this data:
EMP_NO DATE DTR
300395 11/3/2011 11/3/11 5:35 AM
300395 11/3/2011 11/3/11 1:35 PM
300395 11/4/2011 11/4/11 5:35 AM
300395 11/4/2011 11/4/11 1:35 PM
300395 11/5/2011 11/5/11 5:35 AM
300395 11/5/2011 11/5/11 1:35 PM
300395 11/6/2011 11/6/11 5:35 AM
300395 11/6/2011 11/6/11 1:35 PM
300395 11/7/2011 11/7/11 5:35 AM
300395 11/7/2011 11/7/11 1:35 PM
300395 11/8/2011 11/8/11 5:35 PM
300395 11/8/2011 11/8/11 1:35 PM
300395 11/9/2011 11/9/11 5:35 PM
300395 11/9/2011 11/9/11 1:35 PM
300395 11/10/2011 11/10/11 5:35 AM
300395 11/10/2011 11/10/11 1:35 PM
300395 11/11/2011 11/11/11 5:35 AM
300395 11/11/2011 11/11/11 1:35 PM
300395 11/12/2011 11/12/11 5:35 AM
300395 11/12/2011 11/12/11 1:35 PM
300395 11/14/2011 11/14/11 5:35 AM
300395 11/14/2011 11/14/11 1:35 PM
300395 1/15/2011 11/15/11 5:35 AM
300395 11/15/2011 11/15/11 1:35 PM
9300127 11/3/2011 11/3/11 5:35 AM
9300127 11/3/2011 11/3/11 1:35 PM
9300127 11/4/2011 11/4/11 5:35 AM
9300127 11/4/2011 11/4/11 1:35 PM
9300127 11/5/2011 11/5/11 5:35 AM
9300127 11/5/2011 11/5/11 1:35 PM
9300127 11/6/2011 11/6/11 5:35 AM
9300127 11/6/2011 11/6/11 1:35 PM
9300127 11/7/2011 11/7/11 5:35 AM
9300127 11/7/2011 11/7/11 1:35 PM
9300127 11/8/2011 11/8/11 5:35 PM
9300127 11/8/2011 11/8/11 1:35 PM
9300127 11/9/2011 11/9/11 5:35 PM
9300127 11/9/2011 11/9/11 1:35 PM
9300127 11/10/2011 11/10/11 5:35 AM
9300127 11/10/2011 11/10/11 1:35 PM
9300127 11/11/2011 11/11/11 5:35 AM
9300127 11/11/2011 11/11/11 1:35 PM
9300127 11/12/2011 11/12/11 5:35 AM
9300127 11/12/2011 11/12/11 1:35 PM
9300127 11/14/2011 11/14/11 5:35 AM
9300127 11/14/2011 11/14/11 1:35 PM
9300127 1/15/2011 11/15/11 5:35 AM
9300127 11/15/2011 11/15/11 1:35 PM

when I run the query the output is:

EMP_NO = 300395
TotalHours = 296:00:00

the output is totally wrong…

I want to happen is, insert data per day, like for example
EMP_NO TotalHours
300395 8
300395 8
300395 8
300395 8
300395 8
300395 8
and so on same also with other employee no

I hope somebody can help me, thank you so much

my advice is, ask your boss to hire someone to help you

doesn’t have to be permanent, doesn’t even have to be on site

What’s wrong in my code?

I need to resolved it …

I hope you can help me…

Thank you so much

Thank you for your help and support…I really appreciated

Until now, I tried to search, to explore my code, try and error but still I did not resolved my issue in getting total hours per days and the most important is getting the rendered, because its one of the important part in payroll system computing the hours of employee…

Thank you for your help…

I tried this query:


INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, UNIX_TIMESTAMP(max(b.DTR)) - UNIX_TIMESTAMP(min(a.DTR))
FROM regular_dtr a
LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO WHERE a.DATE_DTR = b.DATE_DTR GROUP BY a.EMP_NO;

and the output is:

EMP_NO = 300395
TotalHours = 106:56:00
EMP_NO = 9300127
TotalHours = 106::00

Still wrong output :frowning:

Thank you for your bright idea, it works, but a little bit problem, because, I extra data with 00:00:00 output and also two output become 00:00:00 but it should 08:00:00.

Actually, the output will be 24 rows only, but it becomes 26 rows and the 4 rows has 00:00:00 output.

here is my new code and the data output:


INSERT INTO regular_dtr_total(EMP_NO, TotalHours) SELECT a.EMP_NO, TIMEDIFF(max(b.DTR), min(a.DTR))
FROM regular_dtr a
LEFT JOIN regular_dtr b ON a.EMP_NO = b.EMP_NO WHERE b.DATE_DTR = a.DATE_DTR AND a.EMP_NO = b.EMP_NO GROUP BY a.DATE_DTR,  a.EMP_NO;

DTR DATA:

EMP_NO DATE DTR
300395 11/3/2011 11/3/11 5:35 AM
300395 11/3/2011 11/3/11 1:35 PM
300395 11/4/2011 11/4/11 5:35 AM
300395 11/4/2011 11/4/11 1:35 PM
300395 11/5/2011 11/5/11 5:35 AM
300395 11/5/2011 11/5/11 1:35 PM
300395 11/6/2011 11/6/11 5:35 AM
300395 11/6/2011 11/6/11 1:35 PM
300395 11/7/2011 11/7/11 5:35 AM
300395 11/7/2011 11/7/11 1:35 PM
300395 11/8/2011 11/8/11 5:35 AM
300395 11/8/2011 11/8/11 1:35 PM
300395 11/9/2011 11/9/11 5:35 AM
300395 11/9/2011 11/9/11 1:35 PM
300395 11/10/2011 11/10/11 5:35 AM
300395 11/10/2011 11/10/11 1:35 PM
300395 11/11/2011 11/11/11 5:35 AM
300395 11/11/2011 11/11/11 1:35 PM
300395 11/12/2011 11/12/11 5:35 AM
300395 11/12/2011 11/12/11 1:35 PM
300395 11/14/2011 11/14/11 5:35 AM
300395 11/14/2011 11/14/11 1:35 PM
300395 1/15/2011 11/15/11 5:35 AM
300395 11/15/2011 11/15/11 1:35 PM
9300127 11/3/2011 11/3/11 5:35 AM
9300127 11/3/2011 11/3/11 1:35 PM
9300127 11/4/2011 11/4/11 5:35 AM
9300127 11/4/2011 11/4/11 1:35 PM
9300127 11/5/2011 11/5/11 5:35 AM
9300127 11/5/2011 11/5/11 1:35 PM
9300127 11/6/2011 11/6/11 5:35 AM
9300127 11/6/2011 11/6/11 1:35 PM
9300127 11/7/2011 11/7/11 5:35 AM
9300127 11/7/2011 11/7/11 1:35 PM
9300127 11/8/2011 11/8/11 5:35 AM
9300127 11/8/2011 11/8/11 1:35 PM
9300127 11/9/2011 11/9/11 5:35 AM
9300127 11/9/2011 11/9/11 1:35 PM
9300127 11/10/2011 11/10/11 5:35 AM
9300127 11/10/2011 11/10/11 1:35 PM
9300127 11/11/2011 11/11/11 5:35 AM
9300127 11/11/2011 11/11/11 1:35 PM
9300127 11/12/2011 11/12/11 5:35 AM
9300127 11/12/2011 11/12/11 1:35 PM
9300127 11/14/2011 11/14/11 5:35 AM
9300127 11/14/2011 11/14/11 1:35 PM
9300127 1/15/2011 11/15/11 5:35 AM
9300127 11/15/2011 11/15/11 1:35 PM

I have 12days for 300395 and also 12 days for 9300127

And data out in database using this new query is:

300395 00:00:00
9300127 00:00:00
300395 08:00:00
9300127 08:00:00
// 11 output like this, it should be 12 output like this and no output 00:00:00
300395 00:00:00
9300127 00:00:00

total output is 26 rows

thank you so much for your bright idea

Now i resolved my problem in total hours per day…

And now my big problem I need to face is the rendered…Getting only the time between their shifts like 21:35:00 - 05:35:00, 05:35:00 - 13:35:00, and 13:35:00 - 21:35:00…

Rendered should be if he time in late it will be deduct on his time based on his schedule also if he timeout early…

I used case statement in my update query but it did not work…I really don’t know what should I used syntax to solved my old and new problem… the rendered:(

Sorry if until today I did not resolved it…

Thank you for your help…