在报告中禁止不相邻的重复项 [英] 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
...

由于患者按 Score 在 Name 之前分类，因此报告针对每位患者提供多个条目，条目不总是相邻，我不能使用上一个或下一个来抑制它们。要解决此问题，我只希望根据评估日期字段显示每位患者的最新条目，同时保持上述顺序。

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为每个记录，基于patientID和有序由评估日期 - 最近的日期将具有最低的数字（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).

如果这不工作，让我知道并提供一些表的详细信息 - 设施表是否应该作为起点？每个患者的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屋！