10 Replies Latest reply: Oct 10, 2012 3:17 AM by GJ RSS

    How to write a Query for the mentioned scenario.

    GJ
      Hi All,

      Table A

      ID|| Start_Date||End_date||Rate
      1||01-Jan-2011||31-Mar-2011||0.8
      1||01-Apr-2011||31-Jun-2011||0.9


      I have a table like above. I want to write a query to display the result as below.

      ID|| Start_Date||Rate
      1||01-Jan-2011||0.8
      1||01-Feb-2011||0.8
      1||01-Mar-2011||0.8
      1||01-Apr-2011||0.9
      1||01-May-2011||0.9
      1||01-Jun-2011||0.9

      Kindly help.

      Thanks!
      GJ
        • 1. Re: How to write a Query for the mentioned scenario.
          sb92075
          GJ wrote:
          Hi All,

          Table A

          ID|| Start_Date||End_date||Rate
          1||01-Jan-2011||31-Mar-2011||0.8
          1||01-Apr-2011||31-Jun-2011||0.9


          I have a table like above. I want to write a query to display the result as below.

          ID|| Start_Date||Rate
          1||01-Jan-2011||0.8
          1||01-Feb-2011||0.8
          1||01-Mar-2011||0.8
          1||01-Apr-2011||0.9
          1||01-May-2011||0.9
          1||01-Jun-2011||0.9

          Kindly help.

          Thanks!
          GJ
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: How to write a Query for the mentioned scenario.
            Ashu_Neo
            Try to read link mentioned by SB. It will make you more interactive to share your problems. And immediate reply too from experts.

            Check your solution below.
            SQL> ed
            Wrote file afiedt.buf
            
              1  WITH data1 AS
              2  (
              3  SELECT 1 id, TO_DATE('01-Jan-2011' , 'DD-Mon-YYYY') stdt,TO_DATE('31-Mar-2011' , 'DD-Mon-YYYY') endt, 0.8 rate FROM dual
              4  UNION ALL
              5  SELECT 1 id, TO_DATE('01-Apr-2011' , 'DD-Mon-YYYY') stdt,TO_DATE('30-Jun-2011' , 'DD-Mon-YYYY') endt, 0.9 rate FROM dual
              6  )
              7  SELECT id, ADD_MONTHS(stdt, level -1) st_dt, rate FROM data1
              8  CONNECT BY  level <= ROUND(MONTHS_BETWEEN(endt,stdt))
              9  AND rate= prior rate  /* stick to current line */
             10* AND prior sys_guid() IS NOT NULL  /* used to terminate the connect by loop */
            SQL> /
            
                    ID ST_DT           RATE
            ---------- --------- ----------
                     1 01-JAN-11         .8
                     1 01-FEB-11         .8
                     1 01-MAR-11         .8
                     1 01-APR-11         .9
                     1 01-MAY-11         .9
                     1 01-JUN-11         .9
            
            6 rows selected.
            Thanks!
            Ashutosh

            Edited by: Ashu_Neo on Oct 8, 2012 11:57 AM
            • 3. Re: How to write a Query for the mentioned scenario.
              Ravetd
              Hi,

              A solution (but 11g required, otherwise subquery CAL have to be write without recursivity) :
                  WITH data1 AS
                  (
                  SELECT 1 id, TO_DATE('01-Jan-2011' , 'DD-Mon-YYYY') stdt,TO_DATE('31-Mar-2011' , 'DD-Mon-YYYY') endt, 0.8 rate FROM dual
                  UNION ALL
                  SELECT 1 id, TO_DATE('01-Apr-2011' , 'DD-Mon-YYYY') stdt,TO_DATE('30-Jun-2011' , 'DD-Mon-YYYY') endt, 0.9 rate FROM dual
                  )
                  , CAL(x)  -- Here is your calendar
                  As
                  (
                   Select TO_DATE('01-Jan-2011' , 'DD-Mon-YYYY') From Dual
                   Union All
                   Select Add_Months(x, 1) From Cal
                   Where x <= TO_DATE('30-Jun-2011' , 'DD-Mon-YYYY')     
                  ) 
                  Select id
                  , c.x
                  , Last_Value(rate Ignore Nulls) Over (Partition By id Order BY c.x) as rate
                  From data1 d
                  Partition By ( id )  Right Outer Join Cal c
                    On ( d.stdt = c.x )
              
              ID           X                    RATE
              1     01/01/2011     0,8
              1     01/02/2011     0,8
              1     01/03/2011     0,8
              1     01/04/2011     0,9
              1     01/05/2011     0,9
              1     01/06/2011     0,9
              1     01/07/2011     0,9
              Regards
              • 4. Re: How to write a Query for the mentioned scenario.
                GJ
                Thank you all for the suggestions. But My data doesn't stop with 2 rows for a ID, that is, an ID can have more than 2 rows. An ID can have 2 or more rates with different start and end date. So I can not hard code the values in the Query, it should dynamically take from the Table.

                Kindly provide some suggestions for this.
                • 5. Re: How to write a Query for the mentioned scenario.
                  966744
                  Hello,

                  I think as per your requirement below solution will work perfectly -

                  -- for test data i am creating the table A which will contain the data
                  -- Table Script

                  CREATE TABLE A
                  (
                       ID          NUMBER,
                       START_DATE     DATE,
                       END_DATE     DATE,
                       RATE          NUMBER
                  );

                  -- Query to generate desired output

                  SELECT ID, START_DATE, RATE
                  FROM
                  (
                  SELECT CONNECT_BY_ROOT ID ID,
                  I_MONTH START_DATE,
                  CONNECT_BY_ROOT RATE RATE
                  FROM
                  (
                  SELECT A.*, B.I_MONTH FROM A,
                  (SELECT ADD_MONTHS('01-JAN-2011',ROWNUM-1) I_MONTH FROM DUAL CONNECT BY LEVEL <7)B
                  WHERE TRUNC(A.START_DATE(+),'MM')= B.I_MONTH
                  ORDER BY B.I_MONTH
                  )C CONNECT BY NOCYCLE I_MONTH BETWEEN PRIOR START_DATE AND PRIOR END_DATE
                  ) WHERE RATE IS NOT NULL;


                  Please revert if you fill any drawback in this query.

                  Regards,
                  Rahul Tiwari
                  • 6. Re: How to write a Query for the mentioned scenario.
                    Ashu_Neo
                    Hi GJ,

                    Did you try ever, what was there in solution and how I can use it in my query ??? I guess, you never thought of that.

                    See, here in OTN, we used to provide sample data with 2/3 records with a table structure and insert statements or by using WITH clause. So that, at least somebody can get an idea to develop a query assuming your real data would be same as you are provided.

                    So the query(check below) will work for you. No need to use with clause and remove temp data table name and your original table columns.
                    SELECT id, ADD_MONTHS(stdt, level -1) st_dt, rate FROM use_your_table /* commented data1 */
                      CONNECT BY  level <= ROUND(MONTHS_BETWEEN(endt,stdt))
                       AND rate= prior rate  /* stick to current line */
                       AND prior sys_guid() IS NOT NULL 
                    Thanks!
                    • 7. Re: How to write a Query for the mentioned scenario.
                      GJ
                      Hi Ashu,

                      Thank you for the query. But the query you have provided din't work for me.

                      Current Table Values
                      ID||Start_Date||End_Date||Rate
                      1||01-Jan-1984||01-Jun-2012||0.8
                      1||01-Jun-2012||01-Oct-2012||0.8
                      1||01-Oct-2012||31-Dec-2099||0.6

                      Result based on your query
                      ID||Start_Date||Rate
                      1||01-Oct-2012||0.6
                      1||01-Nov-2012||0.6
                      1||01-Dec-2012||0.6
                      1||01-Jan-2013||0.6 and so on, basically it starts from 01-Oct-2012 and goes on.


                      Expected Result
                      ID||Start_Date||Rate
                      1||01-Jan-1984||0.8
                      1||01-Feb-1984||0.8
                      .
                      .
                      1||01-Jun-2012||0.8
                      1||01-Jul-2012||0.8
                      .
                      .
                      1||01-Oct-2012||0.6
                      1||01-Nov-2012||0.6
                      .
                      .

                      Please let me know how to fix the query to get the desired Output.

                      Thanks!
                      • 8. Re: How to write a Query for the mentioned scenario.
                        Maya
                        Are you sure the query output is not correct ?
                        If you use a smaller range of dates for the last table row
                        1||01-Oct-2012||31-Dec-2099||0.6

                        It would be simpler to check all the output rows of the query. (2012 - 2099 is a very huge range to verify manually)
                        • 9. Re: How to write a Query for the mentioned scenario.
                          Ashu_Neo
                          Maya,
                          I agreed with you. I think GJ is not verifying it properly.

                          We can't check with whole data.

                          GJ,
                          Take some more sample data to a test table and run the I provided you against test table. And try to understand the query logic.
                          Then you can do slight changes to the query, if you find somewhere data is wrong. As I won't find anything wrong with this.

                          Thanks!
                          • 10. Re: How to write a Query for the mentioned scenario.
                            GJ
                            Ashu/Maya,

                            The query is starting from 01-Oct-2012 which is itself is wrong. If you see my sample date I have 3 date range for a single ID. Basically I want the query result to start from 01-Jan-1984.

                            Table Values
                            ID||Start_Date||End_Date||Rate
                            1||01-Jan-1984||01-Jun-2012||0.8
                            1||01-Jun-2012||01-Oct-2012||0.8
                            1||01-Oct-2012||31-Dec-2099||0.6

                            I will try to understand the query as well.