I want to discuss an important query just now. I have done today it. Suppose, I have a rate_info table like this
| dt | rate |
| 01/01/2011 | 24.50 |
| 5/01/2011 | 23.45 |
| 15/01/2011 | 20.34 |
| 25/01/2011 | 28.22 |
| 10/02/2011 | 15.25 |
Now I have to calculate rate for all dates. The rate will be same until the rate has been changed. Then the query will be like this:
select a.*,(select rate from rate_info where dt=(select max(dt) from rate_info where dt <=a.dt_new))rate_new from
(select to_char((select min(dt) from rate_info)+(level – 1),’DD-MON-RRRR’)dt_new from dual
connect by level <= ((select max(dt) from rate_info) – (select min(dt) from rate_info)+1))a
If a single day contain multiple rate with different version like this:
| dt | rate | Version |
| 01/01/2011 | 24.50 | 1 |
| 5/01/2011 | 23.45 | 1 |
| 15/01/2011 | 20.34 | 1 |
| 25/01/2011 | 28.22 | 1 |
| 10/02/2011 | 15.25 | 1 |
| 10/02/2011 | 13.24 | 2 |
Then the rate with maximum version will be picked up and query will be like this:
select a.*,(select rate from rate_info where dt=(select max(dt) from rate_info where dt <=a.dt_new)
and version =(select max(version) from rate_info where dt=(select max(dt) from rate_info where dt<=a.dt_new )
))rate_new from
(select to_char((select min(dt) from rate_info)+(level – 1),’DD-MON-RRRR’)dt_new from dual
connect by level <= ((select max(dt) from rate_info) – (select min(dt) from rate_info)+1))a
