选择具有回溯和未来日期的正确生效日期方案
场景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 ARUN_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回答 1
Stack Overflow用户
发布于 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
复制相似问题