Tough query - 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)

Tough query

Author

Topic

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-07-23 : 23:20:04

Hi guys
I have not been able to solve this problem from quiete a long time.My
question is
i have two tables- practices and practitioner

the practice table contains information on the practices that are performed
by practitioners in different cities and a field called Cost
And an additional information whether the practitoner is full time or part
time

A practitoner can work on any number of practices , full time or part time.
He can even work full time on more than one practice or any combination

The pattern of this table is as follows

Practice-Table

City PractitionerName PracticeName Cost fulltime(y/n)
A Smith XYZ $1233 y
A Jones ABC $300 n
B Clare QWE $1111 y
......................................

The second table contains the practitioner data such as a practioner is
working on how many practices, what was the start date,end date and weather
he was full time or part time ...

PractitionerTable

City PractitionerName Practice StartDate EndDate Fulltime(y/n)
A Smith ABC 12/12/2004 Null
y
A Smith XYZ 03/09/2005 Null
n
B Clare AbX 03/08/2000 03/07/2001 n
B Clare ASD 05/09/2000 Null
y
....................................................

now i have to distribute the cost according to the type of service (full
time or part time)

The calculation is as follows
if a doctor is working as full time in more than one practice, the cost gets
distributed equally in all the practices

actually these costs are paid by the disttrict boards but we are not
concerned about that..Our job is just to distribute the cost amongst the
practices by the practitioners.

If a Practitioner works in two practices and is not recorded as a non-full
time practitioner in any of the two practices, 50% of their expenditure will
be assigned to Practice A and 50% of the expenditure will be assigned to
Practice B.

Example 2: If a practitoner is working full time in practice A and working
as a non-full time practitioner in one other practice B, then 75% of their
expenditure will be assigned to Practice A and 25% of the expenditure will be
assigned to Practice B.

Example 3: If a practitioner is working full time in practice AA and working
as a non-full time practitioner in three other practices , then 62.5% (50% +
(50%/4)) of their expenditure will be assigned to Practice A and 37.5%
(50%/4*3) of the expenditure will be assigned to Practice B.

hope this helps..

I am really struggling with this problem..how shall i do this???

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-24 : 01:06:57

Can you post some sample data and the table defs?

I assume the costs table has a date column? Otherwise you can't reliably link it to the practises table. Send it to my email address (see profile) if it's too big to fit here.

Cheers,

Tim

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-07-24 : 23:02:02

hi
The practice table(Cost) does not have any dates..however we have to assume that the name of the practitioners and practices must be the part of the practitioner table. which means practitioner is sort of a reference table where the names of all the practitioner and practices are listed..

Here is the DLL of my tables
Practitioner Table

USE [Practitioner]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Practitioner](

[city] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PracticeName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PractitionerName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Practitioner_Startdate] [datetime] NULL,
[Practitioner_Enddate] [datetime] NULL,
[fulltime] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL

) ON [PRIMARY]

Practice Table

USE [Practices]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Practices](
[City] [nvarchar] NULL,
[PracticeName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PractitionerName] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,

[Fulltime] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[Cost] [float] NULL
) ON [PRIMARY]

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-24 : 23:49:29

Yes, but what happens if a practitioner moves about? e.g. Dr Bob worked full time for Practice A last year but now works at Practice B. Hence if a cost was incurred last year then you have no way of telling which practice it relates to.

mitasid
Yak Posting Veteran

51 Posts

Posted - 2006-07-27 : 20:15:22

hi Timmy. you are right.
If the doctor shifts the practices, we have to calculate the no of days the doctor worked on the old practice and then calculate the cost depending on wheather he was full time or part time and then calculate the cost for the new job

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources