SQL Server Performance

Complicated query help!!!

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

  1. mita New 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. joechang New Member

    this sounds like a test question
    for which the purpose is to test your knowledge
  3. aefager New 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. mita New 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. joechang New 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. mita New 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. SQLDBcontrol New 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. mita New 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. SQLDBcontrol New 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. mita New Member

    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??
  11. karthikm New 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. SQLDBcontrol New 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. SQLDBcontrol New Member

    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
  14. karthikm New 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. mita New 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. aefager New Member

    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!
  17. mita New Member

    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.
  18. mita New 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. aefager New 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!

Share This Page