count unique combinations

M

mita

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!!
 
M

mita

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
 
M

mita

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

mita

Hi Jamie
Thats fine i do understand.. this problem is not that easy to make anybody
understand..

see the situation is at the moment we are not dealing with the patients'
side..we are just concerned with the doctors' information..

When i say start date and end date, i mean when the DOCTOR started working
on that practice and when he finished working on that practice..

a doctor can be part time in more than one service without being full time..

here is some sample data fo you to have a look..

Doctors id doctorName PRactice StartDate EndDate FullTime
1 John abc 12/12/2005 null
Yes
2 John bbb 12/03/2004 null
no
3 james ccc 01/09/2004 01/11/2004
no
4 james abc 01/12/2004 null
ye
....................................................................................................

Hope this helps..
do let me know if you have some doubts
Cheers :)
 
Top