SQL Server Add Delay to a view that joins two tables

brccelvz 于 2023-03-07 发布在其他

关注(0)|答案(2)|浏览(137)

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

sql-server

来源：https://stackoverflow.com/questions/75608401/add-delay-to-a-view-that-joins-two-tables

2条答案

按热度按时间

idfiyjo81#

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())

赞(0）回复(0）举报 2023-03-07

nwo49xxi2#

You could perhaps try using not exists and include the time-delay required as part of the correlation criteria:

select * 
from Table_A a
where not exists (
  select * from Table_B b
    where a.Item = b.Item
    and a.Creation_Date < DateAdd(second, -5, GetDate())
);

赞(0）回复(0）举报 2023-03-07

我来回答

SQL Server Add Delay to a view that joins two tables

2条答案

相关问题

热门标签

最新问答