Recursive SQL settlement date

Question

I have a sql server table:

CREATE TABLE [Workflow].[MilestoneDate](
 [MilestoneDateId] [int] IDENTITY(1,1) NOT NULL,
 [SpecifiedDate] [datetime] NULL,
 [RelativeDays] [int] NULL,
 [RelativeMilestoneDateId] [int] NULL,
 CONSTRAINT [PK_MilestoneDate] PRIMARY KEY CLUSTERED 
(
 [MilestoneDateId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [Workflow].[MilestoneDate]  WITH CHECK ADD  CONSTRAINT [FK_MilestoneDate_MilestoneDate] FOREIGN KEY([RelativeMilestoneDateId])
REFERENCES [Workflow].[MilestoneDate] ([MilestoneDateId])
GO
ALTER TABLE [Workflow].[MilestoneDate] CHECK CONSTRAINT [FK_MilestoneDate_MilestoneDate]

and it has data that might look like this:

Id     Date                          RelDays RelId
49     2010-03-04 00:00:00.000       NULL    NULL
746    NULL                          6       46
747    NULL                          20      746
46     2010-02-18 00:00:00.000       NULL    NULL
48     2010-04-04 00:00:00.000       NULL    NULL
47     2010-05-04 00:00:00.000       NULL    NULL
748    NULL                          14      48

What I need to do is get the estimated date for each row, which is either the Date, if any, or the Date of the "parent" element (using RelId), or RelDays (which can be recursive).

So, for example, the settlement date Id 747 is 20 days + 6 days + 2010-02-18 and, therefore, 2010-03-16.

Answer 1

try this (uses CTE, which is only available on SQL Server 2005 and above):

DECLARE @YourTable table (Id int, Date datetime, RelDays int, RelId int)
INSERT @YourTable VALUES (49 ,'2010-03-04 00:00:00.000',NULL ,NULL)
INSERT @YourTable VALUES (746, NULL                    ,6    ,46)
INSERT @YourTable VALUES (747, NULL                    ,20   ,746)
INSERT @YourTable VALUES (46 ,'2010-02-18 00:00:00.000',NULL ,NULL)
INSERT @YourTable VALUES (48 ,'2010-04-04 00:00:00.000',NULL ,NULL)
INSERT @YourTable VALUES (47 ,'2010-05-04 00:00:00.000',NULL ,NULL)
INSERT @YourTable VALUES (748, NULL                    ,14   ,48)

;WITH R AS
(
    SELECT id,Date,RelDays,RelId  --get all parents
    FROM @YourTable
    WHERE RelId IS NULL
    UNION ALL    --recursive go through all children, adding the days on
    SELECT
        y.id,r.Date+y.RelDays,y.RelDays,y.RelId
        FROM @YourTable y
            INNER JOIN R ON y.RelId=r.Id
)
select * from R

CONCLUSION:

id          Date                    RelDays     RelId
----------- ----------------------- ----------- -----------
49          2010-03-04 00:00:00.000 NULL        NULL
46          2010-02-18 00:00:00.000 NULL        NULL
48          2010-04-04 00:00:00.000 NULL        NULL
47          2010-05-04 00:00:00.000 NULL        NULL
748         2010-04-18 00:00:00.000 14          48
746         2010-02-24 00:00:00.000 6           46
747         2010-03-16 00:00:00.000 20          746

(7 row(s) affected)