日期:2014-05-17 浏览次数:20434 次
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;