This content has been marked as final. Show 10 replies
GJ wrote:HOW To Make TUNING request
I have a table like above. I want to write a query to display the result as below.
SQL and PL/SQL FAQ
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.
Edited by: Ashu_Neo on Oct 8, 2012 11:57 AM
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
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.
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
-- Query to generate desired output
SELECT ID, START_DATE, RATE
SELECT CONNECT_BY_ROOT ID ID,
CONNECT_BY_ROOT RATE RATE
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.
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
Thank you for the query. But the query you have provided din't work for me.
Current Table Values
Result based on your query
1||01-Jan-2013||0.6 and so on, basically it starts from 01-Oct-2012 and goes on.
Please let me know how to fix the query to get the desired Output.
Are you sure the query output is not correct ?
If you use a smaller range of dates for the last table row
It would be simpler to check all the output rows of the query. (2012 - 2099 is a very huge range to verify manually)
I agreed with you. I think GJ is not verifying it properly.
We can't check with whole data.
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.
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.
I will try to understand the query as well.