如何避免在查询表时进行两次单独的传递？

The context of this problem are hospital visits. The logic is a bit convoluted but these are the requirements.

这个问题的背景是医院访问。逻辑有点复杂,但这些都是要求。

Table Visits contains the names and visitIDs for the patients.

表格访问包含患者的姓名和访问ID。

Table FilteredVisits contains certain visits that meet some criteria

Table FilteredVisits包含符合某些条件的特定访问

The report is run under a certain date range - this is important. Let's assume 1/1/2013 to 1/31/2013

该报告在特定日期范围内运行 - 这很重要。我们假设2013年1月1日至2013年1月31日

If a visit exists in FilteredVisits during that date range, the report shows a 1 for that visit. Else, it returns 0.

如果在该日期范围内FilteredVisits中存在访问,则报告会显示该访问的1。否则,它返回0。

However, if there exists an entry for that Patient (Name1) in FilteredVisits prior to the date range (before 1/1/2013 in our example), then ALL the visits for that patient need to be 1, regardless if the other visits don't exist in FilterVisits table.

但是,如果在日期范围之前的FilteredVisits中存在该患者(Name1)的条目(在我们的示例中为2013年1月1日之前),那么该患者的所有访问必须为1,无论其他访问是否参与不存在于FilterVisits表中。

PatientID and VisitID are both unique values.

PatientID和VisitID都是唯一值。

the report only outputs patients and visits within the date range

该报告仅在日期范围内输出患者和访问

See below for an example.

请参阅下面的示例。

Currently, the code is doing 2 separate updates (2 passes) to a temp table that is storing the results. One update to set the value to 0/1 for the date range selected and then another update where is searches everything prior to the begin date parameter. The FilteredVisits table can be large, is there a way to optimize this to be faster?

目前,代码正在对存储结果的临时表进行2次单独更新(2次传递)。一个更新将所选日期范围的值设置为0/1,然后另一个更新,其中搜索开始日期参数之前的所有内容。 FilteredVisits表可能很大,有没有办法优化这个更快?

Table1 All Patient Visits

表1所有患者就诊

PatientNameID VisitID
-------------------

PatientName1 P1Visit1
PatientName1 P1Visit2
PatientName1 P1Visit3
PatientName2 P2Visit1
PatientName3 P3Visit1
PatientName3 P3Visit2

Table2 Filtered Visits

表2过滤的访问次数

PatientNameID  VisitID  Date
-------------------------
PatientName1 P1Visit1    12/1/2012
PatientName1 P1Visit3    1/2/2013
PatientName3 P3Visit1    1/8/2013

The results:

Results
PatientName1 P1visit1   1
PatientName1 P1visit2   1 -- would be '0' but there was a visit by PatientName1 BEFORE the date range so set to 1
PatientName1 P1visit3   1
PatientName2 P2visit1   0
PatientName3 P3visit1   1
PatientName3 P3Visit2   0 -- this stays 0 because there is no entry of PatientName3 visit3 in table2 and no visit by PatientName3 prior to the selected date range

-- Edit: TempTable includes only the patients and visits for the selected date range

- 编辑:TempTable仅包括患者和所选日期范围的访问

-- First  pass
UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON fv.visitID = t.visitID 

-- Second pass      
UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON fv.PatientID = t.PatientID -- join on patientID to include all visits
        AND fv.visitDate < @BeginDate -- search only before date range

2 个解决方案

#1

In one pass:

一次通过:

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON
 (fv.PatientID = t.PatientID -- join on patientID to include all visits
    AND fv.visitDate < @BeginDate) -- search only before date range
 OR (fv.visitID = t.visitID)

Although, I would not mind two passes in this case as it does not appear to be a performance issue and it works either way. Up to the developer.

虽然,在这种情况下,我不介意两次通过,因为它似乎不是一个性能问题,它可以工作。由开发人员决定。

Or... are you looking for this:

或者......你在找这个:

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
INNER JOIN FilteredVisits fv ON fv.visitID = t.visitID
WHERE
 fv.PatientID = t.PatientID -- join on patientID to include all visits
    AND fv.visitDate < @BeginDate -- search only before date range

Upon review, I think you are looking for a LEFT JOIN between the Patients and Visits tables with summary information. SUM or COUNT or maybe a CASE WHEN VisitDate IS NULL THEN 0 ELSE 1 END with a GROUP BY PatientName.

经过审核,我认为您正在寻找患者和访问表之间的LEFT JOIN,其中包含摘要信息。 SUM或COUNT或可能是一个例子,当VisitDate为NULL时,则为1,结束时为GROUP BY PatientName。

#2

UPDATE t
SET MeasureIsTrue = 1
FROM TempTable t
LEFT OUTER JOIN FilteredVisits fv1 ON fv1.visitID = t.visitID
LEFT OUTER JOIN FilteredVisits fv2 ON fv2.PatientID = t.PatientID
WHERE fv1.visitID is not null
   OR fv2.visitDate < @BeginDate -- search only before date range

#1