Post reply

Relative Scheduling - How to calculate subsequent Dates

  • September 7, 2012 at 9:30 am

    #281234

    I am creating a task scheduling application, where for a given job a number of tasks are necessary. Some tasks due date, is relative to another task.

    For Example, if the job was "Build Car", then the "Add Engine" task would be relative to "Build Chassis" and you may say that it was due 2 days after "Build Chassis". The next task may be "Add Wheels", which would occur say 4 days after "Build Chassis".

    However, some tasks are relative to others, so let's say "Road Test" was perhaps a relative child to "Add Wheels". In others words we want to plan a road test, but that is a relative dependency on "Add Wheels"

    I have a table of Tasks based on the following:

    CREATE TABLE TaskList

    (TaskID int,

    RelativeTaskID INT,

    RelativeOffSet INT, TaskName VARCHAR(100))

    INSERT INTO dbo.TaskList ( TaskID ,RelativeTaskID ,RelativeOffSet , TaskName) VALUES ( 1 , 0,0,'Build Chassis')

    INSERT INTO dbo.TaskList ( TaskID ,RelativeTaskID ,RelativeOffSet , TaskName) VALUES ( 2 , 1,2,'Add Engine')

    INSERT INTO dbo.TaskList ( TaskID ,RelativeTaskID ,RelativeOffSet , TaskName) VALUES ( 3 , 1,4,'Add Wheels')

    INSERT INTO dbo.TaskList ( TaskID ,RelativeTaskID ,RelativeOffSet , TaskName) VALUES ( 4 , 3,1,'Road Test')

    So to keep it simple in our example, the table would look like

    TaskID RelativeTaskID RelativeOffSet TaskName

    ----------- -------------- --------------

    1 0 0 Build Chassis

    2 1 2 Add Engine

    3 1 4 Add Wheels

    4 3 1 Road Test

    So, the question is, if I have a "D-Day", say 1/Nov/2012, how can I get a calculation, for each task of it's planned Date, taking into account the relativity.

    In the example, I cannot get a "Road Test" date, until I have calculated the "Add Wheels" date.

    I think the answer may be a CTE, but I cannot see how to get there.

    The alternative is a temp table and iterations through that

    Any ideas?

  • September 7, 2012 at 10:18 am

    #1535157

    Does this thread helps you?

    http://www.sqlservercentral.com/Forums/Topic1355365-391-1.aspx

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • September 7, 2012 at 1:37 pm

    #1535264

    that is perfect, I spent an hour trying different search phrases to see if there was an answer

    thank you very much

    Andrew

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply