The situation is, I have two fields in a database (or that can be derived) - when the message was received (
Received, of type
DateTime), and the day of the month (but not the month itself) that the message was supposed to be received (
DayOfRun of type
Integer, which is the last two digits of an ID column that doesn't otherwise contain date-time information). I need to know the difference (in whole days, ignoring the time) between the two. If the message arrived before the day of run, the number will be negative.
I can guarantee that the entries will not differ by more than half a month in either direction.
The pseudo-code logic I have come up with is as follows:
DayOfRun = Extract last two characters from ID Field DayReceived = Extract DayOfMonth from Received; DaysInPreviousMonth = Extract Count of Days in (Month before Received) Pick the closest to 0 from the following: A: If DayReceived < DayOfRun then : DaysInPreviousMonth + DayReceived - DayOfRun ; : Otherwise : DaysInPreviousMonth + DayOfRun - DayReceived ; B: DayReceived - DayOfRun;
|Day of Run||Received||Answer||Reasoning|
|01||2021-04-29||-2||29th April is 2 days before 1st May (can't be 1st April - would be too far away)|
|28||2021-05-02||4||02nd May is 4 days after 28th April (which gives a smaller absolute number than assuming 28th May, which would be too far away anyway)|
|14||2021-04-28||14||28th April is 14 days after 14th April (which gives a smaller absolute number than if we assumed 14th May)|
My KQL to implement this logic is as follows:
tableName | project DayOfRun = toint(substring(['ID Field'], -2, 2)), ['Day Of Month Received'] = datetime_part("day",ErrorTimestamp), ErrorTimestamp | where DayOfRun <> ['Day Of Month Received'] | project ['Days In Previous Month'] = datetime_part("day",endofmonth( datetime_add("month",-1, ErrorTimestamp))), DayOfRun, ['Day Of Month Received'] | project ['Day Of Month Received'], Diff = (['Day Of Month Received'] - DayOfRun), CrossMonthDiff = iif(['Day Of Month Received'] < DayOfRun,(['Days In Previous Month']+['Day Of Month Received']-DayOfRun),(['Days In Previous Month']+DayOfRun-['Day Of Month Received'])) | summarize ['Day Of Month Received'], ['Difference (Days)'] = iif(min_of(abs(Diff),abs(CrossMonthDiff)) == abs(Diff),Diff,CrossMonthDiff) | order by ['Difference (Days)'] desc, ['Day Of Month Received'] asc
This query seems overly complex, and is quite slow to run. Any thoughts on how to improve either the logic or the query itself?