more on sql! - Hiya guys, I have a quick SQL question, I have a database of rental prices that have different values for

stu2000 · 30th November 2009, 05:14 PM

Hiya guys,

I have a quick SQL question, I have a database of rental prices that have different values for different days.

What I ideally need to do is calculate the full rental price within an SQL query, so for example:

Code:

add up all "prices" from "prices table" between "from date" and "to date" for the item being rented

...or something along those lines if that makes any sense? I'm not sure where to start, I was looking at SUM but then it's going to need a while clause in the middle, but I've no idea how to combine them (or if it's even right for this problem)! Can anyone point me in the right direction?

Thanks!

Stu

saltedm8 · 30th November 2009, 06:03 PM

I am not sure on the syntax, but cant you just do

PHP Code:

  $var = $var
$var1 = $var1;
$var2 = $var2;
$var3 = $var3;

if  ($var ) {

return $var + $var1;
else $var + $var2;
else $var + $var2;
                  }
print $var;

I know the syntax is probably wrong, but I hope you get the idea.. of course, I could be way off base

stu2000 · 1st December 2009, 09:23 AM

Hi Saltedm8,

Thanks for taking a look!

Unfortunately I need the code in SQL as I need to be able to order by price, which is where I'm having the problem!

I might not have explained myself properly, sorry!

For example, I have a rental table:

Code:

id         item
1          Rental Item 1
2          Rental Item 2
etc...

And I have a prices table:

Code:

id   rental_id   from         to           price
1    1           2010-01-01   2010-03-31   9.99
2    1           2010-04-01   2010-06-30   15.99
etc....

If a customer wants to rent "Rental Item 1" from 2010-03-30 until 2010-04-05, I am trying to work out that those rental dates include 2 days at 9.99, and then 5 days within the 15.99 price bracket, leading to a total rental price of: (2*9.99)+(5*15.99) = 99.93... this is going to be a nightmare! I think I need to find another way of calculating the prices!!

I shall put my thinking cap on as I'm not sure the way I want to do it is possible!!

Stu

saltedm8 · 1st December 2009, 09:53 AM

what about this ?

SQL: SUM Function

stu2000 · 1st December 2009, 10:13 AM

Hiya,

Thanks for the link, I was thinking that's the route to go, however from that page I stole and amended the following code:

Code:

SELECT SUM(`price`) as "Total Price"
FROM rental_prices
WHERE `rental_id` = '1'
AND `from` >= '2010-03-30'
AND `to` <= '2010-04-05';

But this gives me a NULL result. And even when/if I get that particular query working, I'm pretty sure it'll only give the single price of that time period (i.e. 9.99 and 15.99) as I'm not cycling through all the days within those dates, I'm just asking "what is the value of the 'price' field between those dates" rather than "what is the value of the price field for each day between those dates"! If that makes any sense? lol

I think it needs a 'while' statement or something like that, to go:

Code:

set date = '2010-03-30';
while(date < '2010-04-05'){
  SELECT `price` FROM rental_prices WHERE `from` >= 'date'
AND `to` <= 'date'; //ie find the price for that date only
 TotalPrice = TotalPrice + `price`; //increment total price
  increase `date` by 1 day; // increment the date to go to start of while clause and find next days rental price.
}

But I need to brush up on how to use the while statement in SQL, as I'm not sure this is possible!

Stu

stu2000 · 1st December 2009, 10:27 AM

Ignore that first bit, I'm being an idiot as usual! lol I got the more than/less than symbols the wrong way round, the query should have been:

Code:

SELECT SUM(`price`) as "Total Price"
FROM rental_prices
WHERE `rental_id` = '1'
AND `from` <= '2010-03-30'
AND `to` >= '2010-04-05';

This gives me an error though as it won't span across more than one row. So I think maybe the way to go in order to select more than one row is something along the lines of:

Code:

