抑制报表中的不相邻重复项 [英] Suppress Nonadjacent Duplicates in Report

问题描述

我的 Crystal Report 中的医疗记录按以下顺序排序:

Medical records in my Crystal Report are sorted in this order:

...
Group 1: Score [Level of Risk]
  Group 2: Patient Name
...

由于患者在 Name 之前按 Score 排序，因此报告会为每位患者提取多个分数不同的条目 - 由于重复条目并不总是相邻，因此我可以't 使用 Previous 或 Next 来抑制它们.为了解决这个问题，我只想根据 Assessment Date 字段为每位患者显示 最新条目 - 同时保持上述顺序.

Because patients are sorted by Score before Name, the report pulls in multiple entries per patient with varying scores - and since duplicate entries are not always adjacent, I can't use Previous or Next to suppress them. To fix this, I'd like to only display the latest entry for each patient based on the Assessment Date field - while maintaining the above order.

我确信可以使用自定义 SQL 命令来实现此行为，以仅提取每位患者的最新条目，但我自己未能成功创建该行为.我怎样才能完成这种复合排序?

I'm convinced this behavior can be implemented with a custom SQL command to only pull in the latest entry per patient, but have had no success creating that behavior myself. How can I accomplish this compound sort?

当前正在使用的 SQL 语句:

Current SQL Statement in use:

SELECT "EpisodeSummary"."PatientID",
"EpisodeSummary"."Patient_Name",
"EpisodeSummary"."Program_Value"
"RiskRating"."Rating_Period",
"RiskRating"."Assessment_Date",
"RiskRating"."Episode_Number",
"RiskRating"."PatientID",
"Facility"."Provider_Name",

FROM (
  "SYSTEM"."EpisodeSummary"
  "EpisodeSummary"
  LEFT OUTER JOIN "FOOBARSYSTEM"."RiskAssessment" "RiskRating"
  ON (
    ("EpisodeSummary"."Episode_Number"="RiskRating"."Episode_Number")
    AND
    ("EpisodeSummary"."FacilityID"="RiskRating"."FacilityID")
  )
  AND
  ("EpisodeSummary"."PatientID"="RiskRating"."PatientID")
), "SYSTEM"."Facility" "Facility"

WHERE (
  "EpisodeSummary"."FacilityID"="Facility"."FacilityID"
)
AND "RiskRating"."PatientID" IS NOT NULL 

ORDER BY "EpisodeSummary"."Program_Value"

推荐答案

下面的 SQL 代码可能不完全正确，具体取决于表的结构.下面的代码假定重复风险评分"来自 RiskAssessment 表.如果这不正确，则可能需要更改代码.

The SQL code below may not be exactly correct, depending on the structure of your tables. The code below assumes the 'duplicate risk scores' were coming from the RiskAssessment table. If this is not correct, the code may need to be altered.

本质上，我们创建一个派生表并为每条记录创建一个 row_number，基于患者 ID 并按评估日期排序 - 最近的日期将具有最小的数字 (1).然后，在加入时，我们将结果集限制为仅选择记录 #1(每个患者都有自己的排名 #1).

Essentially, we create a derived table and create a row_number for each record, based on the patientID and ordered by the assessment date - The most recent date will have the lowest number (1). Then, on the join, we restrict the resultset to only select record #1 (each patient has its own rank #1).

如果这不起作用，请告诉我并提供一些表格详细信息 -- 是否应该以 Facility 表格作为起点?每个患者的 EpisodeSummary 中是否有多个条目?谢谢！

If this doesn't work, let me know and provide some table details -- Should the Facility table be the starting point? are there multiple entries in EpisodeSummary per patient? thanks!

SELECT es.PatientID
    ,es.Patient_Name
    ,es.Program_Value
    ,rrd.Rating_Period
    ,rrd.Assessment_Date
    ,rrd.Episode_Number
    ,rrd.PatientID
    ,f.Provider_Name
FROM SYSTEM.EpisodeSummary es
LEFT JOIN (
    --Derived Table retreiving highest risk score for each patient)
    SELECT PatientID
        ,Assessment_Date
        ,Episode_Number
        ,FacilityID
        ,Rating_Period
        ,ROW_NUMBER() OVER (
            PARTITION BY PatientID ORDER BY Assessment_Date DESC
            ) AS RN -- This code generates a row number for each record. The count is restarted for every patientID and the count starts at the most recent date.
    FROM RiskAssessment
    ) rrd
    ON es.patientID = rrd.patientid
        AND es.episode_number = rrd.episode_number
        AND es.facilityid = rrd.facilityid
        AND rrd.RN = 1 --This only retrieves one record per patient (the most recent date) from the riskassessment table 
INNER JOIN SYSTEM.Facility f
    ON es.facilityid = f.facilityid
WHERE rrd.PatientID IS NOT NULL
ORDER BY es.Program_Value

这篇关于抑制报表中的不相邻重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！