February 5, 2009 at 3:17 am
I am working a hospital patient event data set. Currently my extract list all patient activity within each care specilaty. Consequently some patients have more than more than one record per care specialty. However I am required to reduce the extract such that it shows distinct patients for each care specialty. Any ideas on how I can do this.
Thanks
February 5, 2009 at 3:49 am
select Distinct
or use Group by and Aggregate functions on the required columns.
if you want specific help then post the query and table structure..
February 5, 2009 at 4:00 am
the current query is
SELECT AR.SequenceID,CL.ClientID,GPP.PCG AS PrimaryTrust,AAP.AppointmentDate,APT.CodeDescription AS AppointmentType,ARS.CodeDescription AS ReferralSource,
GS.CodeDescription AS Specialty,convert(varchar(10),CL.DateOfBirth,110) as [Date Of Birth],CL.NNN AS NHS_NUMBER,GE.CodeDescription as Ethnicity, CGP.GPCode,CA.PostCode,WR.WARD_ID,WR.WARD_NAME
FROM AMSAppointment AAP
INNER JOIN AMSAppointmentContact APC ON APC.SequenceID = AAP.SequenceID
INNER JOIN AMSreferral AR ON AR.ClientID = APC.ClientID AND AR.ReferralNumber = APC.ReferralID
LEFT OUTER JOIN GenSpecialty GS
ON GS.Code = AR.SpecialtyReferredTo
LEFT OUTER JOIN AmsReferralSource ARS
ON ARS.Code = AR.ReferralSource
LEFT OUTER JOIN CLIENT CL
ON CL.ClientID = APC.ClientID
LEFT OUTER JOIN GenEthnicity GE
ON CL.Ethnicity = GE.Code
LEFT OUTER JOIN ClientGP CGP
ON CGP.ClientID = APC.ClientID AND CGP.ToDate IS NULL
LEFT OUTER JOIN ClientAddress CA
ON CA.ClientID = APC.ClientID AND CA.ToDate IS NULL
LEFT OUTER JOIN AmsAppointmentType APT
ON APT.Code = AAP.AppointmentType
INNER JOIN GenGP GP ON CGP.GPCode = GP.CODE -- GET GP description
INNER JOIN GenGPPractice GPP ON CGP.PracticeCode = GPP.Code --GET PRACTISE CODE DETAILS
LEFT OUTER JOIN BRENTREPODB02.Olympus.dbo.POSTCODE BR ON BR.POST_CODE = CA.PostCode
LEFT OUTER JOIN BRENTREPODB02.Olympus.dbo.WARD WR ON WR.WARD_ID = BR.WARD_ID
WHERE AAP.AppointmentDate >= '2008/10/01' AND AAP.AppointmentDate <= '2008/12/31'
Order by GS.CodeDescription
February 5, 2009 at 4:02 am
I forgot to add.
CL.ClientID is the patient ID and
GS.CodeDescription AS Specialty is the care specialty field
February 5, 2009 at 4:06 am
So you want to group by the ClientId and CodeDescription
Out of the other columns, which ones have values that are not distinct?
Or post a sample of the output of the query..
February 5, 2009 at 4:34 am
I have attached a screenshot of the output.
February 5, 2009 at 5:04 am
SELECT
CL.ClientID,
GS.CodeDescription AS Specialty
FROM
AMSAppointment AAP INNER JOIN AMSAppointmentContact APC
ON APC.SequenceID = AAP.SequenceID INNER JOIN AMSreferral AR
ON AR.ClientID = APC.ClientID AND AR.ReferralNumber = APC.ReferralID LEFT OUTER JOIN GenSpecialty GS
ON GS.Code = AR.SpecialtyReferredTo LEFT OUTER JOIN AmsReferralSource ARS
ON ARS.Code = AR.ReferralSource LEFT OUTER JOIN CLIENT CL
ON CL.ClientID = APC.ClientID LEFT OUTER JOIN GenEthnicity GE
ON CL.Ethnicity = GE.Code LEFT OUTER JOIN ClientGP CGP
ON CGP.ClientID = APC.ClientID AND CGP.ToDate IS NULL LEFT OUTER JOIN ClientAddress CA
ON CA.ClientID = APC.ClientID AND CA.ToDate IS NULL LEFT OUTER JOIN AmsAppointmentType APT
ON APT.Code = AAP.AppointmentType INNER JOIN GenGP GP
ON CGP.GPCode = GP.CODE -- GET GP description
INNER JOIN GenGPPractice GPP
ON CGP.PracticeCode = GPP.Code --GET PRACTISE CODE DETAILS
LEFT OUTER JOIN BRENTREPODB02.Olympus.dbo.POSTCODE BR
ON BR.POST_CODE = CA.PostCode LEFT OUTER JOIN BRENTREPODB02.Olympus.dbo.WARD WR
ON WR.WARD_ID = BR.WARD_ID
WHERE
AAP.AppointmentDate >= '2008/10/01' AND AAP.AppointmentDate <= '2008/12/31'
GROUP BY
CL.ClientID,
GS.CodeDescription
Order by
GS.CodeDescription
February 5, 2009 at 5:14 am
Many thanks.
February 5, 2009 at 5:20 am
just one more thing. How can I alter your script so that I can bring in all the other fields that were in the original extract?
February 5, 2009 at 6:47 am
For the values that are repeating over the rows, such as referral source and speciality then you just add these to the select statement and group by clause.
But for the other values you need to work out what you want to do with them, for example what appoinment date do you want ? min / max
February 5, 2009 at 7:03 am
I need the max appointment date
February 5, 2009 at 7:06 am
okay then you add this with a aggregate funciton in the select clause
SELECT
CL.ClientID,
GS.CodeDescription AS Specialty,
MAX(AAP.AppointmentDate) as MaxAppointmentDate
FROM
AMSAppointment AAP INNER JOIN AMSAppointmentContact APC
ON APC.SequenceID = AAP.SequenceID INNER JOIN AMSreferral AR
ON AR.ClientID = APC.ClientID AND AR.ReferralNumber = APC.ReferralID LEFT OUTER JOIN GenSpecialty GS
ON GS.Code = AR.SpecialtyReferredTo LEFT OUTER JOIN AmsReferralSource ARS
ON ARS.Code = AR.ReferralSource LEFT OUTER JOIN CLIENT CL
ON CL.ClientID = APC.ClientID LEFT OUTER JOIN GenEthnicity GE
ON CL.Ethnicity = GE.Code LEFT OUTER JOIN ClientGP CGP
ON CGP.ClientID = APC.ClientID AND CGP.ToDate IS NULL LEFT OUTER JOIN ClientAddress CA
ON CA.ClientID = APC.ClientID AND CA.ToDate IS NULL LEFT OUTER JOIN AmsAppointmentType APT
ON APT.Code = AAP.AppointmentType INNER JOIN GenGP GP
ON CGP.GPCode = GP.CODE -- GET GP description
INNER JOIN GenGPPractice GPP
ON CGP.PracticeCode = GPP.Code --GET PRACTISE CODE DETAILS
LEFT OUTER JOIN BRENTREPODB02.Olympus.dbo.POSTCODE BR
ON BR.POST_CODE = CA.PostCode LEFT OUTER JOIN BRENTREPODB02.Olympus.dbo.WARD WR
ON WR.WARD_ID = BR.WARD_ID
WHERE
AAP.AppointmentDate >= '2008/10/01' AND AAP.AppointmentDate <= '2008/12/31'
GROUP BY
CL.ClientID,
GS.CodeDescription
Order by
GS.CodeDescription
If that is all the columns you need then this query should be fine. Otherwise you need to work out what aggregate functions to perform on the remainder of columns
February 5, 2009 at 7:19 am
Thank you very much. You have been a great help
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply