时间戳

首页 > 解决方案 > 过滤最大有效约会

sql - 过滤最大有效约会

问题描述

我有下面的查询让我得到至少有两行COVERAGE_ELECT = 'E'并且计划类型为 49 的 EMPLID。

WITH Coverage_CTE (EMPLID, COVERAGE_ELECT)
 AS
  (
  SELECT EMPLID, COUNT(COVERAGE_ELECT) 
  FROM PS_SAVINGS_PLAN
  WHERE COVERAGE_ELECT = 'E'
  AND PLAN_TYPE = '49'
  GROUP BY EMPLID
  HAVING COUNT(COVERAGE_ELECT) > 1 
  )
, Emp_CTE (EMPLID)
AS
(
  select distinct EMPLID from Coverage_CTE
)
  SELECT SP.EMPLID, SP.PLAN_TYPE, SP.EFFDT, SP.COVERAGE_ELECT, SP.SAVINGS_COVRG, SP.FLAT_DED_AMT, SP.PCT_GROSS
  FROM PS_SAVINGS_PLAN as SP
  WHERE SP.EMPLID IN (Select EMPLID from Emp_CTE)
  AND SP.COVERAGE_ELECT = 'E'
  AND PLAN_TYPE = '49'

这似乎工作正常，但是我想通过仅包含EMPLID有效日期大于上面选择的“E”行的最大值的EFFDT行的行来进一步过滤结果，即“W”或“T”。

上面的查询当前有EMPLID5423H 的行（因为你看到的这个人有超过 1COVERAGE_ELECT = 'E'行 (3)）。但是，您会看到此人有两行COVERAGE_ELECT等于 T 和 W，其中EFFDT' 大于最大EFFDT“E”行的生效日期 (2018-10-01)。

EMPLID      PLAN_TYPE    EFFDT         COVERAGE_ELECT
5423H       49           2013-01-01    W
5423H       49           2015-10-02    E
5423H       49           2015-10-09    T
5423H       49           2016-09-29    E
5423H       49           2018-10-01    E
5423H       49           2018-10-07    T
5423H       49           2018-11-14    W

所以你在这个例子中看到了这两种情况 - 1. 这个 EMPLID 有一行，其 'T' 大于 (2018-10-07) 最大 EFFDT 'E' 行 (2018-10-01) 和 2. 他们有一排 'W' 大于 (2018-11-14) 最大 EFFDT 'E' 行。如果其中任何一种情况为真，那么我不希望它们导致我上面的查询。我希望将此附加逻辑构建到上述查询中。我希望这是有道理的..

标签： sqlsql-server-2014

解决方案

根据您的叙述，可以根据简单的条件聚合来回答：

WITH Coverage_CTE AS
 (
   SELECT EMPLID 
     ,Count(CASE WHEN COVERAGE_ELECT IN ('E') THEN effdt end) AS cntE
     ,Max(CASE WHEN COVERAGE_ELECT IN ('T', 'W') THEN effdt end) AS maxT
     ,Max(CASE WHEN COVERAGE_ELECT IN ('E') THEN effdt end) AS maxE

   FROM @PS_SAVINGS_PLAN
   WHERE PLAN_TYPE = '49'
   GROUP BY EMPLID
 )
SELECT EMPLID FROM Coverage_CTE
WHERE 
   -- at least two rows with COVERAGE_ELECT = 'E'       
   cntE > 1

   -- where there isn't a row for the EMPLID with an effective date that is greater
   -- than the max EFFDT of the 'E' rows selected above that is either a 'W' or a 'T'
  AND (maxT < maxE OR maxT IS NULL)

推荐阅读