时间戳

首页 > 解决方案 > 如何确定合同是否跨多行有效？

sql - 如何确定合同是否跨多行有效？

问题描述

我正在尝试编写一些 SQL 来确定和分组合同是否在多个不同的行中有效。

这是场景，我有一个表存储有效期为 364 天的合同。有一条线用于合同的初始开始，然后有一条用于续订的线。当合同过期或关闭时，还有另一条线用于关闭。有两种类型的合同不重叠。如果此人签订另一份合同，则旧合同将被关闭。合同不是唯一标识的，但人们有唯一的 ID。

以下是我查看数据的内容。

SELECT CA.ConDate, CA.IsCon, DATEADD(MILLISECOND, 10 * CA.IsCon, CA.ConDate), CA.RenewalStatus, CA.PersonID
FROM dbo.ConActivity CA
WHERE CA.RenewalStatus IN ( 1, 2 )
      AND CA.PersonID = '254915C5-2F30-40C9-B7E9-0239E6CBE27B'
ORDER BY DATEADD(MILLISECOND, 10 * CA.IsCon, CA.ConDate)

这将返回以下结果：

ConDate                 IsCon  (No column name)         RenewalStatus   PersonID
2018-01-12 00:00:00.000 0      2018-01-12 00:00:00.000  1               254915C5-2F30-40C9-B7E9-0239E6CBE27B
2018-03-30 00:00:00.000 0      2018-03-30 00:00:00.000  2               254915C5-2F30-40C9-B7E9-0239E6CBE27B
2018-06-26 00:00:00.000 0      2018-06-26 00:00:00.000  1               254915C5-2F30-40C9-B7E9-0239E6CBE27B
2018-07-26 00:00:00.000 0      2018-07-26 00:00:00.000  1               254915C5-2F30-40C9-B7E9-0239E6CBE27B
2019-07-02 00:00:00.000 0      2019-07-02 00:00:00.000  1               254915C5-2F30-40C9-B7E9-0239E6CBE27B
2019-10-29 00:00:00.000 0      2019-10-29 00:00:00.000  2               254915C5-2F30-40C9-B7E9-0239E6CBE27B
2019-10-29 00:00:00.000 1      2019-10-29 00:00:00.010  1               254915C5-2F30-40C9-B7E9-0239E6CBE27B

RenewalStatus：1 = 已更新（或已打开），2 = 已关闭

从数据中可以看出，合约可以先开后关一段时间（即一个时间段），然后在同一合约类型下开仓续约几次，再转换为另一种合约类型（即另一时间） period)，然后以新的合约类型打开一个新的合约，并保持打开状态，直到出现关闭的 RwnewalStatus (= 2)。（无列名）只是我将行以正确的顺序排列以及我试图用来确定时间范围的一种方法。

我想要每个合同有效期每人一行，在这种情况下，我希望看到：

ConDate                 IsCon  ConEndDate               PersonID
2018-01-12 00:00:00.000 0      2018-03-30 00:00:00.000  254915C5-2F30-40C9-B7E9-0239E6CBE27B
2018-06-26 00:00:00.000 0      2019-10-29 00:00:00.000  254915C5-2F30-40C9-B7E9-0239E6CBE27B
2019-10-29 00:00:00.000 1      NULL                     254915C5-2F30-40C9-B7E9-0239E6CBE27B

你将如何实现这个结果？

标签： sqlsql-server-2012

解决方案

假设您的 DBMS 支持Windowed Aggregates：

with cte as 
 (
    SELECT CA.ConDate, CA.IsCon, 
       DATEADD(MILLISECOND, 10 * CA.IsCon, CA.ConDate) as col,
       CA.RenewalStatus, CA.PersonID,
       -- assign the same value to all rows of 1 followed by 2 using a Cumulative Sum
       sum(case when RenewalStatus = 2 then 1 else 0 end)
       over (partition by PersonID
             order by ConDate desc, RenewalStatus
             rows unbounded preceding) as grp
    FROM tab CA
    WHERE CA.RenewalStatus IN ( 1, 2 )
          AND CA.PersonID = '254915C5-2F30-40C9-B7E9-0239E6CBE27B'
 )
select min(ConDate), max(IsCon),
    case when max(RenewalStatus) = 2 then max(ConDate) end, PersonID
from cte
-- now aggregate over the person and the group's value
group by PersonId, grp

推荐阅读