How to write query with DATEDIFF with different time ranges

Question

How to write query with DATEDIFF with different time ranges

116dreamer 26

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!

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-11-02T22:01:25.787+00:00

Hello @116dreamer ,

Thanks for the question and using MS Q&A platform.

When you say it failed, could you please share the error message that you are seeing?
116dreamer 26 Reputation points

2022-11-03T05:51:13.503+00:00

Hi @KranthiPakala-MSFT !

When I hover over the code, the error I get is:

Syntax errors: Incorrect syntax near BETWEEN. Location: line '198', column '80'.

Line 198 is the line after the CASE clause ie. WHEN ... THEN ...

1 answer

Your answer

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-11-02T22:01:25.787+00:00

Hello @116dreamer ,

Thanks for the question and using MS Q&A platform.

When you say it failed, could you please share the error message that you are seeing?
116dreamer 26 Reputation points

2022-11-03T05:51:13.503+00:00

Hi @KranthiPakala-MSFT !

When I hover over the code, the error I get is:

Syntax errors: Incorrect syntax near BETWEEN. Location: line '198', column '80'.

Line 198 is the line after the CASE clause ie. WHEN ... THEN ...

Answer 1

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

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.

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

116dreamer 26 Reputation points

2022-11-04T08:55:08.72+00:00

Hi @KranthiPakala-MSFT ,

Thank you for the reply!

I understand the 2nd method but I'm confused by the first one since you mention using CASE which I already tried. May I ask for you to write out the correct code using the example I screenshotted above?

Thank you so much.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-11-10T02:51:17.29+00:00
Hello @116dreamer ,

Indeed, CASE doesn't help much here, it is just a logical condition with OR's.

Let say you have a field input1.Detect, which can be 5 or 8. And it defines the desired wiggle range, like 5 means 1 to 100, and 8 means 1 to 300 (let say seconds, but really doesn't matter).

Then the query would look something like this:

SELECT ... FROM (SELECT *, ts=System.Timestamp() FROM input1) AS i1 JOIN (SELECT *, ts=System.Timestamp() FROM input2) AS i2 ON DATEDIFF(second, i1, i2) between 1 and 300 -- the range is the largest from 1-100 and 1-300 WHERE (i1.Detect = 5 AND DATEDIFF(second, i1.ts, i2.ts) BETWEEN 1 AND 100) OR (i1.Detect = 8 AND DATEDIFF(second, i1.ts, i3.ts) BETWEEN 1 AND 300)

Important to note that you need to project the System.Timestamp() per input in a sub-select or a separate step to be used in the DATEDIFF expression, and the condition in WHERE can always be added to the ON condition with an AND.

Hope this clarifies.

Share via

How to write query with DATEDIFF with different time ranges

1 answer

Your answer