使用SQL构建数据报告时尝试消除重复的条目 [英] Trying to eliminate duplicate entries when using SQL to build data report

问题描述

尝试从大型医院数据库中提取非常详细的信息。软件使用SQL构建报告，但如果涉及多个任务，则重复条目。

例如：

患者XYZ在同一天进行了2次手术。报告将为每次手术两次列出患者XYZ。试图阻止它第二次上市。



新的任何类型的编码，但已经能够通过研究达到这一点。



以下是我们正在使用的当前代码（减去私人数据）。任何帮助都会非常感激。



选择不同的前100％Rtrim（Ltrim（Patients.FirstName））+空格（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）作为引用，

dbo.GetLastStudydays（Encounter.PatientID，''，getdate（），''）as Days_since_last_study，

Encounter.EncounterID，Patients.PatientID，Facility.FacilityName as Lab

FROM

遇到LEFT OUTER JOIN PatientReferral ON（Encounter.EncounterID = PatientReferral.EncounterID）

LEFT OUTER JOIN人员参考（Refer.PersonID = PatientReferral.ReferringID）LEFT OUTER JOIN Facility ON（Encounter.EncounterLocation = Facility.FacilityID）

INNER JOIN患者ON（患者） .PatientID = Encounter.PatientID）

LEFT OUTER JOIN PatientEndPoint ON（Patients.PatientID = PatientEndPoint.PatientID）

WHERE（

dbo.GetLastStudydays（Encounter.PatientID，''，getdate（），''） > = 365 AND

（

Refer.Last_Name ='*'或

Refer.Last_Name ='*'

）AND

dbo.GetLastStudyresult（Encounter.PatientID，''，''）<> '否定'和

NOT（

PatientEndPoint.SurvivalStatus ='死'或

Patients.BirthDate< '01 / 01/1925 12 ：00：00 AM'或

Encounter.EncounterType喜欢'SBE％'或

Encounter.EncounterType喜欢'SBF％'或

遇到.EncounterType喜欢'SBG％'或

Encounter.EncounterType喜欢'SBB％'

）AND

dbo.GetLastStudydays（Encounter.PatientID， ''，getdate（），''）< 730

）



订购名称ASC，参考ASC

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

推荐答案

尝试使用GROUP BY而不是DISTINCT，请参阅此文章

https： //support.microsoft.com/en-us/kb/139444/ [ ^ ]

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

这篇关于使用SQL构建数据报告时尝试消除重复的条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！