现在有一个表(如下),name是流程名称,lastName是上一步流程的名称,planCloseDays是流程计划完成所需的时间(从上一流程结束后开始计算),planCloseDate是计划关闭的日期(通过上一流程的planCloseDate和当前流程的planCloseDays计算得出)
P.S.如果lastName='-1',则planCloseDate为DATEADD(D,planCloseDays,GETDATE())
name lastName planCloseDays planCloseDate
------------------------------------------
A1 -1 0
A2 A1 1
A3 A2 1
A4 A3 7
A5 A3 7
A6 A3 7
A7 A6 14
请问怎么计算出每一步的planCloseDate
------解决方案--------------------
WITH a1 (name,lastName,planCloseDays) AS
(
SELECT 'A1','-1',0 UNION ALL
SELECT 'A2','A1',1 UNION ALL
SELECT 'A3','A2',1 UNION ALL
SELECT 'A4','A3',7 UNION ALL
SELECT 'A5','A3',7 UNION ALL
SELECT 'A6','A3',7 UNION ALL
SELECT 'A7','A6',14
)
,a2 AS
(
SELECT name,lastName,planCloseDays FROM a1
UNION ALL
SELECT a.name,b.lastName,b.planCloseDays
FROM a1 a
INNER JOIN a2 b ON a.lastName=b.Name
)
,a3 AS
(
SELECT name,SUM(planCloseDays) planCloseDays
FROM a2
GROUP BY name
)
SELECT a.*,DATEADD(dd,b.planCloseDays,GETDATE()) planCloseDate
FROM a1 a
INNER JOIN a3 b ON a.name=b.name
------解决方案--------------------
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;