T
Tony Botelho
I am looking for a desing answer more than actual coding.
Here is my dilemna, I hope it's not too confusing:
I currently have a database that, among other things,
tracks how many HOURS are spent on a TASK of any
particular PROJECT. There may be more than one TASK to a
PROJECT but each PROJECT has one SALESMAN assigned to it.
(The SALESMAN is ultimately billed for these hours)
At any time we could print out a report, by date range or
by salesman, that would show all of the TASKS of all of
the PROJECTS and a sum of hours for each salesperson. This
sum was then multiplied by a DOLLAR PER HOUR amount that
was held in a single record table.
Here's the problem... that DOLLAR PER HOUR amount is about
to change. This is fine for all of the new projects/tasks
but a report run on any older projects will use the newer
dollar per hour. Keep in mind that some projects are
compiled of several tasks that may extend into seperate
billing periods - the report (which does list tasks
seperately) will have to show the task AND the
corressponding dollar per hour based on the completion
date of the task.
My first thought is to add another field to the Dollar Per
Hour table that would hold a date - the date that the
corresponding DollarPerHour went into effect. Then each
report would simply run a subroutine for each task, based
on the date it was completed, to see what dollarperhour it
fell into. After toying with that idea for a bit, it
seemed overly cumbersome (you would have to loop through
the billing table for every task to find the range it fell
into) and I thought that there must be an easier way. I
was hoping that someone may have some other ideas...
Does anyone know of a good design for billing, where the
billing amount will be different depending on which date
range it falls into? Any suggestions would be greatly
appreciated.
Here is my dilemna, I hope it's not too confusing:
I currently have a database that, among other things,
tracks how many HOURS are spent on a TASK of any
particular PROJECT. There may be more than one TASK to a
PROJECT but each PROJECT has one SALESMAN assigned to it.
(The SALESMAN is ultimately billed for these hours)
At any time we could print out a report, by date range or
by salesman, that would show all of the TASKS of all of
the PROJECTS and a sum of hours for each salesperson. This
sum was then multiplied by a DOLLAR PER HOUR amount that
was held in a single record table.
Here's the problem... that DOLLAR PER HOUR amount is about
to change. This is fine for all of the new projects/tasks
but a report run on any older projects will use the newer
dollar per hour. Keep in mind that some projects are
compiled of several tasks that may extend into seperate
billing periods - the report (which does list tasks
seperately) will have to show the task AND the
corressponding dollar per hour based on the completion
date of the task.
My first thought is to add another field to the Dollar Per
Hour table that would hold a date - the date that the
corresponding DollarPerHour went into effect. Then each
report would simply run a subroutine for each task, based
on the date it was completed, to see what dollarperhour it
fell into. After toying with that idea for a bit, it
seemed overly cumbersome (you would have to loop through
the billing table for every task to find the range it fell
into) and I thought that there must be an easier way. I
was hoping that someone may have some other ideas...
Does anyone know of a good design for billing, where the
billing amount will be different depending on which date
range it falls into? Any suggestions would be greatly
appreciated.