I Removed a lot of the code to make it smaller
ALTER PROCEDURE [dbo].[csp_ProfilerReport]
@ClientID INT
AS
DECLARE @MyTable1 TABLE
(
MemberID INT,
ClientID INT,
Name VARCHAR(50),
Surname VARCHAR(50),
DoB SMALLDATETIME,
Age INT,
AgeGrouping VARCHAR(10),
Gender vARCHAR(8),
MaritalStatus VARCHAR(12),
Weight INT,
Height FLOAT,
BMI FLOAT,
BMIGrouping VARCHAR(20),
BMIRisk INT,
SmokingRisk INT)
DECLARE @MyTable2 TABLE
(
MemberID INT,
ClientID INT,
Name VARCHAR(50),
Surname VARCHAR(50),
DoB SMALLDATETIME,
Age INT,
AgeGrouping VARCHAR(10),
Gender vARCHAR(8),
MaritalStatus VARCHAR(12),
Weight INT,
Height FLOAT,
BMI FLOAT,
BMIGrouping VARCHAR(20),
BMIRisk INT,
SmokingRisk INT,
AlcoholRisk INT,
ExerciseRisk INT,
Smoking VARCHAR(50),
Alcohol VARCHAR(50),
Exercise VARCHAR(50),
Absenteism VARCHAR(50),
FunctionAtWork VARCHAR(50),
Productivity VARCHAR(50),
[Anxiety and Panic Disorders] INT DEFAULT(0),
[Asthma] INT DEFAULT(0),
[Back Pain] INT DEFAULT(0),
[Breast Cancer] INT DEFAULT(0),
[Chronic Bronchitis / Emphysema] INT DEFAULT(0),
[Chronic Fatigue] INT DEFAULT(0))
INSERT INTO @MyTable1
SELECT Age.MemberID,
m.ClientID,
m.Name,
m.Surname,
(SELECT m.MemberID, al.DisplayName AS Alcohol
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 11) AS Alcohol ON Alcohol.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS Exercise
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 10) AS Exercise ON Exercise.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS Absenteism
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 15) AS Absenteism ON Absenteism.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS 'Function At Work'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 18) AS FunctionAtWork ON FunctionAtWork.MemberID =
Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS 'Productivity'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 17) AS Productivity ON Productivity.MemberID =
Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, CASE al.DisplayName
WHEN 'Alternative Health'
THEN 1
ELSE 0
END AS 'Medical Conditions'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 12
AND al.DisplayName = 'Alternative Health') AS T1 ON T1.MemberID =
Age.memberID
LEFT OUTER JOIN
(SELECT m.MemberID, CASE al.DisplayName
WHEN 'Men''s Health' THEN 1
ELSE 0
END AS 'Medical Conditions'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 12
AND al.DisplayName = 'Men''s Health') AS T2 ON T2.MemberID = Age.memberID
LEFT OUTER JOIN
(SELECT m.MemberID, CASE al.DisplayName
WHEN 'Mental Health' THEN 1
ELSE 0
END AS 'Medical Conditions'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 12
AND al.DisplayName = 'Mental Health') AS T3 ON T3.MemberID = Age.memberID
LEFT OUTER JOIN
(SELECT m.MemberID, CASE al.DisplayName
WHEN 'Pregnancy' THEN 1
ELSE 0
END AS 'Medical Conditions'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 12
AND al.DisplayName = 'Pregnancy') AS T4 ON T4.MemberID = Age.memberID
INSERT INTO @MyTable2
SELECT Age.MemberID,
m.ClientID,
m.Name,
m.Surname,
Age.Age AS DoB,
DATEPART(yy, GETDATE()) - DATEPART(yy, Age.Age) AS Age,
CASE DATEPART(yy, GETDATE()) - DATEPART(yy, Age.Age)
WHEN 0 THEN '0-10'
WHEN 1 THEN '0-10'
WHEN 2 THEN '0-10'
WHEN 3 THEN '0-10'
WHEN 4 THEN '0-10'
WHEN 5 THEN '0-10'
WHEN 6 THEN '0-10'
WHEN 7 THEN '0-10'
WHEN 8 THEN '0-10'
WHEN 9 THEN '0-10'
WHEN 10 THEN '0-10'
WHEN 11 THEN '11-20'
WHEN 12 THEN '11-20'
WHEN 13 THEN '11-20'
WHEN 14 THEN '11-20'
WHEN 15 THEN '11-20'
WHEN 16 THEN '11-20'
WHEN 17 THEN '11-20'
WHEN 18 THEN '11-20'
WHEN 19 THEN '11-20'
WHEN 20 THEN '11-20'
WHEN 21 THEN '21-30'
WHEN 22 THEN '21-30'
WHEN 23 THEN '21-30'
WHEN 24 THEN '21-30'
WHEN 25 THEN '21-30'
WHEN 26 THEN '21-30'
WHEN 27 THEN '21-30'
WHEN 28 THEN '21-30'
WHEN 29 THEN '21-30'
WHEN 30 THEN '21-30'
WHEN 31 THEN '31-40'
WHEN 32 THEN '31-40'
WHEN 33 THEN '31-40'
WHEN 34 THEN '31-40'
WHEN 35 THEN '31-40'
WHEN 36 THEN '31-40'
WHEN 37 THEN '31-40'
WHEN 38 THEN '31-40'
WHEN 39 THEN '31-40'
WHEN 40 THEN '31-40'
WHEN 41 THEN '41-50'
WHEN 42 THEN '41-50'
WHEN 43 THEN '41-50'
WHEN 44 THEN '41-50'
WHEN 45 THEN '41-50'
WHEN 46 THEN '41-50'
WHEN 47 THEN '41-50'
WHEN 48 THEN '41-50'
WHEN 49 THEN '41-50'
WHEN 50 THEN '41-50'
WHEN 51 THEN '51-60'
WHEN 52 THEN '51-60'
WHEN 53 THEN '51-60'
WHEN 54 THEN '51-60'
WHEN 55 THEN '51-60'
WHEN 56 THEN '51-60'
WHEN 57 THEN '51-60'
WHEN 58 THEN '51-60'
WHEN 59 THEN '51-60'
WHEN 60 THEN '51-60'
WHEN 61 THEN '61-70'
WHEN 62 THEN '61-70'
WHEN 63 THEN '61-70'
WHEN 64 THEN '61-70'
WHEN 65 THEN '61-70'
WHEN 66 THEN '61-70'
WHEN 67 THEN '61-70'
WHEN 68 THEN '61-70'
WHEN 69 THEN '61-70'
WHEN 70 THEN '61-70'
WHEN 71 THEN '71-80'
WHEN 72 THEN '71-80'
WHEN 73 THEN '71-80'
WHEN 74 THEN '71-80'
WHEN 75 THEN '71-80'
WHEN 76 THEN '71-80'
WHEN 77 THEN '71-80'
WHEN 78 THEN '71-80'
WHEN 79 THEN '71-80'
WHEN 80 THEN '71-80'
WHEN 81 THEN '81-90'
WHEN 82 THEN '81-90'
WHEN 83 THEN '81-90'
WHEN 84 THEN '81-90'
WHEN 85 THEN '81-90'
WHEN 86 THEN '81-90'
WHEN 87 THEN '81-90'
WHEN 88 THEN '81-90'
WHEN 89 THEN '81-90'
WHEN 90 THEN '81-90'
WHEN 91 THEN '90-100'
WHEN 92 THEN '90-100'
WHEN 93 THEN '90-100'
WHEN 94 THEN '90-100'
WHEN 95 THEN '90-100'
WHEN 96 THEN '90-100'
WHEN 97 THEN '90-100'
WHEN 98 THEN '90-100'
WHEN 99 THEN '90-100'
WHEN 100 THEN '90-100'
END AS 'Age Grouping',
Gender.Gender,
CASE Smoking.Smoking
WHEN 'Nil' THEN 0
ELSE 1
END AS 'Smoking Risk',
CASE Alcohol.Alcohol
WHEN 'Nil' THEN 0
ELSE 1
END AS 'Alcohol Risk',
CASE Exercise.Exercise
WHEN 'Sedentary (<1 workout/week)' THEN 1
ELSE 0
END AS 'Exercise Risk',
Smoking.Smoking,
Alcohol.Alcohol,
Exercise.Exercise,
Absenteism.Absenteism,
FunctionAtWork.[Function At Work],
Productivity.Productivity,
Anxiety.[Medical Conditions] AS 'Anxiety and Panic
Disorders',
Asthma.[Medical Conditions] AS 'Asthma',
Back.[Medical Conditions] AS 'Back Pain'
FROM
(SELECT m.MemberID, mc.OptionValue AS Age FROM Members m Inner Join
_MemberProfileCharacterValues mc ON m.memberID = mc.MemberID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionValue <> '1970-1-1') AS Age
INNER JOIN
(SELECT m.MemberID, al.DisplayName AS Gender
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 2) AS Gender ON Age.MemberID = Gender.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS 'Marital Status'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 6) AS [MaritalStatus] ON [MaritalStatus].MemberID =
Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, OptionValue AS Weight
FROM Members m INNER JOIN _MemberProfileNumericValues mc ON m.memberID =
mc.MemberID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 4
AND mc.OptionValue <> 0) AS Weight ON Weight.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.ComputableValue AS Height
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 3) AS Height ON Height.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS Smoking
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 9) AS Smoking ON Smoking.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS Alcohol
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 11) AS Alcohol ON Alcohol.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS Exercise
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 10) AS Exercise ON Exercise.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS Absenteism
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 15) AS Absenteism ON Absenteism.MemberID = Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS 'Function At Work'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 18) AS FunctionAtWork ON FunctionAtWork.MemberID =
Age.MemberID
LEFT OUTER JOIN
(SELECT m.MemberID, al.DisplayName AS 'Productivity'
FROM Members m INNER JOIN _MemberProfileLookupValues mc ON m.memberID =
mc.MemberID
INNER JOIN _AttributeOptionLookupValues al ON al.ValueID =
mc.ValueID
WHERE m.ClientiD = @ClientID
AND m.Active = 1
AND mc.OptionID = 17) AS Productivity ON Productivity.MemberID =
Age.MemberID
LEFT OUTER JOIN Members m ON m.Memberid = Age.MemberID
WHERE Weight.Weight <> 0
ORDER BY Age.memberID
SELECT DISTINCT
t1.MemberID ,
--t1.ClientID,
t1.Name ,
t1.Surname ,
t1.BMIRisk ,
t1.SmokingRisk ,
t1.ExerciseRisk ,
(t1.SmokingRisk + t1.BMIRisk + t1.ExerciseRisk ) AS
'Lifestyle Risk Index',
0 AS 'Morbidity Index',
t1.DoB,
t1.Age ,
t1.AgeGrouping ,
t1.Gender ,
t1.MaritalStatus ,
t1.Weight ,
t1.Height ,
t1.BMI ,
t1.BMIGrouping ,
t1.Smoking ,
t1.Alcohol ,
t1.Exercise ,
t1.Absenteism ,
t1.FunctionAtWork ,
t1.Productivity ,
t1.[Anxiety and Panic Disorders] ,
t1.[Asthma] ,
t1.[Back Pain] ,
t1.[Breast Cancer] ,
t1.[Chronic Bronchitis / Emphysema] ,
t1.[Chronic Fatigue] ,
t1.[Colon Cancer] ,
t1.[Constipation] ,
t1.[Depression] ,
t1.[Diabetes - Non insulin dependant (NIDDM)] ,
t1.[Eczema / Atopic Dermatitis] ,
t1.[Elevated Cholesterol ] ,
t1.[Epilepsy] ,
t1.[Gout] ,
t1.[Hayfever / Allergic Rhinitis] ,
t1.[Headache / Migraine] ,
t1.[Heartburn] ,
t1.[Hepatitis] ,
t1.[High Blood Pressure] ,
t1.[Infertility] ,
t1.[Irritable Bowel Syndrome] ,
t1.[Menopause] ,
t1.[Osteoarthritis] ,
t1.[Osteoporosis] ,
t1.[Peptic ulcer disease] ,
t1.[Prostate Cancer] ,
t1.[Rheumatoid Arthritis] ,
t1.[Sleep Disorders] ,
t1.[Inflammatory Bowel Disease] ,
t1.[Diabetes - Insulin dependant (IDDM)] ,
t1.[Benign Prostate Hypertrophy],
t2.[Alternative Health],
t2.[Men''s Health],
t2.[Mental Health]
FROM @MyTable2 t1 INNER JOIN @MyTable1 t2 ON t1.MemberID = t2.memberID
-----Original Message-----
From: CarolinDivya
[mailto:mssqldba-ezmlmshield-x36480218.[Email address protected]
Sent: Friday, August 31, 2007 11:23 AM
To: LazyDBA Discussion
Subject: RE: Select Distinct Values
Ya...
Show your query so that we can judge?
Regards,
Carolin Divya.K,
Cognizant technologies,
Chennai.
When I was young, I used to admire intelligent people; as I grow older,
I admire kind people. Abraham Joshua Heschel
-----Original Message-----
From: Damir Sultanbekov
[mailto:mssqldba-ezmlmshield-x85186732.[Email address protected]
Sent: Friday, August 31, 2007 2:49 PM
To: LazyDBA Discussion
Subject: Re: Select Distinct Values
Could you show us your query?
Carel Greaves wrote:
>Just a question
>
>
>
>I have a query that selects profile data for members, if I don't do a
select
>distinct it gives me a lot of correct values, (unique values) of
members
>i.e. only one record per member, but every now and then I get duplicate
>values for one member, multiple times.
>
>
>
>Why does this occure?
>
>
>
>I know SELECT DISTINCT is there to remove duplicates, but without
SELECT
>DISTINCT why would this "mistake" happen?
>
>
>
>Any help would be greatly appreciated.
>
>
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
This e-mail and any files transmitted with it are for the sole use of the
intended recipient(s) and may contain confidential and privileged
information.
If you are not the intended recipient, please contact the sender by reply
e-mail and destroy all copies of the original message.
Any unauthorized review, use, disclosure, dissemination, forwarding,
printing or copying of this email or any action taken in reliance on this
e-mail is strictly
prohibited and may be unlawful.
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page