Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    9

    Question Unanswered: Grouping by Date Span

    Hi, I'm using Oracle 11.2.0.3.0 and have the following data:

    Code:
    create table hpp
    (member_id varchar2(9),
     enrollment_date date,
     termination_date date);
     
    insert into hpp
    values ('000000001', to_date('01-feb-2013'), to_date('02-apr-2013'));
    
    insert into hpp
    values ('000000001', to_date('23-apr-2013'), to_date('10-aug-2013'));
    
    insert into hpp
    values ('000000001', to_date('11-aug-2013'), to_date('14-oct-2013'));
    
    insert into hpp
    values ('000000001', to_date('15-oct-2013'), to_date('31-dec-2013'));
    
    insert into hpp
    values ('000000002', to_date('01-jan-2013'), to_date('17-jan-2013'));
    
    insert into hpp
    values ('000000002', to_date('01-mar-2013'), to_date('31-jul-2013'));
    
    commit;
    What I would like to do is assign a number for each "consecutive" span of participation for each member. Consecutive is in this case define as not having a break of longer than 14 days between the previous row's termination_date and the current row's enrollment_date.

    The desired output would look like this:


    MEMBER_ID ENROLLMENT_DATE TERMINATION_DATE SPAN_NBR
    000000001 2/1/2013 4/2/2013 1
    000000001 4/23/2013 8/10/2013 2
    000000001 8/11/2013 10/14/2013 2
    000000001 10/15/2013 12/31/2013 2
    000000002 1/1/2013 1/17/2013 1
    000000002 3/1/2013 7/31/2013 2


    Thanks in advance.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about like the following queries?
    Both were constructed based on same algorithm.

    Though, they were not tested on Oracle.

    Example 1:
    Code:
    SELECT member_id
         , enrollment_date
         , termination_date
         , COUNT( CASE
                  WHEN enrollment_date
                     > lag_termination_date + TO_DSINTERVAL('14 00:00:00')
                  THEN 0
                  END
                )
              OVER( PARTITION BY member_id
                        ORDER BY termination_date
                  ) AS span_nbr
     FROM  (SELECT h.*
                 , LAG( termination_date , 1 , DATE '0001-01-01' )
                      OVER( PARTITION BY member_id
                                ORDER BY termination_date
                          ) AS lag_termination_date
             FROM  hpp AS h
           )
    ;

    Example 2:
    Code:
    SELECT h.*
         , COUNT( CASE
                  WHEN enrollment_date
                     > LAG( termination_date , 1 , DATE '0001-01-01' )
                          OVER( PARTITION BY member_id
                                    ORDER BY termination_date
                              ) + TO_DSINTERVAL('14 00:00:00')
                  THEN 0
                  END
                )
              OVER( PARTITION BY member_id
                        ORDER BY termination_date
                  ) AS span_nbr
     FROM  hpp AS h
    ;

  3. #3
    Join Date
    Nov 2010
    Posts
    9
    Example 2 errors out ("window functions are not allowed here"), but Example 1 works great.

    Thank you.

Posting Permissions

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