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.
LG
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.
LG
