Azure Question posted in
The official documentation to get you started can be found here.

Azure – Calculate the number of overlapping days for each device using KQL

How can I calculate the number of overlapping days for each device in the given table, considering the time periods (StartDate to EndDate) for each device and check if and how much they overlap.
The query needs to return the total number of overlapping days for each DeviceID.

Datatable (Devices) with some sample data.

let Devices = datatable(DeviceID:string, StartDate:datetime, EndDate:datetime)
[
    "a1", datetime(2024-01-01), datetime(2024-05-03),
    "a1", datetime(2024-02-12), datetime(2024-02-18),
    "b1", datetime(2024-06-13), datetime(2024-07-07),
    "b1", datetime(2024-07-08), datetime(2024-07-08),
    "c1", datetime(2024-08-23), datetime(2024-10-10),
    "c1", datetime(2024-09-01), datetime(2024-10-07)
];
Tags:

2

Answers


  1. 0

    Calculate the number of overlapping days for each device using KQL

    You can do that using below KQL Query, where I have table your input into table Rith_Table:

    Rith_Table
| join kind = inner (Rith_Table) on DeviceID
| where StartDate != StartDate1 or EndDate != EndDate1 
| where StartDate < EndDate1 and StartDate1 < EndDate
| extend test_start = iif(StartDate > StartDate1, StartDate, StartDate1), test_end = iif(EndDate < EndDate1, EndDate, EndDate1)
| where test_start <= test_end 
| extend rith = datetime_diff('day', test_end, test_start) + 1 
| summarize Dev_Ovlaping_days = sum(rith)/2 by DeviceID
| join kind = fullouter (Rith_Table|distinct DeviceID) on DeviceID
| extend Dev_Ovlaping_days = iif(isempty(Dev_Ovlaping_days),"0", tostring(Dev_Ovlaping_days))
| project-away DeviceID
| project-rename DeviceID=DeviceID1

    Output:

    enter image description here

    Fiddle.

    Login or Signup to reply.
  2. 0

    Arriving late to the party.
    There are much simpler, generic and performant solutions for your question.

    The solution below will tackle any number of periods per device (and not just two, as in your example).
    No need for joins.

    In this solution we track the change points (where period starts or ended).
    Cumulative sum of the changes gives us the number of periods for each segment.

    This solution is suitable for 2 periods (per device).
    It can be twitched to match any number of periods.
    Please feel free

    let Devices = datatable(DeviceID:string, StartDate:datetime, EndDate:datetime)
[
    "a1", datetime(2024-01-01), datetime(2024-05-03),
    "a1", datetime(2024-02-12), datetime(2024-02-18),
    "b1", datetime(2024-06-13), datetime(2024-07-07),
    "b1", datetime(2024-07-08), datetime(2024-07-08),
    "c1", datetime(2024-08-23), datetime(2024-10-10),
    "c1", datetime(2024-09-01), datetime(2024-10-07)
];
Devices
| mv-expand delta = dynamic([1, -1]) to typeof(int)
           ,day = pack_array(StartDate, EndDate + 1d) to typeof(datetime)
| summarize delta = sum(delta) by DeviceID, day
| partition hint.strategy=native by DeviceID 
  (
    order by day asc 
    | extend cumsum = row_cumsum(delta), next_day = next(day)
  )
| summarize OverlappingDays = sumif(next_day - day, cumsum >= 2) / 1d by DeviceID
| order by DeviceID asc
    DeviceID OverlappingDays
    a1 7
    b1 0
    c1 37
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search