Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Calculate actual amount to be paid

Calculate actual amount to be paid

Calculate actual amount to be paid

Hi, I need to create a report to show the amount my client is supposed to pay for vehicles provided to transport their staff.

We have a table that holds contract details for the vehicle.
1) the contract has a start and end date and this is held in Contract.Start_Date and Contract.End_Date
2) the contract has a daily cost and it is in Contract.Daily_Cost
3) Vehicle will not run if it is a public holiday. Contract.Aday will be a "P" if it is a public holiday, otherewise it will be an "N"

Another table holds the travel details for the staff.
4) staff have a travel start and end date and this is held in Staff.Tr_Start_Date and Staff.Tr_End_Date
5) They travel on specified days and this is held as a string in Staff.Tr_Days, ie 0123456 - 0 denotes Monday, 1 denotes Tuesday and so on.

My client will only pay for the daily cost if staff is travelling on it and the report needs to show them how much they should pay for the vehicles between their selected dates of 01/01/2019 to 31/03/2019:

Contract.Start_Date: 01/12/2018
Contract.End_Date: 07/04/2019
Contract.Daily_Cost: $50
**25/12/2018 and 01/01/2019 are public holidays so Contract.Aday will be "P" for these 2 dates.**

Staff A
Staff.Tr_Start_Date: 15/12/2018
Staff.Tr_End_Date: 28/02/2019
Staff.Tr_Days: 01 3 (which is Mon, Tue and Thu)

Staff B
Staff.Tr_Start_Date: 01/02/2019
Staff.Tr_End_Date: 30/04/2019
Staff.Tr_Days: 3 6 (which is Thu and Sun)

So the report should show $1,900. How can I get the report to show this amount please?

$50 x 8 Mondays (for Staff A until 28/02/2019)
$50 x 8 Tuesdays (for Staff A until 28/02/2019 and not including 01/01/2019 as it is a public holiday)
$50 x 13 Thursdays (for both Staff A and B from 01/01/2019 to 31/03/2019)
$50 x 9 Sundays (for Staff B from 01/02/2019 to 31/03/2019)

Thank you.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close