[RESOLVED] Calculation problem - Please help ...

pradee

Hi!

I have a calculation problem in this system i am doing for on-line holiday booking.

Rates are given for a particular date range as follows in the "rate" table:
E.g.
$10 for 2005/10/01 - 2005/10/05

$20 for 2005/10/05 - 2005/10/10

If client wants to travel from 2nd to 9th, the cost should be $120.00
Calculation:
2nd to 5th @ $10 per day = $40
6th to 9th @ $20 per day = $80

My problem is, how to figure out the exact dates & get the rate from the 'rate' table?

If possible, someone please help since i am in a hurry to finish this.

Thanks & regards,

Pradee.

Roger_Ramjet

First you need to 'clean-up' your rates table. At present there is no way to decide if the rate for the 2005/10/05 is $10 or $20 - it features in 2 rows.

What column type are you using to store the dates? It will effect how you write the query.

pradee

Thanks a lot for your reply, Roger. I appreciate it.

Yes, it's true, the rate is not clear for 2005/10/05. Let's assume it's $10. Therefore, the rate table will look like the following:

date_from...........date_to.................rate.........property_id 2005/10/01.........2005/10/05...............10..............3
2005/10/06........ 2005/10/10...............20..............3

I use mySQL as the database and 'date' type for dates. Is it right or should i use 'datetime' instead for this ? Please advice.

Have a nice day!

Thanks & regards,

Pradee.