cost distribution - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

cost distribution

Author

Topic

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-07-11 : 17:22:34

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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-11 : 17:53:00

Is this a homework question??

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-11 : 18:42:30

Sorry, what do you want ? Can you show us the expected result that you need ?

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-07-11 : 21:08:18

well it looks like a homework question to me as well :)

i am working with the health sector and got this complicated query from our clients
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.

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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-11 : 21:41:21

This should start you in the right direction:


SELECT DoctorName, MAX(CASE FULL_TIME WHEN 1 THEN Practice ELSE '' END) as FT_Practice, 
       SUM(CASE FULL_TIME WHEN 1 THEN Practice ELSE '' END) as NumberPartTimes
FROM table1
group by DoctorName

I would create a view from this, then use the view in your query. It should make your calcs reasonably easy.

HTH,

Tim

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-07-13 : 00:42:31

Hi tim
Thanks for the reply.i guess i did not make myself completely clear to you..i ll explain the prob 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

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-13 : 00:58:57

Can you pls post your create table statements and some sample data?

I think the query I gave will still work as a basis; obviously you'll need to modify to handle your date ranges.

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-07-13 : 06:12:31

ya i will send you the create table statements and the sample data tomorrow

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-07-13 : 16:31:58

CREATE TABLE [table1](
[doctor][nvarchar](50),
[PRACTICE] [nvarchar] (50) ,
[startDate][datetime],
[endDate][datetime],
[fullTime][nvarchar],
[cost][float])

here is some sample data..

doctorName PRactice StartDate EndDate FullTime cost
John abc 12/12/2005 null yes $300

John bbb 12/03/2004 null no $234

james ccc 01/09/2004 01/11/2004 no $1000

james abc 01/12/2004 null yes $500

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources