Find max availability interval durations

  • I have a series of availability status / timestamp pairs per batch, the task is to get the max duration of uninterrupted availability in minutes per each batch.

    Here is a sample data

    declare @T table (
    insert into @T(BATCH_ID,AVAILABLE,CURR_TS) values
    (123,0,'20150203 12:25')
    ,(123,1,'20150203 12:33')
    ,(123,1,'20150203 12:36')
    ,(123,0,'20150203 12:38')
    ,(123,1,'20150203 12:40')
    ,(123,0,'20150203 12:43')

    ,(234,0,'20150203 12:33')
    ,(234,1,'20150203 12:35')
    ,(234,0,'20150203 12:41')

    The data per each batch should start and end as not available, if this info is helpful

    My desired output is


    I tried to use windowing functions, but I don't understand them and it is not working, here is my attempt which gives wrong results:

    ;with START_END as
    select T.*
    , first_value(T.CURR_TS) over (partition by T.BATCH_ID,T.AVAILABLE order by T.CURR_TS) [START_INTERVAL]
    , last_value(T.CURR_TS) over (partition by T.BATCH_ID,T.AVAILABLE order by T.CURR_TS) [END_INTERVAL]
    from @T T

    select BATCH_ID
    from START_END
    group by BATCH_ID

    Any help would be much appreciated

  • In the code below 'start_cte' creates a column called 'start_ind' which is equal to 1 when the AVAILABLE column has changed from 0 to 1 else it is 0.  To create uninterrupted ranges ('ts_range') of availability the 'start_ind' column is SUM'ed OVER.  Then the 'ts_range' column is used to determine the maximum availability interval in minutes for each BATCH_ID.  Something like this

    start_cte(BATCH_ID, AVAILABLE, CURR_TS, start_ind) as (
    select *, case when AVAILABLE=1 and (lag(AVAILABLE) over (partition by BATCH_ID order by CURR_TS))=0
    then 1 else 0 end
    from @t),
    ranges_cte(BATCH_ID, AVAILABLE, CURR_TS, start_ind, ts_range) as (
    select *, sum(start_ind) over (partition by BATCH_ID order by CURR_TS)
    from start_cte),
    start_end_cte(BATCH_ID, ts_range, start_ts, end_ts) as (
    select BATCH_ID, ts_range, min(CURR_TS), max(CURR_TS)
    from ranges_cte
    where ts_range>0
    group by BATCH_ID, ts_range)
    select BATCH_ID, max(datediff(minute, start_ts, end_ts)) max_diff_minutes
    from start_end_cte
    group by BATCH_ID;

    • This reply was modified 1 year, 2 months ago by  Steve Collins. Reason: Changed stop_ind to start_ind to make clearer

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • Thanks for clear explanation it worked

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply