每当毕业更改(促销)时，交易SQL如何标记一个人_ID

Question

我想在晋升时创建一列以标记为标志(等级更改(，如以下示例：

+-----------+------------+------------+-------+------+
| PERSON_ID | DATE_START |  DATE_END  | GRADE | Flag |
+-----------+------------+------------+-------+------+
|       614 | 12/12/2012 | 12/12/2013 |     3 |    0 |
|       614 | 12/12/2013 | 12/31/2013 |     3 |    0 |
|       614 | 1/1/2014   | 5/31/2015  |     2 |    1 |
|       616 | 2/1/2009   | 12/31/2011 |     4 |    0 |
|       616 | 1/1/2012   | 12/31/2012 |     4 |    0 |
|       616 | 1/1/2013   | 12/31/2016 |     2 |    1 |
|       616 | 1/1/2017   | 12/31/4712 |     1 |    1 |
|       617 | 2/26/2012  | 12/31/2012 |     5 |    0 |
+-----------+------------+------------+-------+------+

Answer 1

如果要新等级有效的日期：http：//sqlfiddle.com/#！18/ba18c/3/0

，请查看此方法。

或这是您想要旧等级标记的最后日期：http：//sqlfiddle.com/#！18/ba18c/4/0

在这里，第二种方法更详细：

DECLARE @t TABLE(
  PERSON_ID int
  ,DATE_START date
  ,DATE_END date
  ,GRADE int
 )
 INSERT INTO @t
 VALUES
 (414         ,'12/12/2012'  ,'12/12/2013'  ,3)
,(414         ,'12/12/2013'  ,'12/31/2013'  ,3)
,(414         ,'1/1/2014'    ,'5/31/2015'   ,2)
,(616         ,'2/1/2009'    ,'12/31/2011'  ,4)
,(616         ,'1/1/2012'    ,'12/31/2012'  ,4)
,(616         ,'1/1/2013'    ,'12/31/2016'  ,2)
,(616         ,'1/1/2017'    ,'12/31/4712'  ,1)
SELECT *
      ,CASE
         WHEN grade != ISNULL(LEAD(grade) OVER (PARTITION BY person_id ORDER BY date_start), grade) THEN 1
         ELSE 0
       END AS Flag
  FROM @t
  ORDER BY person_id, date_start

诀窍是将当前等级与同一人的下一个成绩进行比较。这是由LEAD()和PARTITION BY完成的。为了按正确的顺序进行比较，您必须添加ORDER BY。最后但并非最不重要的一点：为了正确考虑当前人员的最后记录，需要IS NULL -LEAD将提供零，如果没有找到当前用户的记录。

Answer 2

尝试一下。

SELECT * INTO #YourTable FROM
(
SELECT  614 PERSON_ID, '12/12/2012' DATE_START, '12/12/2013' DATE_END, 3 GRADE UNION ALL
SELECT  614 , '12/12/2013' , '12/31/2013' , 3 UNION ALL
SELECT  614 , '1/1/2014'   , '5/31/2015'  , 2 UNION ALL
SELECT  616 , '2/1/2009'   , '12/31/2011' , 4 UNION ALL
SELECT  616 , '1/1/2012'   , '12/31/2012' , 4 UNION ALL
SELECT  616 , '1/1/2013'   , '12/31/2016' , 2 UNION ALL
SELECT  616 , '1/1/2017'   , '12/31/4712' , 1 UNION ALL
SELECT  617 , '2/26/2012'  , '12/31/2012' , 5
) AS A
-- *** Query *** ---
;WITH CTE AS (
SELECT *,LAG(GRADE)OVER(PARTITION BY PERSON_ID ORDER BY (SELECT NULL))LAG FROM  #YourTable
)
SELECT PERSON_ID , DATE_START ,  DATE_END  , GRADE,
CASE WHEN GRADE=ISNULL(LAG,GRADE)THEN 0 ELSE 1 END AS FLAG FROM CTE