Query help needed please

[m8fyu]

wonder if anyone can help me out on this MySQL query please as I am totally stuck.

My task is to write a query to get the following details:

booking_id, car_reg, car_make, car_model, customer_fname, customer_sname, booking_start, booking_end
plus the number of days the car is booked out
and also the total cost for the booking (car_type_price)

Tables:

Car_types
car_type_id
car_type_descr
car_type_price

Cars
car_id
car_reg
car_make
car_model
car_year
fk_car_type

Customers
customer_id
customer_sname
customer_fname
customer_title
customer_address1
customer_address2
customer_postcode
customer_phone
customer_email
customer_dl_no

Bookings
booking_id
fk_car_id
fk_cust_id
booking_start
booking_end

I'm so so stuck and I haven't got the foggiest. I've written so many queries but nothing seems to output the intended data. Can anyone help me out on this please.

I'll carry on trying to get this one sorted but I'd really appreciate any help anyone can offer. i'm at my wits end and think I may have over exhausted myself as its been hours now. I will post back immediately with any update to my situation.

[LifeinAsia]

Well, assuming you get the joins right to get the booking_id, car_reg, car_make, car_model, customer_fname, customer_sname, booking_start, and booking_end values, computing the number of days and total cost is just a date conversion and basic math.

So for now, ignore the number of days and total cost. Show us your query to display the other fields and we can help you from there.

[m8fyu]

Thanks so much for your help. It really is much appreciated.

So far I have this, which seems to be OK looking at the output in my query browser. I'm not too sure about the DATEDIFF but it seems to give the the intended result.

SELECT
booking_id AS 'Booking ID',
car_reg AS 'Reg',
car_make AS 'Make',
car_model AS 'Model',
cust_fname AS 'First Name',
cust_sname AS 'Surname',

DATEDIFF(a.booking_end, a.booking_start)+1 AS 'Hire Days'

FROM
dtfma.Bookings a,
dtfma.Cars b,
dtfma.Customers c

WHERE
a.fk_car_id = b.car_id AND
a.fk_cust_id = c.cust_id

ORDER BY
booking_id

So I think all I need to do now is calculate the total cost. Can you provide any guidance on how I would achieve this?

Thanks again

[m8fyu]

Ooops, I forgot to mention that I'm not required to display the start and end date any longer. My original thread says that I do. Now I'm simply required to display the number of days the booking is for. Thx.

[LifeinAsia]

Well, assuming that the cost is constant for each date, it's a simple matter of (number of days) x (cost/day) = (total price).

However, if you have multiple prices (e.g., $X/day on weekdays, but $Y/day on weekends), then it gets more complicated. But from your table structure, it looks like static pricing, so it should be simple.

[rocknbil]

One way would be

(DATEDIFF(a.booking_end, a.booking_start)+1 * car_type_price) as subtotal

Assuming, of course, car_type_price is cost per day. I say "subtotal" because I don't see setup fees, taxes, insurance, etc.

[m8fyu]

Hi

Thanks for the help. A little jiggery pokery on the last bit of advice and it works perfectly.

My final query is:

SELECT
booking_id AS 'Booking ID',
car_reg AS 'Reg',
car_make AS 'Make',
car_model AS 'Model',
cust_fname AS 'First Name',
cust_sname AS 'Surname',

DATEDIFF(a.booking_end, a.booking_start)+1 AS 'Hire Days',
(DATEDIFF(a.booking_end, a.booking_start)+1) * d.car_type_price AS 'Total'

FROM
dtfma.Bookings a,
dtfma.Cars b,
dtfma.Customers c,
dtfma.Car_types d

WHERE
a.fk_car_id = b.car_id AND
a.fk_cust_id = c.cust_id AND
d.car_type_id = b.fk_car_type_id

ORDER BY
booking_id



Thanks again guy. As ever webmasterworld has proved invaluable.