Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support |

#1
December 8th, 2011, 03:37 AM
 helenp
Contributing User

Join Date: Sep 2004
Location: Marbella, Spain
Posts: 383
Time spent in forums: 6 Days 8 h 41 m 34 sec
Reputation Power: 0

Hi I have a calculator script.
In my sql I have a table with the start day and the end day of each month, and every month I have to change the year on the start day and end day of the month, so I have a year in the database. And in

Then to calculate the prices I use a script like this (only past 3 months as is very long and the rest is equal)
Code:
```
// I calculate the prices using 2 diferent types of cases to get the price for the dates the customer choosed in a form:

(CASE WHEN ('\$arrival' BETWEEN feb_start AND feb_fin and '\$departure' BETWEEN feb_start AND feb_fin)
THEN sum(febbr) * (TO_DAYS('\$departure') - TO_DAYS('\$arrival'))ELSE NULL END) AS price12,
(CASE WHEN ('\$arrival' BETWEEN feb_start AND feb_fin and '\$departure' > feb_fin)
THEN sum(febbr) * (TO_DAYS(march_start) - TO_DAYS('\$arrival'))ELSE NULL END) AS price13,
(CASE WHEN ('\$arrival' < feb_start and '\$departure' BETWEEN feb_start AND feb_fin)
THEN sum(febbr) * (TO_DAYS('\$departure') - TO_DAYS(feb_start))ELSE NULL END ) AS price14,

(CASE WHEN ('\$arrival' BETWEEN march_start AND march_fin and '\$departure' BETWEEN march_start AND march_fin)
THEN sum(marzobr) * (TO_DAYS('\$departure') - TO_DAYS('\$arrival'))ELSE NULL END) AS price15,
(CASE WHEN ('\$arrival' BETWEEN march_start AND march_fin and '\$departure' > march_fin)
THEN sum(marzobr) * (TO_DAYS(april_start) - TO_DAYS('\$arrival'))ELSE NULL END ) AS price16,
(CASE WHEN ('\$arrival' < march_start and '\$departure' BETWEEN march_start AND march_fin)
THEN sum(marzobr) * (TO_DAYS('\$departure')  - TO_DAYS(march_start))ELSE NULL END ) AS price17,

(CASE  WHEN ('\$arrival' BETWEEN april_start AND april_fin and '\$departure' BETWEEN april_start AND april_fin)
THEN sum(abrilbr) * (TO_DAYS('\$departure') - TO_DAYS('\$arrival'))ELSE NULL END) AS price21,
(CASE WHEN ('\$arrival' BETWEEN april_start AND april_fin and '\$departure' > april_fin)
THEN sum(abrilbr) * (TO_DAYS(mayo_inicio) - TO_DAYS('\$arrival'))ELSE NULL END) AS price22,
(CASE WHEN ('\$arrival' < april_start and '\$departure' BETWEEN april_start AND april_fin)
THEN sum(abrilbr) * (TO_DAYS('\$departure')  - TO_DAYS(april_start) )ELSE NULL END) AS price23,

etc.

(CASE WHEN ('\$arrival' < feb_start and '\$departure' > feb_fin)
THEN sum(febbr) * (TO_DAYS(march_start) - TO_DAYS(feb_inicio))ELSE NULL END) AS price46,

(CASE WHEN ('\$arrival' < march_start and '\$departure' > march_fin)
THEN sum(marzobr) * (TO_DAYS(april_start) - TO_DAYS(march_start))ELSE NULL END) AS price47,

(CASE WHEN ('\$arrival' < april_start and '\$departure' > april_fin)
THEN sum(abrilbr) * (TO_DAYS(may_start) - TO_DAYS(april_start))ELSE NULL END) AS price49,

etc.

//then I pass the value of the queries rows into a normal php var:

\$price23=\$row["price23"];
\$price24=\$row["price24"];
\$price25=\$row["price25"];

etc.

//then using php I sum the php vars to get the total price

\$total=\$price+\$price1+\$price2+\$price3+\$price4+\$price5+\$price6+\$price7```

This works like a charm, however every now and then I have clients that wants speacial price for easter, and as easter changes dates every year this is complicated to do. I dont want to change the script every year, as one forget and I think once a script is done and working it should not be touched.

Any idea of how I can add special a case for a week in march or april depending of year into this script without changing it every year? Or maybe I should do another script that is more flexibel.
Helena

Last edited by helenp : December 8th, 2011 at 07:37 AM.

#2
December 12th, 2011, 07:51 AM
 helenp
Contributing User

Join Date: Sep 2004
Location: Marbella, Spain
Posts: 383
Time spent in forums: 6 Days 8 h 41 m 34 sec
Reputation Power: 0
As expected no answer, it is not easy though.

I suppose the correct way of doing a calculator script where you can have diferents dates for easter, and for christmas etc.
I have the easiests, the end and start day of every month with every property as a row with a price for each month.

However as there are always persons that want things in a diferent way, I cant personalize with this script.

So I suppose the correct would be a table for each property, however that I dont like as I often calculate price for all properties together to get the one the client want.

