azurekql

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)
];

Solution

  • 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