'infinite loop for Recursively checking SQL tables for missed out data

I have 2 table with me, one is emp_feed which has the details where its records employee swipe details. I have another table emp_event table where it just record employees swipe card one time activity. If the employee has not swiped their card for more than 24 hours till their last swipe an alert gets generated which send the entry to emp_event table as SWIPE_OFF. If this continues for 7 days then it started showing on the system. Problem is some SWIPE_OFF record from emp_event table are not getting updated. Now we need to find out the missing SWIPE_OFF

Emp_feed Table

Emp_no swipe_timestamp swipe_message
7675 2022-05-11 09:00:00 logged in
7675 2022-05-11 13:00:00 cafe entry
7675 2022-05-11 14:00:00 cafe exist
7675 2022-05-11 17:00:00 logged out

Emp_event table

Emp_no swipe_timestamp swipe_message
7675 2022-05-11 09:00:00 logged in
7675 2022-05-11 17:00:00 logged out
7675 2022-05-12 10:00:01 SWIPE_OFF
7675 2022-05-13 10:00:01 SWIPE_OFF
7675 2022-05-15 10:00:01 SWIPE_OFF

How could i check the result recursively?

  1. so if the emp_feed message was yesterday, we should have a SWIPE_OFF today
  2. if the emp_feed was Monday, we should have a SWIPE_OFF Tuesday and today
  3. if the last emp_feed was Sunday, we should have a SWIPE_OFF monday, tuesday and today and so forth

The query I have written so far is returning the employee number that has missing entry. I am not an expert in SQL so just trying to find a lead:

 with R as ( 
 Select  distinct emp_id as r_emp_id, getdate() as date1, 1 as n from emp_Feed
 union all
 Select r.r_emp_id, getdate()-r.n, r.n+1
 from R  join emp_Feed lf on r_emp_id = lf.emp_id
         join emp_Event ef on r_emp_id=ef.vin
  where r.n<=6  and 
 not exists(Select 1 from emp_Event where r_emp_id=ef.vin and ef.swipe_timestamp=(getdate()-r.n)) and
 not exists(Select 1 from emp_Feed where r_emp_id=lf.emp_id and lf.swipe_timestamp=(getdate()-r.n))
 
 )

 Select * from R
 OPTION (MAXRECURSION 0)

The Recusion is goin on infinite loop



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source