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!!
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!
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
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
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
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
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!
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
Thanks for your help Karl.. 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??
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)
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
quote:Originally posted by mita Thanks for your help Karl.. 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
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
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='' />]
quote:Originally posted by karthikm when can i get a solution for this? Probably never if you choose not to follow the advice given. 1. Post this in your own thread instead of jumping into someone else's thread. 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!
Hi aefager Thanks for your reply. 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... Thanks for your help.
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
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!
