cteFilterAccounts AS (
SELECT [Encounter Number],
FROM cteGrouper
WHERE ProcGrouper=1
INTERSECT
SELECT [Encounter Number],
FROM cteGrouper
WHERE ContrastGrouper=1
)
基本上，它使用与 join 相同的逻辑。
您还可以尝试一种完全不同的方法，即分组，如下所示：
cteFilterAccounts AS (
SELECT [Encounter Number]
FROM cteGrouper
GROUP BY [Encounter Number]
HAVING MAX(ProcGrouper) = 1
AND MAX(ContrastGrouper) = 1
)
在这里，您选择 Grouper 属性的最大值（聚合）值均等于 1 的不同帐户。
I think you should redefine your cteFilterAccounts CTE. The thing is, your present code for that CTE returns the set where every individual row from cteGroup has both ProcGrouper=1andContrastGrouper=1. Based on the code for cteGroup, that can never be possible, because a row can have a [Feeder Key] belonging to no more than one of the two groups, so it's eitherProcGrouper=1orContrastGrouper=1, but not both. Therefore, the CTE returns nothing.
Logically, though, you seem to be searching for rows that have both attributes set on the global scale, i.e. within the period. One possible solution could be to use a join, like this:
cteFilterAccounts AS (
SELECT g1.[Encounter Number]
FROM (
SELECT DISTINCT [Encounter Number],
FROM cteGrouper
WHERE ProcGrouper=1
) g1
INNER JOIN (
SELECT DISTINCT [Encounter Number],
FROM cteGrouper
WHERE ContrastGrouper=1
) g2 ON g1.[Encounter Number] = g2.[Encounter Number]
)
This query joins all [Encounter Number] values with [Feeder Key] from one group that are found within the period, with a similar list where [Feeder Key] belongs to the other group. The matching values give you the resulting set.
Alternatively you could use INTERSECT:
cteFilterAccounts AS (
SELECT [Encounter Number],
FROM cteGrouper
WHERE ProcGrouper=1
INTERSECT
SELECT [Encounter Number],
FROM cteGrouper
WHERE ContrastGrouper=1
)
Basically, it uses the same logic as join.
You could also try a completely different approach, grouping, like this:
cteFilterAccounts AS (
SELECT [Encounter Number]
FROM cteGrouper
GROUP BY [Encounter Number]
HAVING MAX(ProcGrouper) = 1
AND MAX(ContrastGrouper) = 1
)
Here you are selecting distinct accounts that have both Grouper attributes' maximum (aggregated) values equal to 1.
发布评论
评论（1）
我认为您应该重新定义您的
cteFilterAccountsCTE。问题是，您当前的 CTE 代码返回一个集合，其中cteGroup中的每个单独行都具有ProcGrouper=1和< /em>ContrastGrouper=1。根据cteGroup的代码，这是不可能的，因为一行可以有一个[Feeder Key]，属于不超过一个两个组中的一个，所以它是ProcGrouper=1 或ContrastGrouper=1，但不能同时是两者。因此，CTE 不会返回任何内容。但从逻辑上讲，您似乎正在搜索在全局范围内（即在时间段内）设置了这两个属性的行。一种可能的解决方案是使用联接，如下所示：
此查询将一段时间内找到的一组中的所有
[Encounter Number]值与[Feeder Key]联接起来，具有类似的列表，其中[Feeder Key]属于另一个组。匹配值将为您提供结果集。或者，您可以使用 INTERSECT：
基本上，它使用与 join 相同的逻辑。
您还可以尝试一种完全不同的方法，即分组，如下所示：
在这里，您选择
Grouper属性的最大值（聚合）值均等于 1 的不同帐户。I think you should redefine your
cteFilterAccountsCTE. The thing is, your present code for that CTE returns the set where every individual row fromcteGrouphas bothProcGrouper=1andContrastGrouper=1. Based on the code forcteGroup, that can never be possible, because a row can have a[Feeder Key]belonging to no more than one of the two groups, so it's eitherProcGrouper=1orContrastGrouper=1, but not both. Therefore, the CTE returns nothing.Logically, though, you seem to be searching for rows that have both attributes set on the global scale, i.e. within the period. One possible solution could be to use a join, like this:
This query joins all
[Encounter Number]values with[Feeder Key]from one group that are found within the period, with a similar list where[Feeder Key]belongs to the other group. The matching values give you the resulting set.Alternatively you could use INTERSECT:
Basically, it uses the same logic as join.
You could also try a completely different approach, grouping, like this:
Here you are selecting distinct accounts that have both
Grouperattributes' maximum (aggregated) values equal to 1.