CODEWITHSUNDEEP
sqlsql-serversql-server-2008
VAAA
VAAA

Reputation: 15039

How to identify consecutive times with gap less than X seconds

I have a set of time stamps (SQL 2012) with a type I or O (input or output) like the sample data below:

enter image description here

I need to set the "status" column to INACTIVE to those consecutive times of the same type (Input or Output) where the gap between them is less than 120 seconds. The first time stamp in that consecutive group won't get any status.

In the image you can see how should it work.

I have about 80,0000 records that I have to analyze and set INACTIVE flag where the condition is matched. That should be done in a Stored Procedure.

UPDATE HERE IS THE SQL SCRIPT THAT CAN BE USED FOR TESTING

http://sqlfiddle.com/#!6/9c9e7/2

Upvotes: 2

Views: 305

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You should be able to do this readily with lag() and some logic. The select form of the query is:

select t.*,
       (case when datediff(second,
                           lag(TimeStamp) over (partition by EntranceType Order by TimeStamp),
                           TimeStamp) < 120
             then 'InActive'
        end) as NewColumnStatus
from timestamp_01 t;

If you want to do an update, you can use an updatable CTE:

with toupdate as (
      select t.*,
             (case when datediff(second,
                                 lag(TimeStamp) over (partition by EntranceType Order by TimeStamp),
                                 TimeStamp) < 120
                   then 'InActive'
              end) as NewColumnStatus
      from timestamp_01 t
     )
update toupdate
    set Status = NewColumnStatus;

EDIT:

The employee_id should be easy to add:

with toupdate as (
      select t.*,
             (case when datediff(second,
                                 lag(TimeStamp) over (partition by employee_id, EntranceType Order by TimeStamp),
                                 TimeStamp) < 120
                   then 'InActive'
              end) as NewColumnStatus
      from timestamp_01 t
     )
update toupdate
    set Status = NewColumnStatus;

Upvotes: 1

Related Questions