Have a look at example code:
DECLARE @days TABLE(id INT IDENTITY(1,1), NumOfDays INT NULL, dueDate DATETIME)
INSERT INTO @days (dueDate)
SELECT '2013-06-01'
UNION ALL SELECT '2013-06-07'
UNION ALL SELECT '2013-06-10'
UNION ALL SELECT '2013-06-15'
UNION ALL SELECT '2013-06-19'
UNION ALL SELECT '2013-06-30'
UNION ALL SELECT '2013-07-01'
UNION ALL SELECT '2013-07-05'
UNION ALL SELECT '2013-07-15'
UNION ALL SELECT '2013-07-24'
UPDATE dst
SET NumOfDays = DATEDIFF(dd, dst.dueDate, src.CurrDate)
FROM @days AS dst INNER JOIN (
SELECT id-1 AS id, dueDate AS CurrDate
FROM @days
) AS src ON dst.id = src.id
SELECT id, NumOfDays, CONVERT(NVARCHAR(10),dueDate,121) AS dueDate
FROM @days
[EDIT #1]
Sorry for misunderstanding ;)
In below example i use
CTE[
^]
DECLARE @days TABLE(id INT IDENTITY(1,1), NumOfDays INT, dueDate DATETIME NULL)
INSERT INTO @days (NumOfDays, dueDate)
SELECT 6, '2013-06-01'
UNION ALL SELECT 3, NULL
UNION ALL SELECT 5, NULL
UNION ALL SELECT 4, NULL
UNION ALL SELECT 11, NULL
UNION ALL SELECT 1, NULL
UNION ALL SELECT 4, NULL
UNION ALL SELECT 10, NULL
UNION ALL SELECT 9, NULL
;WITH dates AS
(
SELECT id, NumOfDays, dueDate, DATEADD(dd,NumOfDays,dueDate) AS newDate
FROM @days
WHERE (dueDate IS NOT NULL) AND (NumOfDays IS NOT NULL)
UNION ALL
SELECT t1.id, t1.NumOfDays, t2.newDate AS dueDate, DATEADD(dd,t1.NumOfDays,t2.newDate) AS newDate
FROM @days AS t1 INNER JOIN dates AS t2 ON t1.id-1 = t2.id
WHERE (t1.dueDate IS NULL) AND (t1.NumOfDays IS NOT NULL)
)
UPDATE dst
SET dueDate = src.dueDate
FROM @days AS dst INNER JOIN dates AS src ON dst.id = src.id
SELECT id, NumOfDays, CONVERT(NVARCHAR(10),dueDate,121) AS dueDate
FROM @days
Result is the same ;)
1 6 2013-06-01
2 3 2013-06-07
3 5 2013-06-10
4 4 2013-06-15
5 11 2013-06-19
6 1 2013-06-30
7 4 2013-07-01
8 10 2013-07-05
9 9 2013-07-15
More about:
Recursive Queries Using Common Table Expressions[
^]
[/EDIT]