OraQA

The following two SQL patterns can be used to find the last consecutive date sequence for
each customer. The last consecutive date sequence contains the last billing_month date and any other consecutive months that are exactly one month apart from each other.

CREATE TABLE cust
(
customer_no number(10) NOT NULL,
billing_month date NOT NULL
);

INSERT INTO cust VALUES (1, to_date('01-JAN-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (1, to_date('01-FEB-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (1, to_date('01-APR-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (1, to_date('01-MAY-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (1, to_date('01-JUN-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (1, to_date('01-AUG-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (1, to_date('01-SEP-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (1, to_date('01-OCT-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (1, to_date('01-NOV-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-JAN-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-FEB-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-APR-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-MAY-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-JUN-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-JUL-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-AUG-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-SEP-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (2, to_date('01-OCT-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (3, to_date('01-MAY-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (3, to_date('01-JUN-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (3, to_date('01-JUL-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (3, to_date('01-AUG-2003', 'DD-MON-YYYY'));
INSERT INTO cust VALUES (3, to_date('01-OCT-2003', 'DD-MON-YYYY'));
commit;
SQL>  SELECT customer_no, billing_month
      FROM  cust order by customer_no, billing_month desc;

CUSTOMER_NO BILLING_M
----------- ---------
          1 01-NOV-03
          1 01-OCT-03
          1 01-SEP-03
          1 01-AUG-03 **July is missing, so the sequence is from Aug to Nov
          1 01-JUN-03
          1 01-MAY-03
          1 01-APR-03
          1 01-FEB-03
          1 01-JAN-03
          2 01-OCT-03
          2 01-SEP-03
          2 01-AUG-03
          2 01-JUL-03
          2 01-JUN-03
          2 01-MAY-03
          2 01-APR-03  **March is missing, so the sequence is from April to Oct
          2 01-FEB-03
          2 01-JAN-03
          3 01-OCT-03  **Sep is missing, so the sequence is October only.
          3 01-AUG-03
          3 01-JUL-03
          3 01-JUN-03
          3 01-MAY-03

23 rows selected.

——————–Solution one——————-

SELECT customer_no, consec_month
FROM ( SELECT customer_no, consec_month
       FROM
(SELECT customer_no, billing_month,
        row_number() OVER (PARTITION BY customer_no ORDER BY billing_month DESC) rn
   FROM cust)
MODEL
PARTITION BY (customer_no )
DIMENSION BY (rn as indexs)
MEASURES( CAST( NULL AS DATE) consec_month,billing_month billing_month, rn as rn)
RULES
(
consec_month[ANY] ORDER BY indexs= CASE WHEN billing_month[CV()-1] IS NULL OR
            billing_month[1] = add_months(billing_month[CV()],rn[CV()]-1)
     THEN billing_month[CV()] END
)
)
WHERE consec_month  IS NOT NULL
ORDER BY customer_no, consec_month  DESC;

CUSTOMER_NO CONSEC_MO
----------- ---------
          1 01-NOV-03
          1 01-OCT-03
          1 01-SEP-03
          1 01-AUG-03
          2 01-OCT-03
          2 01-SEP-03
          2 01-AUG-03
          2 01-JUL-03
          2 01-JUN-03
          2 01-MAY-03
          2 01-APR-03
          3 01-OCT-03                                                          

12 rows selected.

————————Solution two———————

SELECT customer_no,
      to_date(SUBSTR(path,
                     INSTR(path,'#',1,LEVEL) + 1,
                     INSTR(path,'#',1,LEVEL+1) -
                     INSTR(path,'#',1,LEVEL) -1),'DD-MM-YYYY') Last_consec_date
FROM
(SELECT customer_no, max_path, path_len, path||'#' path
   FROM
   (SELECT customer_no, path,last_date, billing_month ,
           MAX(length(REGEXP_REPLACE(path,'[^#]',''))) OVER (PARTITION BY customer_no) max_path,
           length(REGEXP_REPLACE(path, '[^#]','')) path_len
     FROM
  (SELECT customer_no,path,  billing_month,
             MAX(TO_DATE(SUBSTR(path,instr(path,'#',-1) + 1) ,
              'DD-MM-YYYY')) OVER (PARTITION BY customer_no) last_date
       FROM
    (
        SELECT customer_no, billing_month,
            sys_connect_by_path(to_char(billing_month,'DD-MM-YYYY'),'#') path
          FROM   cust
       CONNECT BY PRIOR customer_no = customer_no
       AND add_months(PRIOR billing_month, 1) = billing_month
       )
     )
     WHERE last_date = billing_month
   )
   WHERE max_path = path_len
 )
 CONNECT BY PRIOR customer_no = customer_no
 AND INSTR (path, '#', 1, LEVEL+1) > 0
 AND PRIOR dbms_random.string ('P', 10) IS NOT NULL
 ORDER BY customer_no, Last_consec_date DESC;

CUSTOMER_NO LAST_CONS
----------- ---------
          1 01-NOV-03
          1 01-OCT-03
          1 01-SEP-03
          1 01-AUG-03
          2 01-OCT-03
          2 01-SEP-03
          2 01-AUG-03
          2 01-JUL-03
          2 01-JUN-03
          2 01-MAY-03
          2 01-APR-03
          3 01-OCT-03                                                          

12 rows selected.

Filed under SQL. Follow any responses to this entry through this RSS 2.0 feed.