4 Replies Latest reply: Dec 28, 2012 12:34 AM by ranit B RSS

    SQL help

    KSS
      Hi Experts,

      I need help in writing a sql for validating table. my scenario is need to find overlapping dates for an account number.

      EX: i have a table with fourcolumns Acc num , Rate, start_date and end_date. I need to find if any account number is active in multiple records ?

      Data:
      Acc num----------Start_date--------------------End_date--------------------Rate*
      1--------------------01-01-2012--------------------01-04-2012--------------------10
      1--------------------01-04-2012--------------------01-06-2012--------------------12
      1--------------------01-05-2012--------------------01-07-2012--------------------13
      1--------------------01-07-2012--------------------99-99-9999--------------------19 ( Enddate is all 9's to represent current record )

      I need a sql to find the overlapping dates, like in above data row 2 n row 3 are overlapping i need a sql to identify these kind of rows in entire table.

      Really appreciate your help!

      Thanks
        • 1. Re: SQL help
          Purvesh K
          KSS wrote:
          Hi Experts,

          I need help in writing a sql for validating table. my scenario is need to find overlapping dates for an account number.

          EX: i have a table with fourcolumns Acc num , Rate, start_date and end_date. I need to find if any account number is active in multiple records ?

          Data:
          Acc num----------Start_date--------------------End_date--------------------Rate*
          1--------------------01-01-2012--------------------01-04-2012--------------------10
          1--------------------01-04-2012--------------------01-06-2012--------------------12
          1--------------------01-05-2012--------------------01-07-2012--------------------13
          1--------------------01-07-2012--------------------99-99-9999--------------------19 ( Enddate is all 9's to represent current record )

          I need a sql to find the overlapping dates, like in above data row 2 n row 3 are overlapping i need a sql to identify these kind of rows in entire table.

          Really appreciate your help!

          Thanks
          I will say the way you re-present the "Current Record" is the worst way of doing so.
          Reason 1. You are storing Dates into a Character field, which implies you have to cast them to Date.
          Reason 2. How do you justify a month to have 99 days and an Year to have 99 Months.

          I chose some random date 31-Dec-4172 to mark as end date.

          Below is a way to achieve the solution:

          I can understand Row 3 being a part of outcome of query since May lies between April and June, but how does Row 2 become an expected output? And if Row 2 is expected, then why not Row 4? Eventually, the Start Date of Row 2 and Row 4 match the End Date of their previous rows, isn't it?
          with data as
          (
            select 1 acc, to_date('01-01-2012', 'DD-MM-YYYY') st_date, to_date('01-04-2012', 'DD-MM-YYYY') ed_date, 10 rate from dual union all
            select 1, to_date('01-04-2012', 'DD-MM-YYYY') st_date, to_date('01-06-2012', 'DD-MM-YYYY') ed_date, 12 rate from dual union all
            select 1, to_date('01-05-2012', 'DD-MM-YYYY') st_date, to_date('01-07-2012', 'DD-MM-YYYY') ed_date, 13 rate from dual union all
            select 1, to_date('01-07-2012', 'DD-MM-YYYY') st_date, to_date('31-12-4172', 'DD-MM-YYYY') ed_date, 19  rate from dual
          )
          select acc, st_date, ed_date, rate
            from (
                  select acc, st_date, ed_date, lead(st_date) over (partition by acc order by st_date) nx_st_date, rate
                    from data
                 ) a
           where a.nx_st_date < a.ed_date and a.nx_st_date >= a.st_date;
          
          ACC                    ST_DATE                   ED_DATE                   RATE                   
          ---------------------- ------------------------- ------------------------- ---------------------- 
          1                      01-Apr-2012               01-Jun-2012               12 
          If you want both the extremes inclusive then:
          with data as
          (
            select 1 acc, to_date('01-01-2012', 'DD-MM-YYYY') st_date, to_date('01-04-2012', 'DD-MM-YYYY') ed_date, 10 rate from dual union all
            select 1, to_date('01-04-2012', 'DD-MM-YYYY') st_date, to_date('01-06-2012', 'DD-MM-YYYY') ed_date, 12 rate from dual union all
            select 1, to_date('01-05-2012', 'DD-MM-YYYY') st_date, to_date('01-07-2012', 'DD-MM-YYYY') ed_date, 13 rate from dual union all
            select 1, to_date('01-07-2012', 'DD-MM-YYYY') st_date, to_date('31-12-4172', 'DD-MM-YYYY') ed_date, 19  rate from dual
          )
          select acc, st_date, ed_date, rate
            from (
                  select acc, st_date, ed_date, lead(st_date) over (partition by acc order by st_date) nx_st_date, rate
                    from data
                 ) a
           where a.nx_st_date between a.st_date and a.ed_date;
          
          ACC                    ST_DATE                   ED_DATE                   RATE                   
          ---------------------- ------------------------- ------------------------- ---------------------- 
          1                      01-Jan-2012               01-Apr-2012               10                     
          1                      01-Apr-2012               01-Jun-2012               12                     
          1                      01-May-2012               01-Jul-2012               13
          • 2. Overlapping Rows
            Frank Kulash
            Hi,
            KSS wrote:
            Hi Experts,

            I need help in writing a sql for validating table. my scenario is need to find overlapping dates for an account number.

            EX: i have a table with fourcolumns Acc num , Rate, start_date and end_date. I need to find if any account number is active in multiple records ?

            Data:
            Acc num----------Start_date--------------------End_date--------------------Rate*
            1--------------------01-01-2012--------------------01-04-2012--------------------10
            1--------------------01-04-2012--------------------01-06-2012--------------------12
            1--------------------01-05-2012--------------------01-07-2012--------------------13
            1--------------------01-07-2012--------------------99-99-9999--------------------19 ( Enddate is all 9's to represent current record )
            Are you saying that start_date and end_date are strings, that can contain a value of '99-99-999'? Storing date information in anything but a DATE (or TIMESTAMP) column is not a very good idea. If that's what you're doing, then use TO_DATE to convert them to DATEs (and convert '99-99-9999' to December 31, 9999).
            I need a sql to find the overlapping dates, like in above data row 2 n row 3 are overlapping i need a sql to identify these kind of rows in entire table.
            One way to do that is a self-join:
            SELECT    e.acc_num
            ,       e.start_date
            ,       e.end_date
            ,       l.start_date
            ,       l.end_date
            -- include whatever columns you want in the SELECT clause
            FROM       table_x  e
            JOIN       table_x  l  ON   e.acc_num     = l.acc_num
                              AND  e.start_date     < l.end_date
                            AND  e.end_date     > l.start_date
                            AND  e.ROWID     < l.ROWID
            ORDER BY  e.acc_num
            ,            e.start_date
            ;
             

            I hope this answers your question.
            If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and the results you want from that data.
            Explain, using specific examples, how you get those results from that data.
            Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
            See the forum FAQ {message:id=9360002}
            • 3. Re: SQL help
              NSK2KSN
              SELECT acc_num,
                     start_date,
                     end_date,
                     rate
                FROM (WITH t1
                           AS (SELECT 1 acc_num,
                                      '01-01-2012' start_date,
                                      '04-01-2012' end_date,
                                      10 rate
                                 FROM DUAL
                               UNION ALL
                               SELECT 1,
                                      '04-01-2012',
                                      '06-01-2012',
                                      12
                                 FROM DUAL
                               UNION ALL
                               SELECT 1,
                                      '05-01-2012',
                                      '07-01-2012',
                                      12
                                 FROM DUAL
                               UNION ALL
                               SELECT 1,
                                      '07-01-2012',
                                      '99-99-9999',
                                      12
                                 FROM DUAL)
                      SELECT acc_num,
                             start_date,
                             DECODE (end_date, '99-99-9999', NULL, end_date) end_date,
                             LEAD (start_date, 1) OVER (ORDER BY start_date) lead_date,
                             LAG (end_date, 1) OVER (ORDER BY start_date) lag_date,
                             rate
                        FROM t1)
               WHERE (     TO_DATE (lead_date, 'dd-mm-yyyy')
                         - TO_DATE (end_date, 'dd-mm-yyyy') <> 0
                      OR   TO_DATE (lag_date, 'dd-mm-yyyy')
                         - TO_DATE (start_date, 'dd-mm-yyyy') <> 0);
              • 4. Re: SQL help
                ranit B
                Please try this... if helps...
                with xx as
                (
                  select 1 acc, to_date('01-01-2012', 'DD-MM-YYYY') st_date, to_date('01-04-2012', 'DD-MM-YYYY') ed_date, 10 rate from dual union all
                  select 1, to_date('01-04-2012', 'DD-MM-YYYY') st_date, to_date('01-06-2012', 'DD-MM-YYYY') ed_date, 12 rate from dual union all
                  select 1, to_date('01-05-2012', 'DD-MM-YYYY') st_date, to_date('01-07-2012', 'DD-MM-YYYY') ed_date, 13 rate from dual union all
                  select 1, to_date('01-07-2012', 'DD-MM-YYYY') st_date, to_date('31-12-4172', 'DD-MM-YYYY') ed_date, 19  rate from dual
                ), xx_order as (
                    select * from xx
                    order by st_date,ed_date
                ), xx_result as(
                    select acc, st_date, ed_date, rate,  LEAD(st_date) over(order by st_date,ed_date) next_date
                    from xx_order
                )
                select acc,st_date, ed_date, rate from xx_result 
                where ed_date > next_date;
                Ranit B.