CODEWITHSUNDEEP
sql
Markie Mark
Markie Mark

Reputation: 129

sql how to get first-in of employee between timein timeout

I was trying to get the first-in of employee using SQL query.

Here's what I'm trying to do. Let's say I have this table t:

employee_id | timeinout          
2           | 2019-02-22 02:10:00.000
2           | 2019-02-22 08:30:00.000
2           | 2019-02-22 09:10:00.000
3           | 2019-02-22 08:45:00.000
3           | 2019-02-22 10:30:00.000
3           | 2019-02-22 18:10:00.000

between 6am to 9:15am should be counted as first in, after 9:16 is late.

NOTE: as you can see in the table the time in 2019-02-22 02:10:00.000 doesn't count as first in.

I can get the first in by doing this query.

select employee_id,min(timeinout) as timein, max(timeinout) as timeout
group by employee_id,cast(timeinout as date)


employee_id | timein                  | timeout
2           | 2019-02-22 02:10:00.000 | 2019-02-22 09:10:00.000
3           | 2019-02-22 08:45:00.000 | 2019-02-22 18:10:00.000

how can I get this result:

employee_id | timein                  | timeout
2           | 2019-02-22 08:30:00.000 | 2019-02-22 09:10:00.000
3           | 2019-02-22 08:45:00.000 | 2019-02-22 18:10:00.000

Upvotes: 0

Views: 65

Answers (3)

Barbaros Özhan
Barbaros Özhan

Reputation: 65363

You need to use case..when clause for the min aggregation.

If you're using MySQL, then try to use the following query with str_to_date :

select employee_id,
       min( case when timeinout >= str_to_date('2019-02-22 06:10:00.000','%Y-%m-%d %h:%i:%s') 
                  and timeinout <  str_to_date('2019-02-22 09:16:00.000','%Y-%m-%d %h:%i:%s') 
                 then timeinout end ) as timein, 
       max(timeinout) as timeout 
  from shift 
 group by employee_id,cast(timeinout as date);

employee_id  timein                  timeout
2            2019-02-22 08:30:00     2019-02-22 09:10:00
3            2019-02-22 08:45:00     2019-02-22 18:10:00

Demo

If you're using SQL Server, then try to use the following query with

convert( varchar, @val_date_time, 113 ) :

 select employee_id,
       min( case when timeinout >= convert( varchar, '2019-02-22 06:10:00.000', 113 ) 
                  and timeinout <  convert( varchar, '2019-02-22 09:16:00.000', 113 ) 
            then timeinout end ) as timein, 
        max(timeinout) as timeout 
   from shift
  group by employee_id,cast(timeinout as date);

employee_id  timein                      timeout
2            2019-02-22 08:30:00.000     2019-02-22 09:10:00.000
3            2019-02-22 08:45:00.000     2019-02-22 18:10:00.000

Demo

Upvotes: 2

bimal sharma
bimal sharma

Reputation: 169

I am created dummy table per your descriptions. I am found that following query is giving me result as you want.

SELECT employee_id,min(timeinout) as timein, max(timeinout) as timeout FROM `employee` WHERE timeinout > CAST('6:00:00' AS time) group by `employee_id`

Result is :- 

timein                  timeout               employee_id   
2019-02-22 08:30:00     2019-02-22 09:10:00   2
2019-02-22 08:45:00     2019-02-22 18:10:00   3

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31991

do you find group by employee_id and date

select employee_id,min(timeinout) as timein, max(timeinout) as timeout
from table_name group by employee_id,cast(timeinout as date)

You have to convert timeinout in date and have to use that one in group by

Upvotes: 0

Related Questions