一个计算时间的SQL语句
现在有一个表(如下),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;
go
create table #temp( [name] varchar(100), [lastName] varchar(100), [planCloseDays] INT);
insert #temp
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'
--用CTE递归最方便:
;WITH cte AS
(
select name,lastname,planCloseDays,planCloseDate=CONVERT(CHAR(10),DATEADD(DAY,planCloseDays,GETDATE()),120) from #temp WHERE lastName = '-1'
UNION ALL
SELECT b.name, b.lastname, b.planCloseDays, planCloseDate = CONVERT(CHAR(10),DATEADD(DAY,B.planCloseDays,A.planCloseDate),120)
FROM cte A
INNER JOIN #temp B
ON a.name = b.lastname
)
SELECT name,lastname,planCloseDays,planCloseDate FROM cte
/*
namelastnameplanCloseDaysplanCloseDate
A1-102013-09-16
A2A112013-09-17
A3A212013-09-18
A4A372013-09-25
A5A372013-09-25
A6A372013-09-25
A7A6142013-10-09
*/