How to write query with DATEDIFF with different time ranges

116dreamer 26 Reputation points
2022-10-30T07:07:13.94+00:00

Hello!

I am trying to write a query that can have DATEDIFF functions with different time ranges (BETWEEN x AND y). In my data, I have a column called 'tcamAbsenceDetectionCycle' that lists the desired max time range (y). For example, if the value in the column is 8, my y value will be 480000 (in milliseconds). I want to write a query that can read the value from that column and input it into the 'DATEDIFF ... BETWEEN x AND y' function automatically.

I have attached 2 screenshots of my code – one being the original working one with just one DATEDIFF function and another being the code I tried to use but failed to.

I would greatly appreciate any help if anyone knows how to do this. Thank you!

255340-original.png255300-trial.png

Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
339 questions
{count} votes

1 answer

Sort by: Most helpful
  1. KranthiPakala-MSFT 46,432 Reputation points Microsoft Employee
    2022-11-03T18:58:12.163+00:00

    Hello @116dreamer ,

    Thanks for the details.

    As per my discussion with internal team, the bounding ranges (aka wiggle) must be constants, i.e. known at compile time. What that mean is that if the desired wiggle is dynamic (that is dependent on the event data, as in your example) there are couple of ways to address this.

    1. Use the maximum wiggle specified in the join DATEDIFF and put additional logic for the dynamic behavior using CASE into the WHERE clause (or even leave it in the ON, but in addition to the constant wiggle).
    2. When there is a small known set of various wiggles, like it seems to be the case here. It may be beneficial to just write them as separate joins and union results together. In the above example you would create separate steps - S1: filtering on tcam...Cycle = 8 and S2: filtering on tcam...Cycle <> 8, then do separate join using S1 in one case and S2 in another, each with it's own wiggle and then union the results.

    Hope this helps.