lundi 4 juillet 2016

Identify contiguous and discontinuous date ranges

I have a table named x . The data is as follows.

Acccount_num    start_dt    end_dt
A111326      02/01/2016    02/11/2016
A111326      02/12/2016    03/05/2016
A111326      03/02/2016   03/16/2016
A111331      02/28/2016   02/29/2016
A111331      02/29/2016   03/29/2016
A999999      08/25/2015   08/25/2015
A999999      12/19/2015   12/22/2015
A222222      11/06/2015   11/10/2015
A222222      05/16/2016   05/17/2016

Both A111326 and A111331 should be identified as contiguous data and A999999 and
A222222 should be identified as discontinuous data.In my code I currently use the following query to identify discontinuous data. The A111326 is also erroneously identified as discontinuous data. Please help to modify the below code so that A111326 is not identified as discontinuous data.Thanks in advance for your help.

(SELECT account_num
                FROM (SELECT account_num,
                             (MAX (
                                 END_DT)
                              OVER (PARTITION BY account_num
                                    ORDER BY START_DT))
                                START_DT,
                             (LEAD (
                                 START_DT)
                              OVER (PARTITION BY account_num
                                    ORDER BY START_DT))
                                END_DT
                        FROM x
                         WHERE (START_DT + 1) <=
                                (END_DT - 1))
               WHERE START_DT < END_DT);

Aucun commentaire:

Enregistrer un commentaire