场景1:
Table as,
IF OBJECT_ID('TEMPDB..#RUN_ID') IS NOT NULL
DROP TABLE #RUN_ID
;WITH RUN_ID as (
SELECT 1 AS RUN_ID,1 AS EMP_ID, '1/1/2018' STARTDT, 'A' AS VALUE
UNION
SELECT 2 AS RUN_ID,1 AS EMP_ID, '2/1/2018' STARTDT, 'A' AS VALUE
UNION
SELECT 3 AS RUN_ID,1 AS EMP_ID, '12/1/2017' STARTDT, 'A' AS VALUE
UNION
SELECT 4 AS RUN_ID,1 AS EMP_ID, '3/1/2018' STARTDT, 'A' AS VALUE
UNION
SELECT 5 AS RUN_ID,1 AS EMP_ID, '2/1/2018' STARTDT, 'A' AS VALUE
)
SELECT * INTO #RUN_ID from RUN_ID
RUN_ID EMP_ID STARTDT VALUE
1 1 1/1/2018 A
2 1 2/1/2018 A
3 1 12/1/2017 A
4 1 3/1/2018 A
5 1 2/1/2018 A
RUN_ID是表中每天递增的价值。值列可以相同,也可以不同。需要导出STARTDT的结果,如下所示:
RUN_ID EMP_ID STARTDT VALUE
3 1 12/1/2017 A
5 1 2/1/2018 A
注意: RUN ID 5的最后一条记录覆盖了所有其他记录,其中2/1/2018记录在目标中且RUN ID 3的StartDt应该在结果中,因为它覆盖了先前的RUN ID StartDT
Scenario2:
RUN_ID EMP_ID STARTDT VALUE
1 1 1/1/2018 A
2 1 11/1/2017 A
3 1 12/1/2017 A
4 1 3/1/2018 A
5 1 2/1/2018 A
在这种情况下,结果应该是
RUN_ID EMP_ID STARTDT VALUE
2 1 11/1/2017 A
3 1 12/1/2017 A
5 1 2/1/2018 A
发布于 2018-06-08 05:14:38
如果OBJECT_ID('TEMPDB..#RUN_ID')不为NULL,则删除表#RUN_ID
;使用RUN_ID作为(
SELECT 1作为RUN_ID,1作为EMP_ID,CAST('1/1/2018‘作为日期) STARTDT,'A’作为值UNION SELECT 2作为RUN_ID,1作为EMP_ID,CAST('11/1/2017‘作为日期) STARTDT,'A’作为值UNION SELECT 3作为RUN_ID,1作为EMP_ID,CAST('12/1/2017‘作为日期) STARTDT,'A’作为值UNION SELECT 4作为RUN_ID,1作为EMP_ID,CAST('3/1/2018‘作为日期) STARTDT,'A‘AS值UNION SELECT 5 AS RUN_ID,1 AS EMP_ID,CAST('2/1/2018’AS DATE) STARTDT,'A‘AS值)
SELECT * INTO #RUN_ID from RUN_ID
SELECT * FROM ( SELECT *,LAG( STARTDT ) OVER (PARTITION BY EMP_ID ORDER BY RUN_ID DESC) LAG_DATE,CASE WHEN LAG( STARTDT ) OVER (PARTITION BY EMP_ID ORDER BY RUN_ID DESC)为NULL则为0 WHERE WHERE (STARTDT) OVER (PARTITION BY EMP_ID ORDER BY RUN_ID DESC) THEN 0 ELSE 1 END SCD_IND FROM ( SELECT *,RANK() OVER (PARTITION BY EMP_ID,STARTDT ORDER BY RUN_ID DESC) RN FROM #RUN_ID )A WHERE A.RN=1 )A WHERE SCD_IND=0
RUN_ID EMP_ID启动值RN LAG_DATE SCD_IND 5 1 2018-02-01 A 1 NULL 0 3 1 2017-12-01 A 1 2018-03-01 0 2 1 2017-11-01 A 1 2017-12-01 0
https://stackoverflow.com/questions/50615319
复制相似问题