我得到了一张#a表,如下所示:
ID | TYPE_ID | CREATED_DT
============================
001 | 111 | 2019-08-28
001 | 111 | 2018-08-12
001 | 111 | 2017-08-23
001 | 111 | 2016-08-14
001 | 111 | 2015-08-17
001 | 111 | 2014-08-11
001 | 112 | 2019-05-31
001 | 112 | 2018-05-28
我希望得到如下最终输出:
ID | TYPE_ID | CREATED_DT
============================
001 | 111 | 2019-08-28
001 | 111 | 2018-08-12
001 | 111 | 2017-08-23
001 | 111 | 2016-08-14
001 | 111 | 2015-08-17
001 | 111 | 2014-08-11
001 | 112 | 2019-05-31
001 | 112 | 2018-05-28
001 | 112 | 2017-05-31 --Predict YEAR end dates if not available
001 | 112 | 2016-05-31
001 | 112 | 2015-05-31
001 | 112 | 2014-05-31
如果没有可用的日期,则最终结果集应根据每个TYPE_ID预测最多6个月的结束日期(TYPE_ID = 112仅具有2个可用日期)。我确定我们可以使用DATEADD和DATEDIFF函数来预测日期,但是据我所知有点复杂。有帮助吗?
查询我正在尝试的信息,但不完全是
select *,ROW_NUMber() OVER(PARTITION BY ID,TYPE_ID ORDER BY CREATED_DT DESC) AS RN
INTO #B
from #a;
;WITH CTE(ID,TYPE_ID,CREATED_DT,RN)
AS(
SELECT
ID,RN
FROM #B
WHERE RN = 1 --Instead of RN = 1 I would like to get this till all
--available dates,so that I can go to recursive part for
--predicting non-available dates
UNION ALL
SELECT
A.ID,A.TYPE_ID,DATEADD(yy,-1,CTE.CREATED_DT)AS CREATED_DT,CTE.RN +1 AS RN
FROM #B AS A
INNER JOIN CTE ON CTE.ID = A.ID
AND CTE.TYPE_ID = A.TYPE_ID
AND CTE.RN < 6
AND A.RN = 1
)