Add Delay to a view that joins two tables

Question

I have to create a view that basically queries table A, and ensures that the same item exists in table B, so if the item from table A does not exist in table B, a record is created in my view, and an external procedure triggers an alert. The problem that I have is that the record in table B is created sometimes, some seconds after the record in table A was created, and it trigger sometimes false alarms. So what I need is to add to the view creation some kind of delay in the view that waits at least 5 seconds to create the record if item in table A does not exist in table B before triggering the alert.

so if I have:

Table_A

Item	Creation_Date
XY	00:00:01

Table_B

Item	Creation_Date
XY	00:00:03

And I use this query to create a view in sql server:

select * from Table_A A
where A.Item not in (
select B.Item
from Table_B B)
where A.Creation_Date >= DATEADD(minute, -10, GETDATE())
and B.Creation_Date >= DATEADD(minute, -10, GETDATE())

If the alert procedure runs at 00:00:02 it will create the record in the view, and it will trigger the alarm.

How can I do to avoid this and to wait for some seconds to ensure that the record in Table_B is definitely not created?

Thanks

Answer 1

You could just add another inequality condition to the where clause that excludes very recent records:

select * 
from Table_A A
where 
    A.Item not in (select B.Item from Table_B B)
    and A.Creation_Date >= DATEADD(minute, -10, GETDATE())
    and A.Creation_Date <  DATEADD(second, -10, GETDATE())

Depending on how frequently your procedure runs, you might need to adjust the other bound as well:

. . .
and A.Creation_Date >= DATEADD(second, -10, DATEADD(minute, -10, GETDATE()))
and A.Creation_Date <  DATEADD(second, -10, GETDATE())