CODEWITHSUNDEEP
sql-servergaps-and-islands
Asuna
Asuna

Reputation: 1

SQL Query to get in/out time

I need to create a report when the user entering and exiting time. So far I only manage to get the min and max time. Here, the example of table:

ID   | Flag_Location (bit) | Time
----------------------------
1001 | 1                   | 8:00
1001 | 1                   | 9:00
1001 | 1                   | 10:00
1001 | 0                   | 11:00
1001 | 0                   | 12:00
1001 | 1                   | 13:00
1001 | 1                   | 14:00

The output that I need for the report is like this : 

ID   | ENTERTIME | EXITTIME
-------------------------
1001 | 8:00      | 10:00
1001 | 13:00     | 14:00

So far I only manage to get 1 row of result :

ID   | ENTERTIME | EXITTIME
-------------------------
1001 | 8:00      | 14:00

Upvotes: 0

Views: 216

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37460

Try below query (explanations in code)

declare @tbl table (ID int, Flag_Location bit, Time varchar(5));
insert into @tbl values
(1001,1,'8:00'),
(1001,1,'9:00'),
(1001,1,'10:00'),
(1001,0,'11:00'),
(1001,0,'12:00'),
(1001,1,'13:00'),
(1001,1,'14:00');


select ID,
       cast(max(ts) as varchar(10)), 
       cast(min(ts) as varchar(10))
from (
    select ID, ts, Flag_Location,
           row_number() over (order by ts) -
           row_number() over (partition by Flag_Location order by ts) grp
    from (
        select *,
               -- add 0 at the beginning for correct cast and cast it to timestamp for correct ordering
               cast(right('00000' + time, 5) as timestamp) ts
        from @tbl
    ) a
) a where Flag_Location = 1
group by ID, grp

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

You can just bucket the to identify group by and do group by as below:

;with cte as (select *, bucket = sum(case when flag_location = 0 then 1 when flag_location = 1 and nextflag = 0 then 2  else 0 end) over (partition by id order by [time]),
    [time] as endtime from
    (
        select *,
         lag(flag_location) over(partition by id order by [time]) nextflag
        from #table4
    ) a
)
select id, min([time]), max([time]) from cte
where flag_location = 1
group by id, bucket

Query results:

+------+------------------+------------------+
|  id  |    Entertime     |     ExitTime     |
+------+------------------+------------------+
| 1001 | 08:00:00.0000000 | 10:00:00.0000000 |
| 1001 | 13:00:00.0000000 | 14:00:00.0000000 |
+------+------------------+------------------+

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81990

You can use the window function to create an ad-hoc Grp

Example

Select ID
      ,TimeIn = min(Time)
      ,TimeOut = max(Time)
 From  (
        Select *
              ,Grp = sum(case when flag_location=0 then 1 else 0 end ) over (partition by id order by time)
         From YourTable
       ) A
 Where Flag_Location=1
 Group By ID,Grp

Returns

ID      TimeIn              TimeOut
1001    08:00:00.0000000    10:00:00.0000000
1001    13:00:00.0000000    14:00:00.0000000

If it helps with the visualization, the nested query generates the following:

enter image description here

Upvotes: 3

Related Questions