How to use DateDiff to calculate costs?
Hi,
In my Access bookings system you select a number of Units and a start and end booking time. I need to calculate the cost of each Unit for the booking as well as a total cost for all Units in the booking.
The information is stored in two tables: a bookings table called tblHourPeriod with the resources used and fromTime and thruTime fields. A second table called tblUnit has the list of resources and their rate card costs (per hour). I need the query to calculate the difference between fromTime and thruTime and then multiply this by the rate card. Any ideas how to do this?
Thanks!
Software/Hardware used:
Software/Hardware used:
ASKED:
May 11, 2009 10:16 AM
UPDATED:
May 13, 2009 11:11 PM
Answer Wiki:
It should be something similar to this:
<pre>SELECT tHP.resource_ID, DATEDIFF("h",tHP.fromTime, tHP.thruTime) * tU.cost
FROM tblHourPeriod tHP JOIN tblUnit tU
ON tHP.resource_ID = tU.resource_ID
WHERE <conditions></pre>
To see all answers submitted to the Answer Wiki: View Answer History.
What about
SELECT DateDiff("h", "2009-05-11 01:59:00", "2009-05-11 02:01:00") ;??
sorry i was being particularly slow. I’ve now done it. Thanks
To Carlosdl
The query gives 1 hour instead of 2 minutes.
Oh, thanks Msi77.
I guess that is the way it is expected to be, since we are using ‘h’ as interval.
The above only works if we are going to charge by hour (or fraction).
Carlosdl,
I think that more correctly to calculate in minutes and to round to hour.
You might be right, Sergey.
Thanks for complementing the answer.