Click here to Skip to main content
15,887,875 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Trying to pull very detailed information from a large hospital database. Software uses SQL to build a report but repeats entries if multiple tasks are involved.
Example:
Patient XYZ had 2 surgeries on same day. Report will list patient XYZ twice for each surgery. Trying to stop it from listing second time.

New to any type of coding but have been able to get to this point with research.

Below is the current Code we are using (minus private data). Any help would be greately appreciated.

select distinct top 100 percent Rtrim(Ltrim(Patients.FirstName))+space(Len(Patients.MiddleInit))+Rtrim(ltrim(Patients.MiddleInit))+' '+Patients.LastName as Name,
Patients.BirthDate as Birth_Date,
Encounter.EncounterType as Study_Code,
dbo.ConcatReferring(Encounter.EncounterID) as Referring,
dbo.GetLastStudydays(Encounter.PatientID, '', getdate(),'') as Days_since_last_study,
Encounter.EncounterID, Patients.PatientID, Facility.FacilityName as Lab
FROM
Encounter LEFT OUTER JOIN PatientReferral ON (Encounter.EncounterID=PatientReferral.EncounterID)
LEFT OUTER JOIN Personnel Referring ON (Referring.PersonID=PatientReferral.ReferringID) LEFT OUTER JOIN Facility ON (Encounter.EncounterLocation=Facility.FacilityID)
INNER JOIN Patients ON (Patients.PatientID=Encounter.PatientID)
LEFT OUTER JOIN PatientEndPoint ON (Patients.PatientID=PatientEndPoint.PatientID)
WHERE (
dbo.GetLastStudydays(Encounter.PatientID, '', getdate(),'') >= 365 AND
(
Referring.Last_Name = '*' OR
Referring.Last_Name = '*'
) AND
dbo.GetLastStudyresult(Encounter.PatientID, '','') <> 'Negative' AND
NOT (
PatientEndPoint.SurvivalStatus = 'Dead' OR
Patients.BirthDate < '01/01/1925 12:00:00 AM' OR
Encounter.EncounterType like 'SBE%' OR
Encounter.EncounterType like 'SBF%' OR
Encounter.EncounterType like 'SBG%' OR
Encounter.EncounterType like 'SBB%'
) AND
dbo.GetLastStudydays(Encounter.PatientID, '', getdate(),'') < 730
)

ORDER BY Name ASC, Referring ASC
Posted

1 solution

Try using GROUP BY instead of DISTINCT, see this article
https://support.microsoft.com/en-us/kb/139444/[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900