0

I have a table in MySQL as following:

+----+--------+------------------+------+  
| id | userid | fecha_ingreso    | tipo |  
+----+--------+------------------+------+  
|  1 | 1      | 2015-06-08 20:00 | 1    |  
|  3 | 1      | 2015-06-09 05:00 | 2    |  
| 18 | 2      | 2015-06-09 23:30 | 1    |  
| 19 | 2      | 2015-06-10 05:00 | 2    |  
| 20 | 2      | 2015-06-10 06:00 | 1    |  
| 21 | 2      | 2015-06-10 09:00 | 2    |  
| 22 | 1      | 2015-06-09 23:30 | 1    |  
| 23 | 1      | 2015-06-10 05:00 | 2    |  
| 24 | 1      | 2015-06-10 06:00 | 1    |  
| 25 | 1      | 2015-06-10 09:00 | 2    |  
+----+--------+------------------+------+  
10 rows in set

This table has information of every user (userid) who into the work and out of the work (fecha_ingreso). For example with userid=1 has enter (tipo=1) at '2015-06-08 20:00' and hes left the job at '2015-06-09 05:00'.

Then I have the userid=2 who is entered (tipo=1) to job at '2015-06-09 23:30' (nighttime) and he left (tipo=2) job for breakfast at '2015-06-10 05:00' and he entered again at '2015-06-10 06:00' and he finally left job at '2015-06-10 09:00'.

I'm not able to make a query that only shows me something like this:

+--------+------+---------------------+---------------------+-----------------+ 
| userid | INS | OUTS | time_after_22pm_and_lessOrEqual6am | 
+--------+------+---------------------+---------------------+-----------------+ 
| 1 | 2015-06-25 15:00:00 | 2015-06-26 23:15:00| 01:15:00 
| 2 | 2015-06-25 23:00:00 | 2015-06-26 13:30:00| 07:00:00 
+--------+------+---------------------+---------------------+-----------------+ 
2 rows in set

This is the output I need. Even when a user can have several ins and outs, I need to show his first entrance datetime and the last out datetime with an aditional column saying how many hours and minutes and seconds hes been at work on range from 22:00 and 6am next day.

Tim, Your query is perfect but it's almost close. However I don't know how to get the hour: minute: secs when a user has been after 22pm (as my sample shows).

Peter O.
  • 32,158
  • 14
  • 82
  • 96
user2570870
  • 15
  • 4

1 Answers1

0

It should be easier to calculate the total working time, assuming that the worker can go work after 08:00pm and work at most 10 hours (you can adjust that):

select userid, time_ins, cnt_start, time_outs, cnt_end, seconds,     TIME_FORMAT(SEC_TO_TIME(seconds),'%H:%i:%s') as night_shift
from (
select userid, time_ins, cnt_start, time_outs, cnt_end, 
(select sum(timestampdiff(second, cnt_start,
                case when fecha_ingreso < a.cnt_start then a.cnt_start  
                    when fecha_ingreso > a.cnt_end then a.cnt_end
                    else fecha_ingreso end )  * case when tipo =1 then -1 else 1 end ) seconds 
    from test_t n
    where n.userid = a.userid and n.fecha_ingreso >= a.time_ins and n.fecha_ingreso <=a.time_outs) seconds
from (
 select userid, time_ins, cnt_start, time_outs , 
 case when cast(time_outs as date) = cast(time_ins as date) then time_outs 
    when cast(time_outs as time)>'06:00:00' then       date_add(date(time_outs), interval 6 hour) end cnt_end
 from (
 select userid, time_enter as time_ins , case when cast(time_enter as time) < '20:00:00' then 
    date_add(date(time_enter), interval 22 hour)
     else time_enter end as cnt_start,
     (select max(fecha_ingreso) from test_t l where l.userid=e.userid and l.tipo=2 and l.fecha_ingreso > e.time_enter 
     and l.fecha_ingreso < date_add(e.time_enter, interval 22 hour) )
    as time_outs
  from (
    select userid, min(fecha_ingreso) as time_enter 
      from test_t 
      where cast(fecha_ingreso as time)>='06:00:00'
      and tipo=1
      group by userid ,cast(fecha_ingreso as date)
    ) e
   ) t
  ) a
) ff;

