Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2014
    Posts
    3

    Unanswered: merge the timelines into single timeline for the group?.

    Hi,
    I have a file with the following columns. it has split timelines.as per process I will load this into a table and merge these continuous timelines. could you please give me some idea?. I'm using LAG and LEAD functions to identify the continuous timelines. but it only works for 2 rows. what I'm doing here is check the end date of the first row and compare that with start date of next row. if the difference is 1 then I know it is continuous timeline. my objective is to prepare a another alias column(same date) for each group of timelines. and then apply the aggregate OLAP functions on top of it to merge it. however, it is not working for 3rd row. also, the subsequent gap timelines for the same key combination. I'm looking for help here. Thank you.

    Key1,Key2,start_dt,end_dt
    A,20,2016-01-01,2016-03-31
    A,20,2016-04-01,2016-06-30
    A,20,2016-07-01,2016-07-31
    B,30,2016-08-01,2016-08-15
    A,20,2017-01-01,2017-03-31
    A,20,2017-04-01,2017-06-30
    A,20,2017-07-01,2017-07-31

    Code:
    CREATE TABLE employees.test (
        key1 VARCHAR(10),
        key2 VARCHAR(10),
        start_date DATE,
        end_dt DATE
    );
    
    insert into employees.test values('A','20','2016-01-01','2016-03-31');
    insert into employees.test values('A','20','2016-04-01','2016-06-30');
    insert into employees.test values('A','20','2016-07-01','2016-07-31');
    insert into employees.test values('B','30','2016-08-01','2016-08-15');
    insert into employees.test values('A','20','2017-01-01','2017-03-31');
    insert into employees.test values('A','20','2017-04-01','2017-06-30');
    insert into employees.test values('A','20','2017-07-01','2017-07-31');
    
    
    with 
    cte as (
        select key1, key2, start_date, end_dt
        from test
        union all
        select t.key1, t.key2, cte.start_date, t.end_dt
        from cte
        join test t on cte.key1 = t.key1 and cte.key2 = t.key2 and cte.end_dt = (t.start_date - 1)
    ), 
    cte2 as (
        select *, rn = row_number() over (partition by key1, key2, end_dt order by start_date)
        from cte
    )
    select key1, key2, start_date, max(end_dt) end_dt
    from cte2
    where rn=1
    group by key1, key2, start_date
    order by key1, key2, start_date;
    Result:-
    Key1,Key2,start_dt,end_dt
    A,20,2016-01-01,2016-07-31
    B,30,2016-08-01,2016-08-15
    A,20,2017-01-01,2017-07-31
    Last edited by scope123; 09-27-17 at 09:19. Reason: adding the source code

  2. #2
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    306
    Provided Answers: 46
    Hi,

    Try this:
    Code:
    with a as (
    select t.*
    , sum(
      case when start_dt=coalesce(lag(end_dt, 1) over(partition by key1, key2 order by start_dt) + 1 day, start_dt) then 0 else 1 end
    ) over (partition by key1, key2 order by start_dt) grp
    from employees.test t
    )
    select key1, key2, min(start_dt) start_dt, max(end_dt) end_dt
    from a
    group by key1, key2, grp;
    Regards,
    Mark.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •