代码之家 › 专栏 › 技术社区 › CodeMonkey

基于记录修改日期和活动标志创建开始和结束日期

sql-server

CodeMonkey · 技术社区 · 5 年前

我正在尝试基于具有主键ContactID、ModificationDate和StateCode的表创建开始和结束日期(生效日期)。ModificationDate表示记录的输入或更新时间,StateCode表示记录是启用还是禁用。

我试图找出每个联系人记录的开始(StateCode=0)和结束(StateCode=1)日期,但无法完全搞定查询。

CREATE TABLE Contact(
  ContactID INTEGER,
  StateCode INTEGER,
  ModifiedOn Datetime)

  INSERT INTO Contact
  SELECT 1, 0, '7/1/2019' UNION
  SELECT 1, 0, '7/2/2019' UNION
  SELECT 1, 1, '7/3/2019' UNION
  SELECT 1, 0, '7/4/2019' UNION
  SELECT 1, 0, '7/5/2019' UNION
  SELECT 1, 1, '7/6/2019' UNION
  SELECT 1, 0, '7/7/2019' UNION
  SELECT 1, 0, '7/8/2019'

http://sqlfiddle.com/#!18/e8aca/45

根据我的示例中StateCode的变化,我希望看到3条记录。

ContactID, StartDate, EndDate, ActiveFlag
1, 7/1/2019, 7/3/2019, 0
1, 7/4/2019, 7/6/2019, 0
1, 7/7/2019, NULL, 1

我正在验证的潜在解决方案

WITH CTE AS (
SELECT
  LAG(StateCode,1,1) OVER (PARTITION BY ContactID ORDER BY ModifiedOn) AS IsStart
  , StateCode AS IsEnd
  , ContactID
  , StateCode
  , ModifiedOn  
FROM Contact       
), CTE2 AS(
SELECT 
  ContactID
  , IsStart
  , IsEnd  
  , ModifiedOn  
  , DENSE_RANK() OVER (PARTITION BY ContactID ORDER BY CASE WHEN IsStart = 1 THEN ModifiedOn END) AS StartTest2
  , DENSE_RANK() OVER (PARTITION BY ContactID ORDER BY CASE WHEN IsEnd = 1 THEN ModifiedOn END) AS EndTest2
FROM CTE
WHERE IsStart = 1 OR IsEnd = 1
)
SELECT 
  Start.ContactID  
  , Start.ModifiedOn AS StartDate
  , EndDates.ModifiedOn AS EndDate
FROM CTE2 AS Start
   LEFT JOIN CTE2 AS EndDates
     ON Start.ContactID = EndDates.ContactID
     AND Start.StartTest2 = EndDates.EndTest2
WHERE Start.StartTest2 <> 1
ORDER BY Start.ModifiedOn

0 回复 | 直到 5 年前

Tab Alleman 5 年前

您可以使用LAG()或self join来获取对前一行的引用。

如果当前行 StateCode 为0,并且前一行为NULL(第一行)或1,则此行的日期为“StartDate”。如果相反的是真的,那么它是一个“结束日期”。如果两行都有相同的 ,则忽略该行。

CodeMonkey 5 年前

WITH CTE AS (
SELECT
    CASE WHEN StateCode = 1 THEN 0 ELSE (LAG(StateCode,1,1) OVER (PARTITION BY ContactID ORDER BY ModifiedOn ASC)) END AS IsStartDate
  , CASE WHEN (LAG(StateCode,1,NULL) OVER (PARTITION BY ContactID ORDER BY ModifiedOn ASC)) IS NULL OR (LAG(StateCode,1,NULL) OVER (PARTITION BY ContactID ORDER BY ModifiedOn ASC)) = 1 THEN 0 ELSE StateCode END AS IsEndDate
  , ContactID
  , StateCode
  , ModifiedOn  
FROM Contact       
), CTE2 AS(
SELECT 
  ContactID
  , IsStartDate
  , IsEndDate  
  , ModifiedOn  
  , CASE WHEN IsStartDate = 0 THEN -1 ELSE DENSE_RANK() OVER (PARTITION BY ContactID ORDER BY CASE WHEN IsStartDate = 1 THEN ModifiedOn END ASC) END AS StartRank
  , DENSE_RANK() OVER (PARTITION BY ContactID ORDER BY CASE WHEN IsEndDate = 1 THEN ModifiedOn END ASC) AS EndRank
FROM CTE
WHERE IsStart = 1 OR IsEnd = 1
)
SELECT 
  Start.ContactID  
  , Start.ModifiedOn AS StartDate
  , EndDates.ModifiedOn AS EndDate
FROM CTE2 AS Start
   LEFT JOIN CTE2 AS EndDates
     ON Start.ContactID = EndDates.ContactID
     AND Start.StartRank = EndDates.EndRank
WHERE Start.StartRank <> -1
ORDER BY Start.ModifiedOn