SELECT SUM(  `price`  )  AS  "Total Price"
FROM rental_prices
WHERE  `rental_id`  =  '1'
AND  `from`  <=  '2010-03-30'
UNION 
SELECT SUM(  `price`  )  AS  "Total Price"
FROM rental_prices
WHERE  `rental_id`  =  '1'
AND  `to`  >=  '2010-04-05'

and that does indeed just give me '9.99' and '15.99' as two rows in the results table, rather than the prices for each day between the two days added together.

But again I think I'm on a wild goose chase here, neigh mind! thanks for your help anyway mate!

Stu

saltedm8 · 1st December 2009, 10:50 AM

I think the best people to talk to would be either davidj or cloudedvision.. its definitely a little out of my league lol

send them a pm, see if you get a response

stu2000 · 1st December 2009, 11:17 AM

Thanks mate, out of my league too... I like my PHP but I've never really got on with SQL!! I think I need to take some time out and do a mini course or something!

Anyhow, luck has had it that the site owner has now decided that he wants just one daily price for the whole year for each rental item... phew! I dodged the bullet on that one! lol

saltedm8 · 1st December 2009, 03:09 PM

lol.. dont tell them that, they would expect a discount pmsl

30th November 2009, 05:14 PM	#1
stu2000 Member Join Date: Jan 2009 Location: Spain Posts: 179 Thanks: 6 Thanked 15 Times in 14 Posts Expertise: PHP Experience: Professional	more on sql! Hiya guys, I have a quick SQL question, I have a database of rental prices that have different values for different days. What I ideally need to do is calculate the full rental price within an SQL query, so for example: Code: add up all "prices" from "prices table" between "from date" and "to date" for the item being rented ...or something along those lines if that makes any sense? I'm not sure where to start, I was looking at SUM but then it's going to need a while clause in the middle, but I've no idea how to combine them (or if it's even right for this problem)! Can anyone point me in the right direction? Thanks! Stu

30th November 2009, 06:03 PM	#2
saltedm8 Join Date: Dec 2008 Location: Kent Uk Posts: 1,458 Blog Entries: 5 Thanks: 13 Thanked 43 Times in 43 Posts Expertise: (X)HTML / CSS Experience: Professional	I am not sure on the syntax, but cant you just do PHP Code: `$var = $var $var1 = $var1; $var2 = $var2; $var3 = $var3; if ($var ) { return $var + $var1; else $var + $var2; else $var + $var2; } print $var;` I know the syntax is probably wrong, but I hope you get the idea.. of course, I could be way off base __________________ THePiNcH

1st December 2009, 09:23 AM	#3
stu2000 Member Join Date: Jan 2009 Location: Spain Posts: 179 Thanks: 6 Thanked 15 Times in 14 Posts Expertise: PHP Experience: Professional	Hi Saltedm8, Thanks for taking a look! Unfortunately I need the code in SQL as I need to be able to order by price, which is where I'm having the problem! I might not have explained myself properly, sorry! For example, I have a rental table: Code: id item 1 Rental Item 1 2 Rental Item 2 etc... And I have a prices table: Code: id rental_id from to price 1 1 2010-01-01 2010-03-31 9.99 2 1 2010-04-01 2010-06-30 15.99 etc.... If a customer wants to rent "Rental Item 1" from 2010-03-30 until 2010-04-05, I am trying to work out that those rental dates include 2 days at 9.99, and then 5 days within the 15.99 price bracket, leading to a total rental price of: (29.99)+(515.99) = 99.93... this is going to be a nightmare! I think I need to find another way of calculating the prices!! I shall put my thinking cap on as I'm not sure the way I want to do it is possible!! Stu Last edited by stu2000; 1st December 2009 at 10:14 AM.. Reason: lining up tables lol

1st December 2009, 09:53 AM	#4
saltedm8 Join Date: Dec 2008 Location: Kent Uk Posts: 1,458 Blog Entries: 5 Thanks: 13 Thanked 43 Times in 43 Posts Expertise: (X)HTML / CSS Experience: Professional	what about this ? SQL: SUM Function __________________ THePiNcH

