count unique combinations

[Guest]

Hi
I have got two tables and have one to many relationship on them.
i ll show you the sample data


Table 1
ID DoctorName Practice Full_Time Cost Expenditure
1 ABC qw yes $100
2 ABD wer no $566
3 ZXA ddf yes $22
.........................

table 2
Practices
qw
wer
ffgg
hhjk
ddf
...
The scenario is like this
a doctor can work in more than one practice. and he can be a full time
practioner or a part time practitioner

The problem is if a doctor is a full time practioner in 1 service and a part
time practitioner in another, 75% of cost goes to first practice and the rest
25% goes to other

if the doctor is non full time in more than one practice, the cost gets
distributed equally in all practices.

how shall i do this???
pls HELP!!

 

[Jamie Collins]

Table 1
ID DoctorName Practice Full_Time Cost Expenditure
1 ABC qw yes $100
2 ABD wer no $566
3 ZXA ddf yes $22

a doctor can work in more than one practice. and he can be a full time
practioner or a part time practitioner

The problem is if a doctor is a full time practioner in 1 service and a part
time practitioner in another, 75% of cost goes to first practice and the rest
25% goes to other

if the doctor is non full time in more than one practice, the cost gets
distributed equally in all practices.

I think you have omitted some information and/or your schema is flawed.

How do we know to which practices a doctor is contracted e.g. do you
have another table e.g. Contracts with columns practice_code,
doctor_name and is_full_time? Is your data temporal e.g. do you keep a
history of such contracts so that you can compare the job date with the
doctor's contractual dates?

As your table 1 stands, it seems you are trying to model contractual
arrangements and job costs (without dates) in the same table, which
would appear to be a flaw.

Also, I am not clear on what happens to the cost where a doctor is
contracted full time in more than one practice and part time in yet
more?

A more spec, please.

TIA,
Jamie.

--

 

[Guest]

Hi Jamie
thanks for the reply
i apologise as i have not made myself clear on that..
ill explain you the situation again.the table which i posted was not the
complete one.

my table has got the following fields..
Doctor's Code, Doctor's Name, Practice code, PracticeName, Start Date
, End Date , Full time(y/n)

The situation is like this..
A doctor can work on any number of practices(full time & part time)...
if a doctor is full time in only one service, we do not have to worry.

if a doctor is a full time practioner in 1 service and a part time
practitioner in another, 75% of cost goes to full time practice and the rest
25% goes to the other part time service

if the doctor is non full time in more than one practice, the cost gets
distributed equally in all practices.

There is another scenario as well. If there is no end date mentioned, this
means doctor is still there,however if the end date is there ,we have to
prorate the cost.

This situation is quiete complicated and it seems as a lifetime challenge
for me...

Pls help me

 

[Jamie Collins]

A doctor can work on any number of practices(full time & part time)...
if a doctor is full time in only one service, we do not have to worry.

if a doctor is a full time practioner in 1 service and a part time
practitioner in another, 75% of cost goes to full time practice and the rest
25% goes to the other part time service

if the doctor is non full time in more than one practice, the cost gets
distributed equally in all practices.

You've detailed three scenarios:

One full time and none part time:
full_time_practice = 100%

One full time and one part time:
full_time_practice = 75%
part_time_practice = 25%

More than one full time and none part time:
full_time_practice_1 = 50%
full_time_practice_2 = 50%

However, I can think of several more scenarios (and can think up some
logical percentage splits):

None full time, one part time:
part_time_practice = 100%

One full time and one than one part time:
full_time_practice = 75%
part_time_practice_1 = 12.5%
part_time_practice_2 = 12.5%

None full time, more than one part time:
part_time_practice_1 = 50%
part_time_practice_2 = 50%

More than one full time and one part time:
full_time_practice_1 = 37.5%
full_time_practice_2 = 37.5%
part_time_practice = 25%

More than one full time and one more than one part time:
full_time_practice_1 = 37.5%
full_time_practice_2 = 37.5%
part_time_practice_1 = 12.5%
part_time_practice_2 = 12.5%

....noting that a full time practice could end up with a smaller
percentage than a part time practice:
full_time_practice_1 = 18.75%
full_time_practice_2 = 18.75%
full_time_practice_3 = 18.75%
full_time_practice_4 = 18.75%
part_time_practice = 25%

Perhaps the scenarios you didn't mention are illegal in your data
model? Please confirm because the validity of these scenarios and their
respective percentage splits will impact the design.

Thanks again,
Jamie.

--

 

[Guest]

Thanks Jamie
Well the basic logic is if a practitioner works as full time in a service,we
dont have to bother but if he works full time in one and part time in the
rest then cost 75% goes to full time and rest gets split equally in the rest
of the services..

i understand what you mean and i guess it was my mistake-- a doctor cant
work as full time in more than one practice..

so this is the whole situation..and moreover if there is an end date
mentioned then we have to calculate the no of days he worked and prorate the
cost accordingly..
let me know if there is something on which u r not clear..

 

[Jamie Collins]

a doctor cant
work as full time in more than one practice..

Can a doctor be part time in one practice without being full time at
all?

If there is no end date mentioned, this
means doctor is still there,however if the end date is there ,we have to
prorate the cost.

How? Is there a duration associated with a cost e.g. a patient episode
has a start_date and end_date with a final cost associated with the
episode, and that episode's duration can intersect with a doctor's
contractual start and end dates?

When you say 'no end date mentioned', are you referring to the episode
end date or the doctor's contractual end date?

Can more than one doctor be associated with an episode and therefore
their practices are eligible for a share of its cost?

Sorry for all the questions but this sounds very complex to me and I
don't want to make assumptions

Jamie.

--