I didn't optimize it so you can see the steps.

You can try:

select l1.* from l1 
left join (
select l.id, m.id  mid from l1 l 
join l1 m
on l.userid=m.userid and l.tipo=2 and m.tipo=1 and l.fecha_ingreso >=date_add(m.fecha_ingreso, interval -1 hour)
and l.id < m.id) x
on l1.id=x.id or l1.id =x.mid
where mid is null;

it returns 

# id, userid, fecha_ingreso, tipo
'1', '1', '2015-06-08 20:00:00', '1'
'3', '1', '2015-06-09 05:00:00', '2'
'18', '2', '2015-06-09 23:30:00', '1'
'21', '2', '2015-06-10 09:00:00', '2'
'22', '1', '2015-06-09 23:30:00', '1'
'25', '1', '2015-06-10 09:00:00', '2'

by filtering out any 1-2 entries of same users within 1 hour.

Tim3880
  • 2,563
  • 1
  • 11
  • 14
  • it's better you modify your question to include this kind of information. and please also clarify your rule on which entries should be removed. – Tim3880 Jun 24 '15 at 18:29
  • the multiple in/outs crossing middle night makes it more complicated. do you expect a worker stay for more than 12 hours for a single shift? – Tim3880 Jun 24 '15 at 19:46
  • so by changing the 1 hour interval to 3, you get the correct result and now you need only combine the list to the result you need, right? – Tim3880 Jun 24 '15 at 20:01
  • will try later. i don't have the sample data right now since i am using a new machine. it will be helpful if you can use the sqlfiddle to generate some sample data and I think many guys are going to help you. – Tim3880 Jun 24 '15 at 23:55
  • try the new code. if it's not working, i'll create a chat – Tim3880 Jun 25 '15 at 00:29
  • Tim, Seems the code isnt working, take a look at [link] http://sqlfiddle.com/#!9/a54e3/4 .However i think you were misunderstanding my case. I need what is on the link (where i have run 2 queries, your first one that was closest the the right solution vs the last one). I need: userid, timeIn, timeOut, and how many hh:mm:ss a users been if and only if the user pass over 22:00:00. In case a user enter 18:00 and he left 23:00 as he passed over 22:00 he has 01:00:00 as result (on my last column) – user2570870 Jun 25 '15 at 02:29
  • In your sqlfiddle, the user #1 entered at 2015-06-25 15:00:00 and left at 2015-06-26 23:15:00, that's more than 24 hours shift! No credit at all? – Tim3880 Jun 25 '15 at 03:54
  • Tim, Yes you are right. Thats my mistake :( I'm sorry, that was a bad analogy, the correction for this user would be : entered 2015-06-25 15:00:00 and left at 2015-06-25 23:15:00 and he has 1 hour because he's stayed one more after 22:00. I made the mistake because most of my row users work at night time. Being so, is there a way to get what i need? thats the only thing's left – user2570870 Jun 25 '15 at 05:00
  • i think my last answer is close but it's calculating the whole work shift time instead of time between 10:00pm and 6:00am. It can be done but will be very messy using sql. Please make sure your sample online is correct and good enough for representing your real date since no body want to waste their time. It didn't help you post totally different sample here. – Tim3880 Jun 25 '15 at 12:10
  • Tim, i have updated my data test for this: [link]http://sqlfiddle.com/#!9/956e5/3 and i have deliveritly left one row with no enter info because there are sometimes when a user do not register his entrance (not much but happens). BUt your query runs really good. The last column is what i dontknow how to do it. – user2570870 Jun 25 '15 at 12:26
  • your query will not work once you have multi-day data. Try add them for further testing. – Tim3880 Jun 25 '15 at 13:34
  • OK, but how do we solve my problem with the last column? – user2570870 Jun 25 '15 at 13:41
  • i already provide you the idea how to calculate the seconds. but you need solve the multiple-day issue first. – Tim3880 Jun 25 '15 at 13:43
  • Tim, The analogy that i placed at the beginning and the end result is what is needed. Your idea about grouping by date(fecha_ingreso) doesnt help because i need to compute the time when a user enters and leaves (even when he/she makes it several times and its what your query help me do it). For that reason i can do it by the way you told me :( – user2570870 Jun 25 '15 at 16:46
  • you can't just group by userid because if you have multiple days , you need group by userid and the date . so the approach is to find out the 'time_started" for each day of each user, and find out the total seconds in that day, and the last exit of that day, and all the rows in that day, then we can calculate the real "22:00-06:00" shift time. I am working on it but your data is really not good. Check your user #3 (id 9 and 10). enter time is "2015-06-27 20:00:00" and exit time is "2015-06-27 04:15:00"! – Tim3880 Jun 25 '15 at 16:50
  • Yes you are right, it was suppose to be 2015-06-27 20:00:00 and exit time 2015-06-28 04:15 and 06:15:00 hhmmss after 22:00pm because those ours means some bonus for workers. Again was my mistake but what is important is that get me the first and the last time. I have corrected my schema [link]http://sqlfiddle.com/#!9/06a1f/2 please check it out they are real data, and the query it adapts for the expected result as you could see. – user2570870 Jun 25 '15 at 17:26
  • Going to try,but please amend your data and if there is abnormal ones, please specify. More rows from your real records will be helpful. – Tim3880 Jun 25 '15 at 18:37
  • can you add a few more other days' data for those ids? – Tim3880 Jun 25 '15 at 20:09
  • Dearest Tim, When i first run the query i dont see userid=3, i have also added 2 more users with some information and the query is not returning those users. Something must be wrong, Im analizing your logic also. Take a look [link] http://sqlfiddle.com/#!9/82a9c/1 – user2570870 Jun 25 '15 at 23:17
  • The current one expects the shift starts after 6:00 in the morning other wise it doesn't know if the enter is a re-enter of previous day or a new day, we can solve it but the query will get more complicated. – Tim3880 Jun 25 '15 at 23:26
  • Yes seems it is getting complicated. I need the whole results, as the company works 24 hours a day it is important too show who user has been after 22:00 but if he has not time enter it means that theres something wrong. I thought that with our first query we were close maybe it was needing for a UDF that pick time_in and time_out and if the user is been working after 22:00 then calculate hh:mm:ss :( – user2570870 Jun 25 '15 at 23:34
  • I changed the code. even with UDF, you still need know how to tell if an entry is a re-entry of a new day entry.http://sqlfiddle.com/#!9/82a9c/2 – Tim3880 Jun 26 '15 at 01:07
  • Tim, thats the only one the table handles. I dont have any identifier for it. THe query on the sqlfiddle it doenst work. – user2570870 Jun 26 '15 at 07:13
  • There were two typos and I just fix it. http://sqlfiddle.com/#!9/82a9c/6. Good luck. – Tim3880 Jun 26 '15 at 09:31
  • Tim, This last sqlFiddle doesnt calculate hh:mm:ss after 22:00. Finally seems my problem doesnt have any solution right? – user2570870 Jun 27 '15 at 01:23
  • If there is a problem, please specify the exact problem. – Tim3880 Jun 27 '15 at 01:50
  • On the sqlfiddle you sent me, it doesnt calculate the last column. I think im redounding on the same, but i do even like our first query where we could get time In and time Out, even when a user could enter and leave several times. The last thing was left to do is compute when a user stay after 22:00pm (and less or equal to 06:00 next day). I'm concerning about performance (i was checking execution plan also). Im sorry for answering too late but ive been sick since yesterday (on bed) – user2570870 Jun 27 '15 at 02:19
  • please specify which user id is missing the last column? it actually calculate the time but only between 22:00 and 6:00am. this query should be run periodically so you should not run the query on the full year data, mostly you should run it weekly or monthly, then the number of rows should be limited. Even with thousands of workers, you can only expect less than one million records per month. with proper index and time range, i don't think the performance should be a big problem. the first query doesn't work. – Tim3880 Jun 27 '15 at 02:24
  • http://sqlfiddle.com/#!9/82a9c/6 night_shift is 00:00:00 userid=2 it doesnt appears. All those users with night_shift 00:00:00 it should have some value ie userid=1 hes been from June, 23 2015 03:30:00 until June, 23 2015 08:00:00, as he's been since 03:30:00 and 06:00:00, hes been 02:30:00 (on the last column "night_shift") – user2570870 Jun 27 '15 at 02:46
  • check http://sqlfiddle.com/#!9/82a9c/9 for see if we are getting the time_start and time_stop correctly, which is the most important step. With the correct start/stop time, we can calculate the real seconds. – Tim3880 Jun 27 '15 at 04:07
  • try http://sqlfiddle.com/#!9/82a9c/10. user #2 starts from 08:00:00 and stops at 19:30:00. so no credit for him. – Tim3880 Jun 27 '15 at 04:18
  • Tim, seems you are close to end. But icdont know why the last column returns incorrect data. For example for userid=1 it should be 8:00 and not 7:30 because hes been from 22:00 until 6:00. The only one thats right is userid=4. The results for timein and timeouts they all correct. – user2570870 Jun 27 '15 at 12:10
  • User 1 was out for 30 minutes. – Tim3880 Jun 27 '15 at 13:42
  • Tim, this is a good point to consider, they never told me about it. All i know is when they are staying work from 22pm until 06am, they need to know how many hours to give them a bonus per hh:mm:ss. I think they dont care if in that range he/she is outthere. But it's an interesting point to consider. – user2570870 Jun 27 '15 at 14:16
  • I think you described it as a requirement from the beginning. If you don't care whether he/she got out, just subtract the time_start from time_stop. – Tim3880 Jun 27 '15 at 14:28
  • I've tried to change it by time_star and time_stop and it returns 00:00:00 seems it is because of the rule you placed on the subquery. Could you help me with it please? – user2570870 Jun 27 '15 at 15:11
  • For userid=1 it should be 08:00 not 09:30. the calcs arent correct it should compute total amount >=22:00 – user2570870 Jun 27 '15 at 16:01
  • Excellent, thats what i was needing. On monday im gonna test it with real data. Thank you so much Tim – user2570870 Jun 27 '15 at 16:25
  • Tim, Seems there's even a problem here and not sure if i have to test several scenarios to check if the query is working correctly, take a look at userid=7 on here http://sqlfiddle.com/#!9/c4f94/1 – user2570870 Jun 27 '15 at 21:22
  • Just as I said, it's hard to determine if an enter is new day or just another re-entry of previous day. You only need change the 10 hours to 12 hours to solve this case. You may have to adjust it again. http://sqlfiddle.com/#!9/c4f94/2 – Tim3880 Jun 27 '15 at 23:03
  • Yes Tim, seems theres no solution for my case because suppose a user leave today at 09:30am and then his supervisor call him to start to work in the same day but beginning from 21:30. This analogy doesnt addapt on the query. Take a look at http://sqlfiddle.com/#!9/fcda7/1 userid=3 – user2570870 Jun 28 '15 at 00:00
  • If you are sure that the worker will not go out for more than 3 hours, we don't need do the group by. check http://sqlfiddle.com/#!9/fcda7/5. – Tim3880 Jun 28 '15 at 00:27
  • Tim, the query was working really good but some people was complaining because of some hours werent consider at some days, and looking inside i found something weird. Please take a look at this userid=8 (a real scenario) on http://sqlfiddle.com/#!9/25177/1 (i dont have data for 21,22 day). Please help me with some upgrade. – user2570870 Jul 22 '15 at 16:46