I suppose another way would be to have a column for every day of a year, and insert the daily price for that column for every property....
however dont know if is a good idea having 365 columns....and having to change the year and date if feb have 29 days.
And how to do the script....
any ideas?

Last edited by helenp : December 12th, 2011 at 07:54 AM.

#3
December 12th, 2011, 01:43 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,359
Time spent in forums: 3 Months 1 Week 2 Days 5 h 19 m 15 sec
Reputation Power: 4140
script? properties? tables?

seems to me like what you need is a calendar table that you can pre-load with a few years of data, that will flag easter for you

as for that other stuff you're doing, with the CASE expressions, i have no idea what that's all about, but my gut says it can be greatly simplified

for instance, why are you storing the start day of the month? isn't it always the 1st?
__________________
r937.com | rudy.ca

#4
December 12th, 2011, 02:38 PM
 helenp
Contributing User

Join Date: Sep 2004
Location: Marbella, Spain
Posts: 383
Time spent in forums: 6 Days 8 h 41 m 34 sec
Reputation Power: 0
Quote:
 Originally Posted by r937 script? properties? tables? seems to me like what you need is a calendar table that you can pre-load with a few years of data, that will flag easter for you as for that other stuff you're doing, with the CASE expressions, i have no idea what that's all about, but my gut says it can be greatly simplified for instance, why are you storing the start day of the month? isn't it always the 1st?

Hm, If I dont remember bad, you were the one that helped me with the script, it was really complicated, if it was not you it was some other expert in mysql in this forum.

Let me explain, I have another table where the bookings are inserted, and when I insert a booking for exampel I choose the arrival and departur dates, and the cases calculate the prices for the diferent months as some are low, medium and high season, and often a booking can be part of medium and high season etc.

A calendar table sounds interesting, however no clue of how to do, is there some script to download? Even if I can do a calendar table, isnt the problem the same to calculate the prices, the script that get the result must change every year,?
That is what I dont want to do, as complicated scripts, mostly I just forgot how I did and prefer not to touch.

Last edited by helenp : December 12th, 2011 at 02:44 PM.

#5
December 12th, 2011, 03:50 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,359
Time spent in forums: 3 Months 1 Week 2 Days 5 h 19 m 15 sec
Reputation Power: 4140
a calendar table would have one row for each date in each year

each date would have some kind of indicator describing what season it is

2012-02-28 high
2012-02-29 high
2012-03-01 low
2012-03-02 low

(you can also have another column that indicates holidays like easter, if you want)

then to calculate prices, you simply pull out all the dates that fall between the arrival and departure, and calculate a price for each date, and sum them all up

you don't need all those CASEs because each date knows what season it is

#6
December 12th, 2011, 03:59 PM
 helenp
Contributing User

Join Date: Sep 2004
Location: Marbella, Spain
Posts: 383
Time spent in forums: 6 Days 8 h 41 m 34 sec
Reputation Power: 0
Quote:
 Originally Posted by r937 a calendar table would have one row for each date in each year each date would have some kind of indicator describing what season it is 2012-02-28 high 2012-02-29 high 2012-03-01 low 2012-03-02 low (you can also have another column that indicates holidays like easter, if you want) then to calculate prices, you simply pull out all the dates that fall between the arrival and departure, and calculate a price for each date, and sum them all up you don't need all those CASEs because each date knows what season it is

Thanks, Sounds good, however the problem is that everybody dont have the same high and low dates and price....so I suppose the dates would be the columns? and I would have to add each property as a new row, and its price for each day....
How many columns is ok for a mysql table?
And how can I add easily the columns with the dates? I imagine there is a php function to do this.
But more complicated, how do I add the price for each date and property on more than 300 columns???
Maybe I can insert where xx is between date and date?

Edited, upps, I see now you said the dates will be in rows.....
So I would have to insert the properties as columns.....not sure how that would work, as it is the contrary as to what I have know

Last edited by helenp : December 12th, 2011 at 04:28 PM.

#7
December 12th, 2011, 04:17 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,359
Time spent in forums: 3 Months 1 Week 2 Days 5 h 19 m 15 sec
Reputation Power: 4140
Quote:
 Originally Posted by helenp Thanks, Sounds good, however the problem is that everybody dont have the same high and low dates....
so you would have a separate calendar for each one

Quote:
 Originally Posted by helenp So I would have to insert the properties as columns...
properties???

#8
December 12th, 2011, 04:37 PM
 helenp
Contributing User

Join Date: Sep 2004
Location: Marbella, Spain
Posts: 383
Time spent in forums: 6 Days 8 h 41 m 34 sec
Reputation Power: 0
Quote:
 Originally Posted by r937 so you would have a separate calendar for each one properties???

Yes properties, we rent out properties.
However separate calendar I think will be a problem as we take care of the properties, its not a portal,
When I insert a booking I would have to choose table to calculate the prices (owner and client price) to insert in the database.
Also I have a very practical calculator that checks availability and prices for a given date on all properties, so to do that I would have to calculate using many tables, .....
Complicated, at least for me lol

#9
December 12th, 2011, 04:54 PM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,359
Time spent in forums: 3 Months 1 Week 2 Days 5 h 19 m 15 sec
Reputation Power: 4140
each property would have its own calendar

