select / update SQL records that has a timestamp difference of more then 30 days-CodePudding

I need to select or update records from badge-records that have a date difference of more than 30 days after the last visit. A select query to find them is ok, so I can update them. Difficult to explain in detail but I'll try with an example: (This is an access system where people scan a badge and the timestamp is recorded.) I only need to know the records when a badge has entered the system more than 30 days after the previous scan, the very first scan. The example table is showing the records I need from the table (i need 5 records)

Only records of the same badge number must be compared and updated.

Is this possible using TSQL ?

Example:

 ------------------ -------------- 
| TimeStamp        | Badge        |
 ------------------ -------------- 
| 19-10-2022 10:18 | Badge1       | <--- **select** (more the 30 days after previous scan)
| 01-01-2022 12:18 | Badge1       | <--- ok (less then 30 days)
| 08-12-2021 13:23 | Badge1       | <--- ok (less then 30 days)
| 20-11-2021 11:18 | Badge1       | <--- ok (less then 30 days)
| 22-10-2021 13:18 | Badge1       | <--- **select** (more the 30 days after previous scan)
| 23-08-2020 14:18 | Badge1       | <--- **select** (first entrance)
| 01-01-2022 09:18 | Badge12      | <--- ok (less then 30 days)
| 02-12-2021 10:18 | Badge12      | <--- **select** (more the 30 days after previous scan)
| 29-10-2021 23:18 | Badge12      | <--- ok (less then 30 days)
| 25-10-2021 12:18 | Badge12      | <--- **select** (first entrance)
 ------------------ --------- ----

use this fiddle to have the example db and my wrong answer https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=c1528618004f0fe6bb6319e8e638abae

CodePudding user response：

Help others help you. Post a script that contains DDL and sample data that can be used as the basis for writing code.

with cte as (
   select *, ROW_NUMBER() over (partition by Badge order by Timestamp) as rno
   from @x 
)
select cte.*, prior.rno as prno, datediff(day, prior.TimeStamp, cte.Timestamp) as ddif
from cte
left join cte as prior on cte.badge = prior.badge and cte.rno - 1 = prior.rno
where cte.rno = 1 or datediff(day, prior.TimeStamp, cte.Timestamp) > 30
order by cte.Badge, cte.TimeStamp;

This should work but I have no way of testing on 2008. fiddle to demonstrate. Comment out the WHERE clause to see the all the rows and the columns that are computed for the query logic. This uses ROW_NUMBER to generate a sequence number and then simply self joins using that value to simulate LAG.

CodePudding user response：

updated fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=a24d23f54030d7aadd8f889819cd4512

;WITH Ordered AS (
    SELECT *
         , ROW_NUMBER() OVER (PARTITION BY Badge ORDER BY  CONVERT(DATETIME, [scandate] ,103) DESC) rn
    FROM History 
) 
SELECT M.*, DATEDIFF(dd,  p.[scandate],m.[scandate]) DaysGap
FROM Ordered M
LEFT JOIN  Ordered P 
ON M.rn = P.rn-1 
AND M.Badge = P.Badge 
WHERE P.rn IS NULL -- first entrance
OR DATEDIFF(dd,  p.[scandate],m.[scandate]) > 30