Home » SQL & PL/SQL » SQL & PL/SQL » Help with sql query
Help with sql query [message #201783] Mon, 06 November 2006 13:25 Go to next message
jinga
Messages: 115
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 Go to previous messageGo to next message
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
icon14.gif  Re: Help with sql query [message #201831 is a reply to message #201783] Tue, 07 November 2006 00:41 Go to previous messageGo to next message
pritika
Messages: 9
Registered: November 2006
Junior Member
Good answer. tested and it works.....
Re: Help with sql query [message #201834 is a reply to message #201783] Tue, 07 November 2006 00:43 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #201955 is a reply to message #201952] Tue, 07 November 2006 10:16 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
Thanks for all the replies.I would like to write the query without creating a table to hold all the months.

Ananthi
Re: Help with sql query [message #201986 is a reply to message #201955] Tue, 07 November 2006 13:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #202059 is a reply to message #202034] Wed, 08 November 2006 01:09 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
@bala_id and jrich: How to format your posts.
Re: Help with sql query [message #202138 is a reply to message #202034] Wed, 08 November 2006 06:04 Go to previous message
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;
Previous Topic: order and order_item trigger to send email
Next Topic: how can I access a package through dblink?
Goto Forum:
  


Current Time: Thu Dec 08 10:05:10 CST 2016

Total time taken to generate the page: 0.12474 seconds