# Complicated query help!!!

Discussion in 'General Developer Questions' started by mita, Jul 11, 2006.

1. ### mitaNew Member

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!!
2. ### joechangNew Member

this sounds like a test question
for which the purpose is to test your knowledge
3. ### aefagerNew Member

Since you have in there an assumption that the doctor full time on one practice cannot be in more than one part time practice as well, I would suggest that the internal math be to treat the full time practice as 100% if no part time status exists for the doctor, and .75 * the cost of the full time practice if a part time status exists for the doctor. Then, when you are calculating the cost on the part time side, sum the costs * 1/# of part time practices, and add cost * 1/4 of the full time practice. since the cost will be 0 if no full time exists, the added value does no harm. I am assuming constraints exist to prevent a doctor from having more than one full time practice, as well.

Good luck!
4. ### mitaNew Member

Hi Thanks for the help..
Well i am sorry if i did not make myself clear..
This query was sent to us from our clients as i am working in a health sector..
we have got one huge table with doctor's names and practices and information wheather they are full time or not..

To simplify the problem, i created a seperate table called practices having all the unique practice names.
i tried to join these two tables on practice field.
but i dont know what to do next

What we want is if a doctor is working as full time in more than one practice, the cost gets distributed equally in all the practices

whereas if a doctor is working full time in one and part time in two others, the cost gets distributed like this:
62.5%(50% + (50%/3) will be assigned to first practice(full time one) and 16%(50%/3) to the rest(part time)..

have i made myself clear this time??
do let me know

many thanks
5. ### joechangNew Member

Table 1 is really an Expenditure table
Table 2 is a Practice table

i think you need a separate Docters table, and a Docters_Practice table

Docter table should have fields to indicate:
a. has full time and no part time (for 100% to full time)
b. has full time and 1 part time (for 75/25 split)
b. has multiple part time (and number for 1/n split)

The Docters_Practice table should has one row for unique Docter, Practice combination, for storing the total expenditure per doc/prac

the Expenditure table should probably be split into
1. an Expenditure table, listing only the Docter and the total amount
2. an ExpenditureDetail table, listing the split amount going into each practice

of course, all this is guessing your intent
6. ### mitaNew Member

Hi joe
Thanks for your help..it does give me some direction but can i guess i was not successful in explaining you the problem clearly..
i ll do it again..
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
7. ### SQLDBcontrolNew Member

quote:Originally posted by mita

Hi
I have got two tables and have one to many relationship on them.

Hi,

here's the problem. In your first post you indicated that there is a one to many relationship. But in actual fact, it is a many to many relationship.

Each practice can have many doctors. And each doctor can work in more than one practice (in your most recent post you indicate: "A doctor can work on any number of practices"). Hence, the relationship is many to many and not one to many as you first assumed.

So joechang was pointing out that you should look at your design an change it accordingly.

What I'm not clear about is what you're after. You said that, "This query was sent to us from our clients as i am working in a health sector".

What was the client's query?

It seems like you started to design or amend an existing design by creating tables in order to simplify a problem but you never told us what the problem was.

In other words, what is the end result you're trying to achieve? Are you trying to design a database that models this scenario or are you trying to get some data out of this huge table that was sent to you? Or something else?

Karl Grambow

www.sqldbcontrol.com
8. ### mitaNew Member

Hi Karl

Thanks for replying.. well sorry if i didnt tell you that i created relationships only to make the problem simpler..but i could not go any further..
anyways the query of our client is that from this huge chunk of data we have to find the total cost drilled down to various levels..

so the main problem is to distribute the cost according to the number of practices a doctor works on.

if a doctor is full time in only one practice, we dont have to worry about the cost.
however if the doctor is working full time in one practice and part time in others,then 75% of cost goes to full time practice and the rest gets distributed equally among the rest of part time practices

and if he is non full time in all the practices, cost gets split equally in all these practices.
but then if there is an end date mentioned against a practice,this means the doctor is no longer working on that practice,so we have to calculate the no of days he worked and then prorate the cost accordingly..

pls help!
9. ### SQLDBcontrolNew Member

Well, taking the tables you provided (I won't go into the merits of the design but as joechang suggested, there is possibly a better design) I'd go for something like this:

First query below, selects and calculates the total cost for a doctor that works full time and part time in more than one practice.

The second query, selects and calculates the total cost for a doctor that works only part time in one or more practices. The cost is calculated by dividing the sum of the total cost by the number of practices the doctor is working in.

You can then union both queries to get it all back as one result-set.

--select for doctors that work in practices full and part time
select DoctorName,
sum(case full_time when 1 then cost * 0.75 else cost * 0.25 end) as total_cost
from doctor d
where exists
(select 1 from doctor where d.doctorname = doctorname
and full_time = 1)
and exists
(select 1 from doctor where d.doctorname = doctorname
and full_time = 0)
group by DoctorName

union all

--select for doctors that work in practices only part time
select DoctorName , sum(cost)/count(*)
from doctor d
where exists
(select 1 from doctor where d.doctorname = doctorname
and full_time = 0)
and not exists
(select 1 from doctor where d.doctorname = doctorname
and full_time = 1)
group by DoctorName

Like I said, it might be worth figuring out a better design. And there might be a better way of achieving this but this is what I could come up with at short notice.

Hope it helps,

Karl Grambow

www.sqldbcontrol.com
10. ### mitaNew Member

I ll definately try this out..but i need to clarify onething with you, does this above query take care of any number of practices that a doctor works on ?
For ex if a doctor is working on practice A as full time and on practice B and C as part time, so this query will calculate the cost in this case??
11. ### karthikmNew Member

iam getting "incorrect syntax near into" when i execute the below in sql server 2005.can i have a solution?

MERGE INTO MyTable
USING MyTempTable
ON MyTempTable.eid= MyTable.eid
WHEN MATCHED THEN
UPDATE sal= MyTempTable.sal
WHEN NOT MATCHED THEN
INSERT VALUES(MyTempTable.eid, MyTempTable.sal)
12. ### SQLDBcontrolNew Member

quote:Originally posted by karthikm

iam getting "incorrect syntax near into" when i execute the below in sql server 2005.can i have a solution?

MERGE INTO MyTable
USING MyTempTable
ON MyTempTable.eid= MyTable.eid
WHEN MATCHED THEN
UPDATE sal= MyTempTable.sal
WHEN NOT MATCHED THEN
INSERT VALUES(MyTempTable.eid, MyTempTable.sal)

I'd start a new thread (in the SQL 2005 forum) for this. I'd also include an explanation of what you're trying to do.

Karl Grambow

www.sqldbcontrol.com
13. ### SQLDBcontrolNew Member

quote:Originally posted by mita

I ll definately try this out..but i need to clarify onething with you, does this above query take care of any number of practices that a doctor works on ?
For ex if a doctor is working on practice A as full time and on practice B and C as part time, so this query will calculate the cost in this case??

Yes, the query should work regardless of how many practices a Dr is working at. Note that the query does not calculate costs for Dr's that only work full time as you said you had this covered.

Karl Grambow

www.sqldbcontrol.com
14. ### karthikmNew Member

quote:Originally posted by SQLDBcontrol

quote:Originally posted by karthikm

iam getting "incorrect syntax near into" when i execute the below in sql server 2005.can i have a solution?

MERGE INTO MyTable
USING MyTempTable
ON MyTempTable.eid= MyTable.eid
WHEN MATCHED THEN
UPDATE sal= MyTempTable.sal
WHEN NOT MATCHED THEN
INSERT VALUES(MyTempTable.eid, MyTempTable.sal)

I'd start a new thread (in the SQL 2005 forum) for this. I'd also include an explanation of what you're trying to do.

Karl Grambow

when can i get a solution for this?

www.sqldbcontrol.com
15. ### mitaNew Member

hey guys..<br />just clarifying.. is it the answer to my post or has karthikm just added his post below mine??[}<img src='/community/emoticons/emotion-1.gif' alt='' />]
16. ### aefagerNew Member

quote:Originally posted by karthikm
when can i get a solution for this?

2. Post it on the correct board (SQL 2005), as Karl suggested.
3. When you reply, do not squeeze it within someone else's reply section, it took a second glance before I figured out that this was the only line of your response.
4. Finally, do not act like you are a boss demanding an answer, it makes people very uneager to help you.

I would suggest that you try to find a coworker to help you since they are likely to have done the same kind of query.

Good luck!
17. ### mitaNew Member

Hi aefager
Well things which u said were quiete rude.. its ok if i wasnt able to make u understand what the problem was and i think i started my own thread and did not jump to anybody else's... y would i do that!!!

And finally i WAS NOT TRYING TO BE A BOSS!!
I am not an expert and i am just learning..

Neways i apologise if i hurt anybody's feelings...

18. ### mitaNew Member

Really sorry for my above post aefager.
I thought all these things which u said were meant for me.. in fact it was for somebody else...

Really sorry..
pls ignore the above post
19. ### aefagerNew Member

quote:Originally posted by mita

Really sorry for my above post aefager.
I thought all these things which u said were meant for me.. in fact it was for somebody else...

Really sorry..
pls ignore the above post
No problem.

BTW, you can edit the original post and remove the comments if you want, rather than sending a second message. I noticed that the person that tried to hijack (take over) your thread did not change his behavior, but I am glad that you got an answer to your question before they jumped in with their problem.

Have a great day!