CODEWITHSUNDEEP
sqlsql-server
Verta
Verta

Reputation: 11

Select min of time that got group by status and need more than 1 values

I want to find min time of shift worker but some days worker have 2 shifts.

Table t:

|     ID   |   date     |  time    |  status  |
| -------- | ---------- | -------- | -------- |           
|     1    | 2022-01-01 | 08:00:00 | Shift In | 
|     1    | 2022-01-01 | 08:15:00 | Shift In |
|     1    | 2022-01-01 | 10:30:00 | Shift Out|
|     1    | 2022-01-01 | 12:15:00 | Shift In |
|     1    | 2022-01-01 | 12:18:00 | Shift In |
|     1    | 2022-01-01 | 14:52:00 | Shift Out|
|     1    | 2022-01-01 | 15:00:00 | Shift Out|
|     2    | 2022-01-01 | 17:15:00 | Shift In |
|     2    | 2022-01-01 | 18:15:00 | Shift Out|
|     2    | 2022-01-01 | 18:18:00 | Shift Out|

Output I need:

|     ID   |   date     |  time    |  status  |
| -------- | ---------- | -------- | -------- |           
|     1    | 2022-01-01 | 08:00:00 | Shift In | 
|     1    | 2022-01-01 | 10:30:00 | Shift Out|
|     1    | 2022-01-01 | 12:15:00 | Shift In |
|     1    | 2022-01-01 | 14:52:00 | Shift Out|
|     2    | 2022-01-01 | 17:15:00 | Shift In |
|     2    | 2022-01-01 | 18:15:00 | Shift Out|

I need min of time for each shift. When I try output always shows 1 of each employee so I have no idea.

I try to find solutions for return output of min time of shift worker that work more than 1 time in a day on SQL or PL/SQL but I still no idea.

Upvotes: 0

Views: 72

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

It seems you just want to list all Shift In and Shift Out rows, but there are some redundant rows, such as Shift In at 8:30 when there is already a row for Shift In at 8:00. You want to remove the redundant rows and only keep the first row of such recurring statuses.

Use LAG to see the status of the previous row.

select id, date, time, status
from
(
  select
    t.*,
    lag(status) over (partition by id order by date, time) as prev_status
  from mytable t
)
where prev_status is null or prev_status <> status
order by id, date, time;

Upvotes: 2

Related Questions