How to use DateDiff to calculate costs?

105 pts.
Tags:
Database programming
DATEDIFF
Microsoft Access
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!

Answer Wiki

Thanks. We'll let you know when a new response is added.

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>

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • msi77
    What about
    SELECT DateDiff("h", "2009-05-11 01:59:00", "2009-05-11 02:01:00") ;
    1,660 pointsBadges:
    report
  • carlosdl
    ??
    68,470 pointsBadges:
    report
  • Dontgetit
    sorry i was being particularly slow. I've now done it. Thanks
    105 pointsBadges:
    report
  • msi77
    To Carlosdl The query gives 1 hour instead of 2 minutes.
    1,660 pointsBadges:
    report
  • carlosdl
    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).
    68,470 pointsBadges:
    report
  • msi77
    Carlosdl, I think that more correctly to calculate in minutes and to round to hour.
    1,660 pointsBadges:
    report
  • carlosdl
    You might be right, Sergey. Thanks for complementing the answer.
    68,470 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following