1 2 Previous Next 16 Replies Latest reply: Feb 28, 2013 9:07 AM by Mr Lonely RSS

    Range Date to Individual date.

    Mr Lonely
      Hi Legends,

      I have one requirement. I have done it through PL/ SQL but it's taking too long to process that. Can you please suggest whether this can be done through SQL only?

      Table:
      CREATE TABLE test_lonely
        ( db_id NUMBER, start_date DATE, end_date DATE
        );
      *1st Requirement:*
      Data Set 1:
      insert into test_lonely values ( 1, '01-JAN-2013', '03-JAN-2013');
      This should be changed to
      1, '01-JAN-2013'
      1,'02-JAN-2013'
      1,'03-JAN-2013'
      *2nd Requirement:*
      Data Set 2:
      insert into test_lonely values ( 1, '01-JAN-2013', null);
      This should be changed to
      1, '01-JAN-2013'
      *3rd Requirement:*
      Data Set 3:
      insert into test_lonely values ( 1, null, '04-JAN-2013');
      This should be changed to
      1, '04-JAN-2013'
      *4th Requirement:*
      Data Set 3:
      insert into test_lonely values ( 1, null,null);
      This should be changed to
      1, null
      Hope my requirement is clear.

      added
       tag properly :D                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
        • 1. Re: Range Date to Individual date.
          Peter vd Zwan
          hi

          For 1st Requirement:
          with test_lonely as
          (select 1 db_id, date '2013-01-01' start_date, date '2013-01-03' end_date from dual
          )
          
          select
            db_id
            ,start_date + level -1
          
          from
            test_lonely
          
          connect by
            level <= end_date - start_date + 1
          ;
          For 2nd Requirement:
          with test_lonely as
          (select 1 db_id, date '2013-01-01' start_date,null end_date from dual
          )
          
          select
            db_id
            ,start_date
          
          from
            test_lonely
          ;
          For 3rd Requirement:
          with test_lonely as
          (select 1 db_id,null start_date, date '2013-01-04' end_date from dual
          )
          
          select
            db_id
            ,end_date
          
          from
            test_lonely
          ;
          For 4th Requirement:
          with test_lonely as
          (select 1 db_id,null start_date, null end_date from dual
          )
          
          select
            db_id
            ,end_date
          
          from
            test_lonely
          ;
          Regards,

          Peter

          Edited by: Peter vd Zwan on Feb 28, 2013 4:58 AM
          • 2. Re: Range Date to Individual date.
            theoa
            Try something like this
            sql> with range as (select to_date('01-01-2013','dd-mm-yyyy') as from_date
              2                 ,      to_date('03-01-2013','dd-mm-yyyy') as till_date
              3                 from   dual
              4                )
              5  select nvl(r.from_date, r.till_date) + level-1
              6  from range r
              7  connect by nvl(from_date, till_date) + level-1 <= nvl(till_date, from_date);
            
            NVL(R.FRO
            ---------
            01-JAN-13
            02-JAN-13
            03-JAN-13
            • 3. Re: Range Date to Individual date.
              Frank Kulash
              Hi,

              Here's one way:
              WITH   got_n_times     AS
              (
                   SELECT     db_id, start_date, end_date
                   ,     CASE
                            WHEN  LEAST    (start_date, end_date) IS NOT NULL
                            THEN  GREATEST (start_date, end_date)
                                      + 1
                                - LEAST        (start_date, end_date)
                            ELSE  1
                        END     AS n_times
                   FROM    test_lonely
              )
              ,     got_max_n_times     AS
              (
                   SELECT  MAX (n_times)     AS max_n_times
                   FROM     got_n_times
              )
              ,     cntr          AS
              (
                   SELECT     LEVEL     AS n
                   FROM     got_max_n_times
                   CONNECT BY     LEVEL     <= max_n_times
              )
              SELECT       t.db_id
              ,       CASE 
                         WHEN  c.n = 1
                         THEN  NVL (t.start_date, t.end_date)
                         ELSE  LEAST (t.start_date, t.end_date) + c.n
                                                              - 1
                     END     AS a_date
              FROM       got_n_times  t
              JOIN       cntr            c  ON  c.n <= t.n_times
              ;
              Most of the complexity is needed only to handle multiple rows.
              The query abpve does produces N rows when start_date is N days after end_date. That's easy to change if you want only 1 row of output in that case.
              • 4. Re: Range Date to Individual date.
                Mr Lonely
                I think I did it.

                Will it do?
                select db_id, nvl(start_date,end_date) + level-1
                from test_lonely
                connect by level <=(end_date - start_date)+1;
                I mean any possible change of getting error?

                Oops .. so many replies when I was replying to the thread. Will go through them one by one.,

                Edited by: Mr Lonely on Feb 28, 2013 6:33 PM
                • 5. Re: Range Date to Individual date.
                  887479
                  Mr Lonely wrote:
                  I think I did it.

                  Will it do?
                  select db_id, nvl(start_date,end_date) + level-1
                  from test_lonely
                  connect by level <=(end_date - start_date)+1;
                  I mean any possible change of getting error?

                  Oops .. so many replies when I was replying to the thread. Will go through them one by one.,

                  Edited by: Mr Lonely on Feb 28, 2013 6:33 PM
                  Will fail, if your table has multiple rows..

                  Edited by: 884476 on Feb 28, 2013 5:04 AM
                  • 6. Re: Range Date to Individual date.
                    theoa
                    Null values will not work as you specified.
                    Change it to this:
                    select r.db_id, nvl(r.start_date, r.end_date) + level-1
                    from test_lonely r
                    connect by nvl(start_date, end_date) + level-1 <= nvl(end_date, start_date);
                    • 7. Re: Range Date to Individual date.
                      Frank Kulash
                      Hi,
                      Mr Lonely wrote:
                      I think I did it.

                      Will it do?
                      select db_id, nvl(start_date,end_date) + level-1
                      from test_lonely
                      connect by level <=(end_date - start_date)+1;
                      I mean any possible change of getting error?
                      That assumes test_lonely has only 1 row.
                      • 8. Re: Range Date to Individual date.
                        Mr Lonely
                        You are right :(

                        I was testing with only one row.
                        But I don't understand . If it's working with only one row then why it's not working with multiple rows?
                        • 9. Re: Range Date to Individual date.
                          Mr Lonely
                          even this is not working when I have multiple rows :(.
                          • 10. Re: Range Date to Individual date.
                            theoa
                            Mr Lonely wrote:
                            even this is not working when I have multiple rows :(.
                            Nope, I overlooked that little detail too. ;-)
                            • 11. Re: Range Date to Individual date.
                              Frank Kulash
                              Hi,
                              Mr Lonely wrote:
                              You are right :(

                              I was testing with only one row.
                              But I don't understand . If it's working with only one row then why it's not working with multiple rows?
                              It will work with any number of rows, but I don't think it will do what you want.

                              When the only CONNECT BY condition is <tt> LEVEL &lt;= x </tt> , then every row will be considered the parent of every row. So if you have 2 rows in the table, there iwll 2 rows at LEVEL=1, 4 rows at LEVEL=2, 8 rows at LEVEL=3, ..., and 2 ^N^ rows at LEVEL=N. Furthermore, each row will be multiplied the same number of times. If I undersand the problem correctly, you want some rows mutlplied 3 times, and others only 1time with the given sample data.

                              If your real table has more than 1 row, why not post some sample data with more than 1 row? Wouldn't it be jst as easy to post 1 sample data set of 4 rows:
                              insert into test_lonely values ( 1, '01-JAN-2013', '03-JAN-2013');
                              insert into test_lonely values ( 2, '01-JAN-2013', NULL);
                              insert into test_lonely values ( 3, NULL,        '04-JAN-2013');
                              insert into test_lonely values ( 4, NULL,         NULL);
                              as it was to post 4 sets of 1 row each?
                              • 12. Re: Range Date to Individual date.
                                Mr Lonely
                                awesome.

                                I got it now. Thank you. But it took more than 10 minutes for me just to understand what sample query you have written. It's working as desired.
                                • 13. Re: Range Date to Individual date.
                                  jeneesh
                                  Mr Lonely wrote:
                                  You are right :(

                                  I was testing with only one row.
                                  But I don't understand . If it's working with only one row then why it's not working with multiple rows?
                                  Because in the connect by loop you are not defining the relation - that the same row has to be manipulated..

                                  You can make it work
                                  select nvl(start_date,end_date)+level-1 dt
                                  from test_lonely
                                  connect by level <=nvl((end_date - start_date),0)+1
                                  and prior rowid = rowid
                                  and prior sys_guid() is not null;
                                  Or, If your DB_ID is unique, then
                                  select *
                                  from test_lonely;
                                  
                                  DB_ID START_DATE END_DATE
                                  ----- ---------- ---------
                                      1 01-JAN-13  03-JAN-13 
                                      2 01-JAN-13            
                                      3            04-JAN-13 
                                      4                      
                                  
                                  
                                  select t.*,nvl(start_date,end_date)+level-1 dt
                                  from test_lonely t
                                  connect by level <=nvl((end_date - start_date),0)+1
                                  and prior db_id = db_id
                                  and prior sys_guid() is not null;
                                  
                                  DB_ID START_DATE END_DATE  DT      
                                  ----- ---------- --------- ---------
                                      1 01-JAN-13  03-JAN-13 01-JAN-13 
                                      1 01-JAN-13  03-JAN-13 02-JAN-13 
                                      1 01-JAN-13  03-JAN-13 03-JAN-13 
                                      2 01-JAN-13            01-JAN-13 
                                      3            04-JAN-13 04-JAN-13 
                                      4                                
                                  
                                   6 rows selected 
                                  Edited by: jeneesh on Feb 28, 2013 6:50 PM
                                  • 14. Re: Range Date to Individual date.
                                    Mr Lonely
                                    oh my god. I think this one is awesome.

                                    One little help needed in understanding thorugh.
                                    and prior db_id = db_id
                                    and prior sys_guid() is not null;
                                    My DB_ID is not unique.

                                    Can you please explain little bit more about this one?

                                    Edited by: Mr Lonely on Feb 28, 2013 6:55 PM
                                    1 2 Previous Next