primary key would be { property_id, cal_date }

all these rows would be in a single table

hey, look, i don't want to tell you what to do, i'm only describing how i would handle different seasons for different properties, and including easter, as you originally wanted

there's no way i'd use a complicated query with all those CASE expressions

but i don't want to redesign your entire application...

#10
December 12th, 2011, 05:29 PM
 helenp
Contributing User

Join Date: Sep 2004
Location: Marbella, Spain
Posts: 383
Time spent in forums: 6 Days 8 h 41 m 34 sec
Reputation Power: 0
Quote:
 Originally Posted by r937 each property would have its own calendar primary key would be { property_id, cal_date } all these rows would be in a single table hey, look, i don't want to tell you what to do, i'm only describing how i would handle different seasons for different properties, and including easter, as you originally wanted there's no way i'd use a complicated query with all those CASE expressions but i don't want to redesign your entire application...

Thanks,
Yes I know you dont want to tell me what to do, and I apreciate your advices, however I am just trying to figure out if I can apply those to my application or not.
Thats what I want handle each proeprty separetely but at the same time all together....ie been able to do a query on them all.

How many tables can be involved in a query? I guess not to many.

I dont understand this:
primary key would be { property_id, cal_date }

all these rows would be in a single table

#11
December 13th, 2011, 03:51 AM
 helenp
Contributing User

Join Date: Sep 2004
Location: Marbella, Spain
Posts: 383
Time spent in forums: 6 Days 8 h 41 m 34 sec
Reputation Power: 0
Quote:
 Originally Posted by r937 each property would have its own calendar primary key would be { property_id, cal_date } all these rows would be in a single table

I have been playing around a bit, trying to understand what you said, If I understood correctly I would have one table with the dates in rows, and one table for each property and to calculate the price I would join the tables.

Playing I did a table with several years dates in rows, then I added a property as column, just to test, the name of the column = name_of_property and then I inserted the prices for high season for 2012 like this.
UPDATE calendar_table_owner
SET Casa_Blanca_4 = 514.285 where dt between '2012-07-01' and '2012-08-31'

Then to calculate the price for a week in July I just did this:
SELECT SUM(Casa_Blanca_4) AS price_total
FROM calendar_table_owner where dt between ' 2012-07-01' and '2012-07-08' however it gave me the price of 8 days instead of 7 nights, but that is a minor problem I think.

Is it posible to do it this way? that way I would have all the properties in 2 tables, one with client price and one with owner price.
What do you think?

Last edited by helenp : December 13th, 2011 at 03:57 AM.

#12
December 13th, 2011, 05:11 AM
 r937
SQL Consultant

Join Date: Feb 2003
Posts: 26,359
Time spent in forums: 3 Months 1 Week 2 Days 5 h 19 m 15 sec
Reputation Power: 4140
Quote:
 Originally Posted by helenp What do you think?
i think naming a column for a property is totally wrong
Code:
```SELECT SUM(price * dailyrate) AS price_total
FROM calendar
WHERE property = 'Casa_Blanca_4'
AND cal_date BETWEEN '2012-07-01' AND '2012-07-08' ```

Last edited by r937 : December 13th, 2011 at 05:14 AM.

#13
December 13th, 2011, 07:08 AM
 helenp
Contributing User

Join Date: Sep 2004
Location: Marbella, Spain
Posts: 383
Time spent in forums: 6 Days 8 h 41 m 34 sec
Reputation Power: 0
Quote:
 Originally Posted by r937 i think naming a column for a property is totally wrong Code: ```SELECT SUM(price * dailyrate) AS price_total FROM calendar WHERE property = 'Casa_Blanca_4' AND cal_date BETWEEN '2012-07-01' AND '2012-07-08' ```

Why is naming a column for a property wrong? not correct by the rules, or can give problems?

Anyway your solution looks better, however cant make it work,
I did a new table using your query to make sure I do the right thing (as I dont understand how the structure of the table should be) it looks to me only one table and not several:
Code:
```SELECT SUM(price * dailyrate) AS price_total
FROM calendar
WHERE property = 'Casa_Blanca_4'
AND cal_date BETWEEN '2012-07-01' AND '2012-07-08'```

And I got as error, price, dailyrate, property and cal_date are unknown columns, so I added these columns, also I added the dates to cal_date.
When I do the query again I dont get error but I do get Null as a result, and it does not look correct to me at all,
these are the last rows where I inserted the properties and prices.
Full texts property cal_date price dailyrate
Edit Edit Edit Inline Edit Copy Copy Delete Delete 0 2013-12-30 0.000 0.000
Edit Edit Edit Inline Edit Copy Copy Delete Delete 0 2013-12-31 0.000 0.000
Edit Edit Edit Inline Edit Copy Copy Delete Delete Casa_Blanca_4 0000-00-00 514.285 300.142
Edit Edit Edit Inline Edit Copy Copy Delete Delete Costa_Nagueles_2 0000-00-00 478.689 125.456

Last edited by helenp : December 13th, 2011 at 07:26 AM.

 Viewing: Dev Shed Forums > Databases > MySQL Help > Calculator script - add easter