1st December 2009, 10:13 AM	#5
stu2000 Member Join Date: Jan 2009 Location: Spain Posts: 179 Thanks: 6 Thanked 15 Times in 14 Posts Expertise: PHP Experience: Professional	Hiya, Thanks for the link, I was thinking that's the route to go, however from that page I stole and amended the following code: Code: SELECT SUM(`price`) as "Total Price" FROM rental_prices WHERE `rental_id` = '1' AND `from` >= '2010-03-30' AND `to` <= '2010-04-05'; But this gives me a NULL result. And even when/if I get that particular query working, I'm pretty sure it'll only give the single price of that time period (i.e. 9.99 and 15.99) as I'm not cycling through all the days within those dates, I'm just asking "what is the value of the 'price' field between those dates" rather than "what is the value of the price field for each day between those dates"! If that makes any sense? lol I think it needs a 'while' statement or something like that, to go: Code: set date = '2010-03-30'; while(date < '2010-04-05'){ SELECT `price` FROM rental_prices WHERE `from` >= 'date' AND `to` <= 'date'; //ie find the price for that date only TotalPrice = TotalPrice + `price`; //increment total price increase `date` by 1 day; // increment the date to go to start of while clause and find next days rental price. } But I need to brush up on how to use the while statement in SQL, as I'm not sure this is possible! Stu

1st December 2009, 10:27 AM	#6
stu2000 Member Join Date: Jan 2009 Location: Spain Posts: 179 Thanks: 6 Thanked 15 Times in 14 Posts Expertise: PHP Experience: Professional	Ignore that first bit, I'm being an idiot as usual! lol I got the more than/less than symbols the wrong way round, the query should have been: Code: SELECT SUM(`price`) as "Total Price" FROM rental_prices WHERE `rental_id` = '1' AND `from` <= '2010-03-30' AND `to` >= '2010-04-05'; This gives me an error though as it won't span across more than one row. So I think maybe the way to go in order to select more than one row is something along the lines of: Code: SELECT SUM( `price` ) AS "Total Price" FROM rental_prices WHERE `rental_id` = '1' AND `from` <= '2010-03-30' UNION SELECT SUM( `price` ) AS "Total Price" FROM rental_prices WHERE `rental_id` = '1' AND `to` >= '2010-04-05' and that does indeed just give me '9.99' and '15.99' as two rows in the results table, rather than the prices for each day between the two days added together. But again I think I'm on a wild goose chase here, neigh mind! thanks for your help anyway mate! Stu

1st December 2009, 10:50 AM	#7
saltedm8 Join Date: Dec 2008 Location: Kent Uk Posts: 1,458 Blog Entries: 5 Thanks: 13 Thanked 43 Times in 43 Posts Expertise: (X)HTML / CSS Experience: Professional	I think the best people to talk to would be either davidj or cloudedvision.. its definitely a little out of my league lol send them a pm, see if you get a response __________________ THePiNcH

1st December 2009, 11:17 AM	#8
stu2000 Member Join Date: Jan 2009 Location: Spain Posts: 179 Thanks: 6 Thanked 15 Times in 14 Posts Expertise: PHP Experience: Professional	Thanks mate, out of my league too... I like my PHP but I've never really got on with SQL!! I think I need to take some time out and do a mini course or something! Anyhow, luck has had it that the site owner has now decided that he wants just one daily price for the whole year for each rental item... phew! I dodged the bullet on that one! lol

1st December 2009, 03:09 PM	#9
saltedm8 Join Date: Dec 2008 Location: Kent Uk Posts: 1,458 Blog Entries: 5 Thanks: 13 Thanked 43 Times in 43 Posts Expertise: (X)HTML / CSS Experience: Professional	lol.. dont tell them that, they would expect a discount pmsl __________________ THePiNcH

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode