Home » SQL & PL/SQL » SQL & PL/SQL » Help with sql query
| Help with sql query [message #201783] |
Mon, 06 November 2006 13:25  |
jinga
Messages: 116
Registered: January 2003
|
Senior Member |
|
|
I have a table with following strucure.
ITEM EFF_MONTH PRICE END_MONTH
I1 200404 10 200409
I1 200401 15 200403
I would like to write a sql query that makes the above records into like this.
ITEM MTH PRICE
----------------------------
I1 200401 15
I1 200402 15
I1 200403 15
I1 200404 10
I1 200405 10
I1 200406 10
I1 200407 10
I1 200408 10
I1 200409 10
|
|
|
|
| Re: Help with sql query [message #201797 is a reply to message #201783] |
Mon, 06 November 2006 15:54  |
jrich
Messages: 35
Registered: February 2006
|
Member |
|
|
This can be done very easily if you create a new table to hold all your months, for example...
create table dates (mondate char(6));
begin
insert into dates values ('200401');
insert into dates values ('200402');
insert into dates values ('200403');
insert into dates values ('200404');
insert into dates values ('200405');
insert into dates values ('200406');
insert into dates values ('200407');
insert into dates values ('200408');
insert into dates values ('200409');
insert into dates values ('200410');
insert into dates values ('200411');
insert into dates values ('200412');
end;
Now we create your table with the item price history...
create table history (item char(2), eff_month char(6), price number, end_month char(6));
begin
insert into history values ('I1', '200404', 10, '200409');
insert into history values ('I1', '200401', 15, '200403');
end;
And now the following query should give you the output you want...
select h.item, d.mondate, h.price from dates d, history h
where d.mondate >= h.eff_month and d.mondate <= h.end_month
order by mondate
JR
|
|
|
|
|
|
| Re: Help with sql query [message #201834 is a reply to message #201783] |
Tue, 07 November 2006 00:43 |
bala_id
Messages: 27
Registered: July 2005
|
Junior Member |
|
|
Hi Ananthi,
Try the following query.
select Item, eff_month+rnum-1,price from <table_name>,
(select rownum rnum from user_objects where rownum<=12)
where rnum<=end_month-eff_month+1
order by eff_month+rnum-1
If you have any clarifications, please revert back.
Thanks & Best Regards,
Bala
|
|
|
|
| Re: Help with sql query [message #201952 is a reply to message #201834] |
Tue, 07 November 2006 09:33 |
jrich
Messages: 35
Registered: February 2006
|
Member |
|
|
Bala, does your query handle dates that cross year boundries? For example if the following row is added to my example above...
insert into history values ('I1', '200410', 30, '200502');
I don't get the expected results.
JR
|
|
|
|
|
|
| Re: Help with sql query [message #201986 is a reply to message #201955] |
Tue, 07 November 2006 13:55 |
jrich
Messages: 35
Registered: February 2006
|
Member |
|
|
This query does not require the additional date table and handles prices that span years...
select * from
(select item, to_char(add_months(to_date(eff_month, 'YYYYMM'), rn-1),'YYYYMM') month, price from
(select item, eff_month, num_months, price,
row_number() over (partition by item, eff_month order by num_months) rn
from
(select item, eff_month, end_month,
months_between(to_date(end_month, 'YYYYMM'), to_date(eff_month, 'YYYYMM'))+1 as num_months, price
from history) h,
(select rownum from user_objects where rownum < 13)
)
where rn <= num_months
)
order by item, month
Note the number 13 in 'select rownum from user_objects where rownum < 13' limits the number of consecutive months it will print for any one row to one year, ie if a price spans more than 12 months it will not print more than 12. You can raise this number to handle more consecutive months, but be aware a cartesian join is being done against every row from the main table you select against the rownums from user_objects.
JR
|
|
|
|
| Re: Help with sql query [message #202013 is a reply to message #201986] |
Tue, 07 November 2006 20:29 |
 |
ebrian
Messages: 2794
Registered: April 2006
|
Senior Member |
|
|
| jrich wrote on Tue, 07 November 2006 14:55 |
select * from
(select item, to_char(add_months(to_date(eff_month, 'YYYYMM'), rn-1),'YYYYMM') month, price from
(select item, eff_month, num_months, price,
row_number() over (partition by item, eff_month order by num_months) rn
from
(select item, eff_month, end_month,
months_between(to_date(end_month, 'YYYYMM'), to_date(eff_month, 'YYYYMM'))+1 as num_months, price
from history) h,
(select rownum from user_objects where rownum < 13)
)
where rn <= num_months
)
order by item, month
|
jrich, doesn't seem like the query above is capturing all the sequences the OP is looking for ? Are you seeing the same ?
ITEM MONTH PRICE
------ ------ ----------
I1 200401 15
I1 200402 15
I1 200403 15
I1 200404 10
I1 200405 10
I1 200406 10
I1 200407 10
7 rows selected.
Ananthi, the following query should work for you and handles entries that spans different years:
select * from history;
ITEM EFF_MO PRICE END_MO
------ ------ ---------- ------
I1 200401 15 200403
I1 200404 10 200409
I1 200410 20 200502 <-- added row that spans two different years
select item, case when inc_month >= to_date(eff_month, 'rrrrmm')
and inc_month <= to_date(end_month, 'rrrrmm') then to_char(inc_month, 'rrrrmm')
else null end mnth, price
from history, (select add_months(eff_mnth, level - 1) inc_month from (
select min(to_date(eff_month, 'rrrrmm')) eff_mnth,
months_between(max(to_date(end_month, 'rrrrmm')), min(to_date(eff_month, 'rrrrmm'))) btwn from history)
connect by level <= btwn + 1)
where case when inc_month >= to_date(eff_month, 'rrrrmm')
and inc_month <= to_date(end_month, 'rrrrmm') then to_char(inc_month, 'rrrrmm')
else null end is not null
order by 2;
ITEM MNTH PRICE
------ ------ ----------
I1 200401 15
I1 200402 15
I1 200403 15
I1 200404 10
I1 200405 10
I1 200406 10
I1 200407 10
I1 200408 10
I1 200409 10
I1 200410 20
I1 200411 20
I1 200412 20
I1 200501 20
I1 200502 20
14 rows selected. |
|
|
|
| Re: Help with sql query [message #202034 is a reply to message #202013] |
Tue, 07 November 2006 22:47 |
bala_id
Messages: 27
Registered: July 2005
|
Junior Member |
|
|
Hi,
If the eff_month and end_month is more than a year, you can use the following query
select Item,to_char(Add_months(to_date(eff_month,'YYYYMM'),rnum-1),'YYYYMM'),price from test,
(select rownum rnum from user_objects where rownum<=
(select max(months_between(to_date(end_month,'YYYYMM'),to_date(eff_month,'YYYYMM'))+1) from test)
)
where rnum<=months_between(to_date(end_month,'YYYYMM'),to_date(eff_month,'YYYYMM'))+1
order by eff_month+rnum-1
Thanks & Regards,
Bala
|
|
|
|
|
|
| Re: Help with sql query [message #202138 is a reply to message #202034] |
Wed, 08 November 2006 06:04 |
|
ebrian
Messages: 2794
Registered: April 2006
|
Senior Member |
|
|
Nice Bala. It may help to obviate the reliance on the dba_objects table and rewrite the inline view as:
select Item,to_char(Add_months(to_date(eff_month,'YYYYMM'),rnum-1),'YYYYMM'),price
from test, (select rownum rnum from (
select max(months_between(to_date(end_month, 'yyyymm'), to_date(eff_month,'yyyymm'))+1) btwn from test)
connect by rownum <= btwn)
where rnum<=months_between(to_date(end_month,'YYYYMM'),to_date(eff_month,'YYYYMM'))+1
order by eff_month+rnum-1; |
|
|
|
Goto Forum:
Current Time: Mon Dec 02 07:22:36 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
