Skip to content
Advertisement

Calculate time difference between two records and insert into another table if difference is greater than 30 seconds

I have the following table

Tran_DeviceAttRec table:

Emp_id  Card_Number Dev_Id  Dev_Direction   Punch_RawDate           sno_id    
    1       1           1   IN              2021-02-01 16:52:26.000 331
    2       2           1   IN              2021-02-01 16:52:48.000 332
    2       2           2   OUT             2021-02-01 16:52:54.000 333
    3       3           1   IN              2021-02-01 16:58:01.000 334
    4       4           1   IN              2021-02-01 16:58:46.000 335
    3       3           2   OUT             2021-02-01 16:59:02.000 336
    4       4           2   OUT             2021-02-01 18:25:00.000 338
    1       1           2   OUT             2021-02-01 18:26:00.000 339

I want to select and insert only those fields which time difference is more than 30 second into temp table for example if emp_id 2 punchdate is 2021-02-01 16:52:48.000 and Dev_direction is IN and the same emp_id 2 punchdate is 2021-02-01 16:52:54.000 and Dev_direction is OUT then it should not select and insert value to temp table

TempTable:

Emp_id  Card_Number Dev_Id  Dev_Direction   Punch_RawDate           sno_id
1       1           2       OUT             2021-02-01 18:26:00.000 339
3       3           2       OUT             2021-02-01 16:59:02.000 336
4       4           2       OUT             2021-02-01 18:25:00.000 338

I am using SQL Server 2014. I tried following queries to find the time but I don’t know how to calculate the difference between time

SELECT TOP 1 Emp_id, Punch_RawDate
FROM Tran_DeviceAttRec
where Dev_Direction = 'OUT'
ORDER BY sno_id DESC

SELECT TOP 1 Emp_id, Punch_RawDate
from Tran_DeviceAttRec
where Dev_Direction = 'IN' and Emp_id = 1
order by sno_id desc

Advertisement

Answer

I want to select and insert only those fields which time difference is more than 30 second into temp table for example

Do you just want lag()?

select dar.*
from (select dar.*,
             lag(Punch_RawDate) over (partition by emp_id order by Punch_RawDate) as prev_Punch_RawDate
      from Tran_DeviceAttRec dar
     ) dar
where dar.Punch_RawDate > dateadd(second, 30, prev_Punch_RawDate);

Your code and some of the explanation mention other conditions. To be honest, I don’t follow them and this seems to produce what you want.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement