Calculating cost of room with a function 1

[mrasad]

I currently have a table (reserved room) in my database, I have a number of columns. I have a room reserved ID, a date_for and date_to, and member id (foreign key) . Example;

'RR001'
to_date('21-11-2003 09:00','DD-MM-YYYY HH24:MI'),
to_date('21-11-2003 11:00','DD-MM-YYYY HH24:MI'),
1 .....

Ideally, I think what I need is a stored function that calculates the time the room has been booked for and then is multiplied by x amount of pounds/dollars (i.e. 11:00 -09:00 * 50). I think I am able to do that in a query, but I’m am not sure how I would do it if for example I wanted to enter the reserved ID, the calculation was performed and then displayed the reserved id, the member id, and the total amount.

Is it possible to do that?

 

[BJCooperIT]

Assuming the rate is the same for all dates in the range you can create a function that will calculate the total amount. Pass the start date, end date and the rate as input paramaters and return the total amount:

:TOTAL_AMT := 
  F_CALC_TOTAL('11:00','09:00',50);

The function will do the TO_DATE and the math to calculate the total.

Is this what you meant?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[SantaMufasa]

Mr. Asad,

From where is the rate for the room supplied? (...another table; fixed rate per hour, per day; et cetera?)

Since (time * rate) is such a simple operation, you can perform the operation as part of your SELECT, but certainly you can make a simple function, as well.

If you cannot figure out either option, provide answers to the above questions, and I'm sure we'll supply a solution.

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:41 (01Jan04) GMT, 12:41 (01Jan04) Mountain Time)

 

[mrasad]

hi

Its a fixed rate of £50 per hour. This amount has not been stored anywhere in the database.

Will it help if I added this field to a table? When I was creating the database, I thought because it was a fixed rate, i would not have to include it, but if you think its a good idea, then I will.

 

[BJCooperIT]

If your table will contain historical data and/or if the rate could change (as most rates do) a column for rate would be advisable. Many hotels charge one rate for weekdays and a different rate for weekends, which would affect your design as well.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[SantaMufasa]

Mr. Asad,

As I'm sure BJ and other programmers would agree, "CONSTANTS aren't (in most cases)". And unless you are prepared to fix your charges FOREVER at "£50 per hour", then it is best to create a rate table that presents updatable rates for given rooms, dates, length of stay, et cetera. It is unlikely that your sales manager would not negotiate special rates for special circumstances. All such special rates should appear in your rate table to match the special circumstances. Even then, once Reservationists apply a given rate, there should also be the flexibility, with management approval, to adjust the rate FOR THE SPECIFIC RESERVATION even further, based upon even "more special" circumstances.

So, in summary, there should be a rate pick list that appears based upon given characteristics of the reservation, then once the "Book Rate" appears, then (based upon business rules) an authorised person can adjust the rate specifically for the reservation.

BJ's sample function invocation in her first post should work fine for you. In a heavily simplified example of non-function-based code to resolve your need, you could use the following SELECT:

SELECT ...(date_for-date_to)*(50*24) total_cost...

Mufasa
(aka Dave of Sandy, Utah, USA @ 20:54 (01Jan04) GMT, 13:54 (01Jan04) Mountain Time)

 

[mrasad]

Thanks for the suggestion, Bj and Dave. A simply sql statement is working.

I'm still trying to work on producing a simply function, but as let have not sloved it.

 

[BJCooperIT]

I did not realize you were looking for an coded example of how to create a function. You can type "oracle function" in a search engine such as

http://www.GOOGLE.com

to find the proper syntax if you do not have access to reference manuals.

Create or Replace Function {function_name} 
(paramterN IN | OUT | IN OUT)
RETURN return_type IS
BEGIN
       ...your code...
END function_name;

In your case this would be something like:

Create or Replace Function F_CALC_TOTAL
( P_FOR_DATE  in date,
  P_TO_DATE   in date,
  P_RATE      in number
) RETURN number IS
V_AMOUNT number := 0;
BEGIN
  V_AMOUNT := (P_TO_DATE - P_FOR_DATE) * P_RATE;
  return(V_AMOUNT);
EXCEPTION
when invalid_number then return(-1);
when others         then return(-2);
END F_CALC_TOTAL;

Your PL/SQL calling code would look like:

:TOTAL_AMOUNT := 
F_CALC_TOTAL
(to_date('21-11-2003 09:00','DD-MM-YYYY HH24:MI'),
 to_date('21-11-2003 11:00','DD-MM-YYYY HH24:MI'),
 50
);

I hope this helps.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[mrasad]

Thanks alot Bj, that helps alot. I will need to change bits of the code.

Silly Question Time

Once a function has been created, how to I find out/execute to see if that function is working correcly.

Thank for the tip about the google search

Asad

 

[BJCooperIT]

Log into SQL*Plus and run an SQL statement that uses your function, such as:

select 
F_CALC_TOTAL
(to_date('21-11-2003 09:00','DD-MM-YYYY HH24:MI'),
 to_date('21-11-2003 11:00','DD-MM-YYYY HH24:MI'),
 50
) total_amount
from dual;

Good luck!

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000

 

[mrasad]

When I run the function

select
F_CALC_TOTAL
(to_date ('21-11-2003 21:00','DD-MM-YYYY HH24:MI'),
to_date ('21-11-2003 14:00','DD-MM-YYYY HH24:MI'),
10) total_amount
from dual;

I don't get the result I want. I get;

TOTAL_AMOUNT
------------
14.583333

Which does not mean very much. If for example I use bj code above
(P_TO_DATE - P_FOR_DATE) * P_RATE
OR '21-11-2003 21:00' '21-11-2003 14:00' * 50

should give me 350?

How can I acheive this?

 

[BJCooperIT]

Sorry that I was not able to test the code first. The problem stems from handling the times as date fields.

Create or Replace Function F_CALC_TOTAL
( P_FOR_DATE  in date,
  P_TO_DATE   in date,
  P_RATE      in number
) RETURN number IS
V_AMOUNT     number := 0;
BEGIN
  V_AMOUNT :=
    round(((P_TO_DATE - P_FOR_DATE)*24) * P_RATE,2);
  return(V_AMOUNT);
EXCEPTION
when invalid_number then return(-1);
when others         then return(-2);
END F_CALC_TOTAL;

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant/Custom Forms & PL/SQL - Oracle 8.1.7 - Windows 2000