Post reply

Find max availability interval durations

  • June 3, 2021 at 1:19 am

    Go to Answer

    #3891260

    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 (
    BATCH_IDint
    ,AVAILABLEbit
    ,CURR_TSdatetime
    );
    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

    /*
    BATCH_IDAVAILABLE_MINUTES
    1235
    2346
    */

    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
    , max(datediff(minute, START_INTERVAL, END_INTERVAL)) [AVAILABLE_MINUTES]
    from START_END
    group by BATCH_ID

    Any help would be much appreciated

  • June 3, 2021 at 2:32 am

    Answer

    #3891271

    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

    with 
    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;
    BATCH_IDmax_diff_minutes
    1235
    2346

    • This reply was modified 2 years, 11 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

  • June 3, 2021 at 5:08 pm

    #3891549

    Thanks for clear explanation it worked

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

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