选择具有追溯日期和将来日期的业务情景的正确生效日期 [英] Pick right Effective Dates with backdated and future dates scenario
问题描述
方案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是表中每天的增量值.VALUE列可以相同或不同.需要得出STARTDT的结果，如下所示:
RUN_ID is every day incremental value in the table. VALUE Column can be same or different. Need to derive the result for the STARTDT's as below:
RUN_ID EMP_ID STARTDT VALUE
3 1 12/1/2017 A
5 1 2/1/2018 A
注意:RUN ID 5的最后一条记录正在覆盖所有其他记录，其中2018年2月1日记录的StartDt将在目标中，而RUN ID 3应该在结果中，因为它将覆盖先前的RUN ID StartDT
Note: The last records of RUN ID 5 is over writing all other records, where StartDt of 2/1/2018 record to be in target and RUN ID 3 should be in result, as its over writing the previous RUN ID StartDT
方案2:
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
在这种情况下，结果应该是
In this case, result should be
RUN_ID EMP_ID STARTDT VALUE
2 1 11/1/2017 A
3 1 12/1/2017 A
5 1 2/1/2018 A
推荐答案
如果OBJECT_ID('TEMPDB ..#RUN_ID')不为空删除表#RUN_ID
IF OBJECT_ID('TEMPDB..#RUN_ID') IS NOT NULL DROP TABLE #RUN_ID
; RUN_ID为(
选择1 AS RUN_ID，1 AS EMP_ID，CAST('1/1/2018'AS DATE)STARTDT，'A'AS VALUE联盟SELECT 2 AS RUN_ID，1 AS EMP_ID，CAST('11/1/2017'AS DATE)STARTDT，'A'AS VALUE联盟SELECT 3 AS RUN_ID，1 AS EMP_ID，CAST('12/1/2017'AS DATE)STARTDT，'A'AS VALUE联盟SELECT 4 AS RUN_ID，1 AS EMP_ID，CAST('3/1/2018'AS DATE)STARTDT，'A'AS VALUE联盟选择5 AS RUN_ID，1 AS EMP_ID，CAST('2/1/2018'AS DATE)STARTDT，'A'AS VALUE)
SELECT 1 AS RUN_ID,1 AS EMP_ID, CAST('1/1/2018' AS DATE) STARTDT, 'A' AS VALUE UNION SELECT 2 AS RUN_ID,1 AS EMP_ID, CAST('11/1/2017' AS DATE) STARTDT, 'A' AS VALUE UNION SELECT 3 AS RUN_ID,1 AS EMP_ID, CAST('12/1/2017' AS DATE) STARTDT, 'A' AS VALUE UNION SELECT 4 AS RUN_ID,1 AS EMP_ID, CAST('3/1/2018' AS DATE) STARTDT, 'A' AS VALUE UNION SELECT 5 AS RUN_ID,1 AS EMP_ID, CAST('2/1/2018' AS DATE) STARTDT, 'A' AS VALUE )
从RUN_ID中选择* INTO #RUN_ID
SELECT * INTO #RUN_ID from RUN_ID
选择*从 (选择 *，LAG(STARTDT)结束(按EMP_ID排序或按RUN_ID DESC排序)LAG_DATE，当滞后(STARTDT)结束(按EMP_ID排序或按RUN_ID DESC排序)为NULL时，则为0当STARTDT<滞后(STARTDT)结束(按EMP_ID排序或按RUN_ID DESC排序)然后0 ELSE 1 END SCD_IND从 (选择 *，RANK()OVER(按EMP_ID分配，STARTDT按RUN_ID递减DESC)RN来自#RUN_ID) 一种在哪里A.RN = 1)WHERE SCD_IND = 0
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) IS NULL THEN 0 WHEN STARTDT < LAG(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 STARTDT VALUE RN LAG_DATE SCD_IND5 1 2018-02-01 A 1空03 1 2017-12-01 A 1 2018-03-01 02 1 2017-11-01 A 1 2017-12-01 0
RUN_ID EMP_ID STARTDT VALUE 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
这篇关于选择具有追溯日期和将来日期的业务情景的正确生效日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
