SQL Server Performance Forum – Threads Archive

Tough one

Hi guys
I have not been able to solve this problem from quiete a long time.
I was not able to explain my problem clearly in my earlier post.
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??? 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]

Hi, mita are you still looking for solution? Would you mind to answer a few questions. 1- There is a bit confusion
You said if a doctor is working as full time in more than one practice, the cost gets
distributed equally in all the practices What does it mean?
if smiths practising practice abc and xyz with costing respectively $1000 and $2000.
how i fit it to the above statement? 2-
You said
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.
A- Does it mean one of practice is full and other is part time pratice?
B- Or does it mean both are full time?
C- Or does it mean both are part time? 3-
You said
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. Do i take it as formula below?
total cost= y = 100
full time = yx3/4 = 100×3/4 = 75 = 75%
part time = yx1/4 = 100×1/4 = 25 = 25% 4-
You said
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 it is bit confusing here.
Would you mind to put some data here (e.g; 10-20 records) to show your idea. What is your regional time? mine is GMT – 5. Waqar. ________________________________________________
~* Opinions are like a$$holes, everyone got one. *~

Without delving too deeply into your request, I would say first that the data is ‘suboptimally’ normalized. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> Any time you touch these, especially if you query them together in a join, you will almost certainly be getting cartesian situations or duplicate join conditions. If you cannot move the data to third normal form (which should fix you up here) you will probably need to include DISTINCT(s) in your queries to avoid duplications. Best, SQLGuru

<blockquote id="quote">quote:<hr height="1" noshade id="quote"> I would say first that the data is ‘suboptimally’ normalized. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ /> <hr height="1" noshade id="quote"></blockquote id="quote"> You are right, and there is something else wrong in this model. Why are you using NVARCHAR(255) all over the place? Even if the column will only ever contain Y or N. A CHAR(1) would be a much better choice, methinks. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Second you haven’t posted the full DDL. But without fully anticipating your requirement, I would guess that this looks either like a monster CASE expression with subqueries all around or temp tables come into play. Does it need to be done in T-SQL or can you also write a small client script? – Frank Kalis Microsoft SQL Server MVP Webmaster:<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a>

]]>