使用每个策略的第一次记录以及随后的事务在原始策略的90天内创建一个指示符。

Question

TL/DR:每个策略都有许多事务类型，都以"N“开头，因为这是新的业务事务。R=更新，通常每年跟踪。"C“表示策略被取消，而"U”表示它被恢复，从而取消取消。我需要知道，对于每一个保单，在N记录后90天内，业务是否被取消(0)，或通过没有取消或撤销在该90天窗口(1)内也发生的取消。

与这个非常相似的问题：SQL Server: Find records with closest Date to CurrentDate based on conditions

对于4个策略(ID)，我的数据将如下所示。

Trs | Id | Effective_Dt  | Expiration_DT
N      |   01      |   2018-01-08 | 2018-01-23
C      |   01      |   2018-01-23 | 2018-02-03 
U      |   01      |   2018-02-03 | 2019-01-08 
R      |   01      |   2019-01-08 | 2020-01-08
R      |   01      |   2020-01-08 | 2021-01-08
N      |   02      |   2019-10-10 | 2019-12-01
C      |   02      |   2019-12-01 | NULL
N      |   03      |   2017-06-10 | 2017-11-01
C      |   03      |   2017-11-01 | NULL
N      |   04      |   2017-06-10 |   2017-07-01
C      |   04      |   2017-07-01 |   2017-11-01
U      |   04      |   2017-11-01 |   NULL

每个记录都是策略中的一个新事务，其中写入了N=new业务，C=已取消，U=Reinstated (撤消取消)，R=更新

有效期通常从续订的生效日期起一年，但在取消的情况下，到期日期是新交易的生效日期。

对于这个问题，我特别关注N，C和U事务；但是我包括了R，所以您可以了解数据的样子。我需要知道的是，哪种策略(0 (如果取消)或1指示符(如果保留)在N事务类型的90天内具有C事务类型.在同一时期内没有被U所取代。

实例/结果：

Id      |   Retained      
01      |     1    
02      |     0 
03      |     1   
04      |     0  

详细信息：

• For policy 01 N发生在2018-01-08.从那时起90天将是2018-04-08。2018-01-23年的C记录于2018-02-03年被取消；这一记录在90天的范围内。因此，这个政策将得到1的保留。
• 政策02 N发生在2019年-10-10年.从那时起90天是2020年-01-08天。2019年-12-01年的C记录没有被撤销.因此，这一政策将得到0的canceled.
• Policy 03 N发生在2017年-06-10.2017年-11-01年的C记录发生在90天之后.因此，这一政策将得到1为retained.
• Policy 04，N发生在2017-06-10.2017年-07-01年的C记录发生在90天之前，然后在2017年-11-01年被取消--但这是在90天之后。因此，此策略将获得0.

。

我希望这不是太糟糕的要求..。但基本上，取N事务的日期作为每个策略的日期，将其与在N的90天内发生的最后U或C事务进行比较，如果是C，0，否则是1。

Answer 1

我知道我的答案了。创建了一个CTE：

 NB_CANCELS AS (
 SELECT 
 CONCAT(POL_SYMBOL_CD,POL_NBR) NB_CANCEL_ID
,EFFECTIVE_DT
,EFFECTIVE_TYPE_CD
,HISTORY_VLD_NBR
,PLN_EXP_DT
,EXPIRATION_TYPE_CD
,EXPIRATION_DT


 FROM  (SELECT *,(SELECT DATEADD(D,90,OGN_EFF_DT) -- Find NB record and add 90 Days
                FROM  Exceed_Reporting.XCD.POLICY_TAB SUB
               WHERE  SUB.EFFECTIVE_TYPE_CD='N' --- NB only 
               AND    (SUB.EXPIRATION_TYPE_CD!='7' OR EXPIRATION_TYPE_CD IS NULL)
               AND    SUB.POLICY_ID           = PT.POLICY_ID
               AND    SUB.QUOTE_SEQUENCE_NBR   = 0
              ) NB90DAY
              FROM Exceed_Reporting.XCD.POLICY_TAB pt) PT1
 WHERE EFFECTIVE_TYPE_CD in ('5','3','s','p') -- Any cancel status
AND (EXPIRATION_TYPE_CD!='7' OR EXPIRATION_TYPE_CD IS NULL)
AND POL_SYMBOL_CD in ('HO','DP')
AND RIGHT(policy_ID,4)!='SAVE' 
AND STATUS_CD='A' 
AND pt1.EFFECTIVE_DT<=NB90DAY
AND (pt1.EXPIRATION_DT>=NB90DAY
OR PT1.EXPIRATION_DT IS NULL)
,

然后在基本查询中创建以下Case语句：

 , CASE     WHEN pt.EFFECTIVE_TYPE_CD!='N' THEN NULL
            WHEN CONCAT(PT.POL_SYMBOL_CD,PT.POL_NBR) IN (SELECT NB.NB_CANCEL_ID FROM NB_CANCELS nb)
            THEN 0  
            else 1 
END AS NB_90D_PERSISTENCE  



        FROM (SELECT *,DATEADD(dd,90,EFFECTIVE_DT) NB_90DAY
                    FROM POLICY_TAB 
                where    EFFECTIVE_TYPE_CD in ('N','R')

                ) PT

Answer 2

如果对你有用的话，试试这个。

  Select id,
   max(Case when  trs='C' and
      Expiration_DT<=min(Effective_Dt) 
      +90
    Then 1 else 0 end) as indicator
    from table  
   group by id 

Answer 3

您可以分两个步骤使用窗口函数:首先使用窗口min()获取每个策略的第一个'N‘的日期，然后执行一个条件last_value()来恢复最后一个U或C事务的类型。

select distinct 
    id,
    case 
        last_value(
            case 
                when coalesce(expiration_dt, effective_dt) <= dateadd(day, 90, n_effective_dt)
                and trs in ('C', 'U')
            then trs
            end
        ) over(partition by id order by effective_dt)
        when 'U' then 1
        when 'C' then 0
    end retained
from (
    select
        t.*,
        min(case when trs = 'N' then effective_dt end) over(partition by id) n_effective_dt
    from mytable t
) t