Join by Range?? 1

[DugsDMan]

Is there a way to join two tables, in such a way that values from one table can be properly associated with another, when one of the fields in a join will not match all the time?

Here's my scenario:

I have 2 tables, one has account information, the other has rate information.

The account table is updated each month after the billing cycle and has information on a customers account. An example is:

AccNumber UsageType  BillDate
123          1         2/1/04
123          1         3/1/04
123          1         4/1/04
123          1         5/1/04
234          2         2/1/04

The rate table is only updated when the rates change. An example of the table is:

UsageType  EffectiveDate   Rate
1            1/1/04        1.00
1            4/1/04        1.50
2            1/1/04        0.75
2            4/1/04        1.00

I would like to figure out a way to join the 2 tables, so that the rate that was in effect at the time of the bill is pulled in. But, I can't directly join the EffectiveDate with the BillDate, since I don't have entries for 2/1/04 and 3/1/04. Is there a way to join these two tables so that the rate will be pulled in when the BillDate is between the EffectiveDates?

Something like this:

AccNumber UsageType  BillDate   Rate
123          1         2/1/04   1.00
123          1         3/1/04   1.00
123          1         4/1/04   1.50
123          1         5/1/04   1.50
234          2         2/1/04   0.75

Thanks for any help you have!!!

 

[SantaMufasa]

Dugs,

There are MANY ways to solve this, but I like the following because of its simplicity: a user-defined function that you invoke to produce your results:

Section 1 -- User-defined "GET_RATE" function:

create or replace function get_rate (UsageIn number, DateIn date) return number is
	rate_hold	number;
begin
	select rate into rate_hold from rate
		where (UsageType,EffectiveDate) in
			(select UsageType,max(EffectiveDate) from rate
			  where EffectiveDate <= DateIN
			    and UsageIn = UsageType
			  group by UsageType);
	return rate_hold;
end;
/

Section 2 -- Query to produce your results via the "GET_RATE" function (using your posted data):

col a heading "AccNumber" format 999
col b heading "UsageType" format 99
col c heading "BillDate" format a8
col d heading "Rate" format 990.99
select	 AccNumber a
	,UsageType b
	,to_char(BillDate,'fmmm/dd/yy')c
	, get_rate(UsageType,billdate)d
from account;

AccNumber UsageType BillDate    Rate
--------- --------- -------- -------
      123         1 2/1/4       1.00
      123         1 3/1/4       1.00
      123         1 4/1/4       1.50
      123         1 5/1/4       1.50
      234         2 2/1/4       0.75

5 rows selected.

Let me know if this satisfactorily resolves your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 02:56 (04Apr04) UTC (aka "GMT" and "Zulu"), 19:56 (03Apr04) Mountain Time)

 

[DugsDMan]

I'll have to see if we can run UDFs. We have an "in-house" Java application that has an Oracle back-end. I'm not sure if the application will allow the use of UDFs or not. I've sent a note to our support contact, but he's out of the office today. Hopefully I'll hear something back tomorrow.

Thanks for the tip!

 

[SantaMufasa]

Dugs,

In the absence of your "support contact", would you encounter any trouble in just trying the solution above within your testing enviornment. The reason I suggest a test is because "One test is worth 10 expert opinions." Your test removes the support contact's temptation to give the "easy" answer: "Just do it the way we've always done it."

It seems to me that if the Java application has access to the application schema's tables, indexes, and other objects, then it should also be able to access the schema's UDFs.

Let us know.

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:40 (05Apr04) UTC (aka "GMT" and "Zulu"), 13:40 (05Apr04) Mountain Time)

 

[DugsDMan]

Sorry about taking so long to get back on this. Things are crazy around here and I haven't had a chance to get back on this. Actually, I still haven't been able to. However, the reason I needed to wait for the support contact is that we do not have the authority to create UDFs. After looking this over some more, I think I can do something very similar to this in the where clause and be able to join the tables. This definitely looks like the right direction.

Thanks for the help Mufasa!

Christmas in April, you get a star!