Re: bookings
Kevin Waterson wrote: I am (trying) to make a booking systems. Currently I have a table with 3 timestamps that record bookingDate, bookingFromDate and bookingToDate I have another table that records 'seasons'. This table contains two timestamps that record seasonStartDate and seasonEndDate also I have the rates as seasonRateWeekly and seasonRateNightly What I need to do, is INSERT INTO bookings the bookingFromDate and bookingToDate, no problem there, but I need to know what rate to charge them. and if the booking dates overlap seasons, the appropriate rate needs to be applied. Kevin: You have several options: * do it in the application logic ( write a UDF or a static MySQL function to do the computation * if your application is not going to be deployed for a year or so or if you have enough confidence in MySQL alpha, use stored procedures in the bleeding edge MySQL 5.0 -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookings
here's a quick and dirty way to do it:
1) make the seasons table look like this:
dayofyear int
season enum('winter','spring','summer','fall')
rate decimal(5,2)
2) populate 'seasons' with 1-366 for dayofyear and the corresponding
season and daily rate.
3) to get the total rate do a query like:
select sum(seasons.rate) as total,sum(1) as daysbooked from
bookings,seasons where
bookings.id=$id and
seasons.dayofyear BETWEEN DATE_FORMAT(bookingFromDate,%j) AND
DATE_FORMAT(bookingToDate,%j)
I don't know how you're charging weekly rates, but you could just
check if daysbooked=7 and knock a percentage off total.
gl,
- Mark
On Wed, 3 Mar 2004 22:28:20 +1100, Kevin Waterson wrote:
I am (trying) to make a booking systems.
Currently I have a table with 3 timestamps that record bookingDate,
bookingFromDate and bookingToDate
I have another table that records 'seasons'.
This table contains two timestamps that record
seasonStartDate and seasonEndDate
also I have the rates as seasonRateWeekly and seasonRateNightly
What I need to do, is INSERT INTO bookings the bookingFromDate and
bookingToDate, no problem there, but I need to know what rate to
charge them. and if the booking dates overlap seasons, the
appropriate rate needs to be applied.
All replies greatfully recieved,
Kevin
--
__
(_ \
_) )
| / / _ ) / _ | / ___) / _ )
| | ( (/ / ( ( | |( (___ ( (/ /
|_| \) \_||_| \) \)
Kevin Waterson
Port Macquarie, Australia
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookings
Kevin Waterson wrote:
I am (trying) to make a booking systems.
Currently I have a table with 3 timestamps that record
bookingDate, bookingFromDate and bookingToDate
I have another table that records 'seasons'.
This table contains two timestamps that record
seasonStartDate and seasonEndDate
also I have the rates as seasonRateWeekly and seasonRateNightly
What I need to do, is INSERT INTO bookings the bookingFromDate and
bookingToDate, no problem there, but I need to know what rate to
charge them. and if the booking dates overlap seasons, the appropriate
rate needs to be applied.
All replies greatfully recieved,
Kevin
That sounds like a job for your application so I would just do a few
tests on the dates you received from the user before you try to insert
a booking record in your database.
like (this is PHP, I don't know what you use but the logic is the same
anyway)
if (($s = season($startdate)) == ($e=season($enddate))) {
if interval($startdate,$enddate,days) 7) {
$rate = $weekly[$s];
}
else {
$rate = $nightly[$s];
}
} else {
//it straddles two seasons... do something
}
then INSERT into bookings ...
I assume bookingDate is
like today's date, and FromDate and ToDates are the date the facility
is requested... You need to define what you want to do for a weekly booking
that straddles seasons, use the higher rate or the lower rate or average
them or something
Also just to make processing
easier later on you might want to define a flag to indicate whether they are
getting a weekly or nightly bill
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookings
This one time, at band camp, Mark Maggelet [EMAIL PROTECTED] wrote: I don't know how you're charging weekly rates, but you could just check if daysbooked=7 and knock a percentage off total. I was thinking of having each day of a period charged as the weekly period rate divided by 7. But this seems a bit cumbersome. I guess I would need some sort of grouping for the rooms, such that rooms could be grouped together by price and number of bedrooms/beds and rooms could be assigned to groups. Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookings
This one time, at band camp, Sasha Pachev [EMAIL PROTECTED] wrote: * if your application is not going to be deployed for a year or so or if you have enough confidence in MySQL alpha, use stored procedures in the bleeding edge MySQL 5.0 How would I benifit from a stored procedure? Kind regards Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bookings
How would I benifit from a stored procedure? Stored procedures live with your data and can be called directly from inside queries. They can be customized to do what you want to the data before mysql gives it to you (SELECT) or after you feed it in, which is what you want for an insert. so your stored procedure would be something like optimal_rate(date1,date2) where it did the logic I said before only it runs in your database server instead of your application (e.g. web server), and is more closely bound to the data. so if you had such a stored procedure all you would have to do when a user inputs booking dates is turn around and say something like insert bookings (bookstart,bookend,rate) VALUES ($start,$stop,OPTIONAL_RATE($start,$stop)) well you'd need to get the syntax right but your application code would be much simpler and the crunching would be done by the DBMS instead of your application. especilly nice if the DBMS is on sombody else's nickel.;) Like the previous poster said, for now version 5 is still a little on the bleeding edge to use for production but I've heard it supports stored procedures. I used them in postgres and they are a pain to write but nice once you get them. Kind regards Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bookings
I am (trying) to make a booking systems. Currently I have a table with 3 timestamps that record bookingDate, bookingFromDate and bookingToDate I have another table that records 'seasons'. This table contains two timestamps that record seasonStartDate and seasonEndDate also I have the rates as seasonRateWeekly and seasonRateNightly What I need to do, is INSERT INTO bookings the bookingFromDate and bookingToDate, no problem there, but I need to know what rate to charge them. and if the booking dates overlap seasons, the appropriate rate needs to be applied. All replies greatfully recieved, Kevin -- __ (_ \ _) ) | / / _ ) / _ | / ___) / _ ) | | ( (/ / ( ( | |( (___ ( (/ / |_| \) \_||_| \) \) Kevin Waterson Port Macquarie, Australia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Querying for continuous bookings
Hi there, My problem at this time is following: I have a table of position bookings, having information of a position code, beginning time of the booking and end time: Pos | Begings | Ends APP | 2003-09-30 11:00:00 | 2003-09-30 12:15:00 APP | 2003-09-30 12:15:00 | 2003-09-30 13:00:00 DEP | 2003-09-30 10:30:00 | 2003-09-30 13:30:00 ... Now I should make a query that, in some way, gives me an information of the positions that are booked without any pause for specified time. For example, 2003-09-30 11:00 - 2003-09-30 13:00 should return APP and DEP. Anyway, if I queried for period of 2003-09-30 10:30 - 2003-09-30 12:20, I should receive only DEP. Any ideas how to build such a query? Thanks for information, Ville M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Querying for continuous bookings
well, I am working with mysql for less than 2 months so I may not give you right answer but I guess we cannot solve this problem without programming. The point is how to combine the times to have a one POS with continuous time. this query is an example to combine to records: SELECT table1.pos, table1.Begings, table2.Ends FROM mytable as table1, mytable as table2 WHERE table1.pos = table2.pos AND table1.Ends = table2.Begings ORDER BY table1.pos, table1.Begings, table2.Begings but this is not working if there are 3 records (or more) which should be combined together. so if you don't have this case, you can work more on this query to have the other records and do the search on the final query. - Original Message - From: Ville Mattila [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, September 22, 2003 10:56 AM Subject: Querying for continuous bookings Hi there, My problem at this time is following: I have a table of position bookings, having information of a position code, beginning time of the booking and end time: Pos | Begings | Ends APP | 2003-09-30 11:00:00 | 2003-09-30 12:15:00 APP | 2003-09-30 12:15:00 | 2003-09-30 13:00:00 DEP | 2003-09-30 10:30:00 | 2003-09-30 13:30:00 ... Now I should make a query that, in some way, gives me an information of the positions that are booked without any pause for specified time. For example, 2003-09-30 11:00 - 2003-09-30 13:00 should return APP and DEP. Anyway, if I queried for period of 2003-09-30 10:30 - 2003-09-30 12:20, I should receive only DEP. Any ideas how to build such a query? Thanks for information, Ville M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
