Azure Stream Analytics Detecting Absence of Events

Question

I have written code to detect an absence of event within the next 9 minutes of the previous one. Attached is the code I have written that works and gives me my desired results. My question is: Why is it when I write 'BETWEEN 0 AND 540' instead, the code no longer works and I don't get any results? I am curious to know why I can't start at '0' and have to start at '1'. Thank you!

Answer 1

Hello @116dreamer ,
Thanks for the question and using MS Q&A platform.
As we understand the ask here is why intrducing the between 0 and 540 gives you a no rcord , please do let us know if its not accurate.

The datadiff function behaves very similar to what it does in SQL and when i did played with SQL ( I know its a ASA question , but should behave in the same way ) , This is a what i tried .

CREATE TABLE ROWDATASETEP
(
ID INT ,
JOBTIME DATETIME
)

INSERT INTO Rowdatasetep (id ,Jobtime) values ( 1,'2022-10-16 16:52:59.367')
INSERT INTO Rowdatasetep (id ,Jobtime) values ( 1,'2022-10-16 16:52:59.367')
INSERT INTO Rowdatasetep (id ,Jobtime) values ( 1,'2022-10-16 16:53:59.367')
INSERT INTO Rowdatasetep (id ,Jobtime) values ( 1,'2022-10-16 16:54:59.367')

select DATEDIFF(second, t1.Jobtime,t2.Jobtime),* from Rowdatasetep t1
left outer join Rowdatasetep t2
on t1.id = t2.id
and DATEDIFF(second, t2.Jobtime,t1.Jobtime) between 0 and 540
and t2.id is null

I suggest that you try to to add DATEDIFF(second, t2.Jobtime,t1.Jobtime) in the SELECT statement that then run the query with without

and t2.id is null

This will give you a better idea of the data which is coming . Please revert back if you do not find a resolution , I will try to work with ASA and see whats happening .
Please do let me if you have any queries.
Thanks
Himanshu

---

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
  • If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators