Hello,
I work for a telephone company and am producing a database to help us with our monthly billing. We get a CSV file from our supplier each month with a list of calls made by our customers - including the date/time, phone number the call was made from and to, the duration in seconds and the amount it cost us. We then apply a markup.
This has worked great, but we now want to start offering deals and packages, where you could buy x amount of minutes per month. For example, the first 4000 minutes you use are included in the plan and the remainder are billed accordingly.
I need to produce a query which brings me a list of calls for each customer using a plan, but only if the total duration of the calls is less than the plan allowance. The current tables I have set up are:
CUSTOMERS - containing CustID, CustName
(CustID is a number, CustName is Short Text)
PLANS - containing PlanID, PlanType, PlanAllowance
(PlanID is a number, PlanType is Short Text and is set to either A or B where A = landline and B = mobile, and PlanAllowance is the number of inclusive minutes in the plan)
PLAN_ALLOC - containing PlanAllocID, PlanID and CustID
(These are all number fields, where a plan is allocated to a customer. I.e. plan 1 is allocated to customer 888, and can also be allocated to customer 777 etc).
CALLS - containing CallID, CallDateTime, CallFrom, CallTo, CallType, CallDuration, CallCostPrice, CallCost
(CallID is autonumber, CallDateTime is self-explanatory, CallFrom is the telephone number the call was made from [used to allocate the call to an individual customer in another table], CallTo is the destination, CallType is A for landline or B for mobile, CallDuration is the duration in seconds, CallCostPrice is what we paid and CallCost is updated by an update query to apply the rate we bill the call out at).
What we have so far is an update query which goes through each record and changes CallCost to be the rate we bill the call out at. We keep CallCostPrice as the price we pay, so we can calculate profit margins, etc.
I want to carry on doing this, but add an extra step which makes CallCost = 0 for all calls which are included in the plan. But, this should only take place for the first calls in the month (in chronological order) which were covered by the call plan. So if the customer has a 4000 minute call plan for Type A calls and they use 5000 minutes, the calls relating to the first 4000 minutes in the month should have CallCost updated to be 0. The remaining calls are then left as they should be - with the correct billing amount in.
I hope this isn't too confusing - it's a complex system which has served me well so far, but I can't get my head around how to achieve this! I may be over thinking it?
Any advice greatly appreciated. :banghead:
I work for a telephone company and am producing a database to help us with our monthly billing. We get a CSV file from our supplier each month with a list of calls made by our customers - including the date/time, phone number the call was made from and to, the duration in seconds and the amount it cost us. We then apply a markup.
This has worked great, but we now want to start offering deals and packages, where you could buy x amount of minutes per month. For example, the first 4000 minutes you use are included in the plan and the remainder are billed accordingly.
I need to produce a query which brings me a list of calls for each customer using a plan, but only if the total duration of the calls is less than the plan allowance. The current tables I have set up are:
CUSTOMERS - containing CustID, CustName
(CustID is a number, CustName is Short Text)
PLANS - containing PlanID, PlanType, PlanAllowance
(PlanID is a number, PlanType is Short Text and is set to either A or B where A = landline and B = mobile, and PlanAllowance is the number of inclusive minutes in the plan)
PLAN_ALLOC - containing PlanAllocID, PlanID and CustID
(These are all number fields, where a plan is allocated to a customer. I.e. plan 1 is allocated to customer 888, and can also be allocated to customer 777 etc).
CALLS - containing CallID, CallDateTime, CallFrom, CallTo, CallType, CallDuration, CallCostPrice, CallCost
(CallID is autonumber, CallDateTime is self-explanatory, CallFrom is the telephone number the call was made from [used to allocate the call to an individual customer in another table], CallTo is the destination, CallType is A for landline or B for mobile, CallDuration is the duration in seconds, CallCostPrice is what we paid and CallCost is updated by an update query to apply the rate we bill the call out at).
What we have so far is an update query which goes through each record and changes CallCost to be the rate we bill the call out at. We keep CallCostPrice as the price we pay, so we can calculate profit margins, etc.
I want to carry on doing this, but add an extra step which makes CallCost = 0 for all calls which are included in the plan. But, this should only take place for the first calls in the month (in chronological order) which were covered by the call plan. So if the customer has a 4000 minute call plan for Type A calls and they use 5000 minutes, the calls relating to the first 4000 minutes in the month should have CallCost updated to be 0. The remaining calls are then left as they should be - with the correct billing amount in.
I hope this isn't too confusing - it's a complex system which has served me well so far, but I can't get my head around how to achieve this! I may be over thinking it?
Any advice greatly appreciated. :banghead:
