'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?
- so if the emp_feed message was yesterday, we should have a SWIPE_OFF today
- if the emp_feed was Monday, we should have a SWIPE_OFF Tuesday and today
- 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 |
|---|
