Recursively calculate date intervals
-
CREATE TABLE #tblTasks
(
TaskID int,
Task varchar(50),
BaseTask bit,
BaseTaskID int,
BaseTaskInterval int
)
GO
INSERT INTO #tblTasks (TaskID, Task, BaseTask, BaseTaskID, BaseTaskInterval)
SELECT 1, 'Task 1 - 2 days after Task 4', 0, 4, 2 UNION ALL
SELECT 2, 'Task 2 - 2 days before Task 4', 0, 4, -2 UNION ALL
SELECT 3, 'Task 3 - 2 days after Task 2', 0, 2, 2 UNION ALL
SELECT 4, 'Task 4 - the base Task', 1, 4, 0 UNION ALL
SELECT 5, 'Task 5 - 2 days after Task 3', 0, 3, 2 UNION ALL
SELECT 6, 'Task 6 - 7 days after Task 1', 0, 1, 7 UNION ALL
SELECT 7, 'Task 7 - 3 days before Task 8', 0, 8, -3 UNION ALL
SELECT 8, 'Task 8 - 1 day after Task 2', 0, 2, 1 UNION ALL
SELECT 9, 'Task 9 - undated Task', 0, null, null UNION ALL
SELECT 10, 'Task 10 - 6 days before Task 7', 0, 7, -6
GO
--SELECT * FROM #tblTasks
DROP Table #tblTasks
A list of tasks. One of them is the Bask Task - Task 4 in this case.
I need to calculate the dates that the tasks occur. So, I start with a Base Date - any date - let's say 'today' and say that the Base Task occurs on the Base Date.
I then need to calculate when all the other tasks occur etc.
In the scenario above the relationships are:
Task 1 relates to Task 4 (Base Task)
Task 2 relates to Task 4 (Base Task)
Task 3 relates to Task 2 which relates to Task 4 (Base Task)
Task 4 is the Base Task
Task 5 relates to Task 3 which relates to Task 2 which relates to Task 4 (Base Task)
Task 6 relates to Task 1 which relates To Task 4 (Bask Task)
Task 7 relates to Task 8
Task 8 relates to Task 2 which relates to Task 4 (Base Task)
Task 9 - no relationship
Task 10 relates to task 7 which relates to Task 8 which relates to Task 2 which relates to Task 4 (Base Task)
If you loop through the temporary table when you get to Task 7 you wouldn't get a result as Task 8 has not been calculated yet. So I need some sort of recursive loop that starts from the Base Date and keeps calculating until it runs out of things to calculate.
Any ideas? I don't know where to start. I can assign a date to Task 4 and then put the Tasks that relate to Task 4 into a cursor, loop through and calculate their dates ... but, what then?
Thanks for any help.
-
It's not clear exactly what output you're expecting, but see if this helps
WITH Recur AS (SELECT TaskID, Task, BaseTask, BaseTaskID, BaseTaskInterval, 1 AS Level
FROM #tblTasks
WHERE TaskID = BaseTaskID
UNION ALL
SELECT t.TaskID, t.Task, t.BaseTask, t.BaseTaskID, t.BaseTaskInterval, r.Level+1
FROM #tblTasks t
INNER JOIN Recur r ON r.TaskID = t.BaseTaskID
WHERE t.TaskID <> t.BaseTaskID
)
SELECT *
FROM Recur
ORDER BY Level,BaseTaskInterval;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Just to expand upon Mark's answer, I think you want this: -
WITH Recur AS (SELECT TaskID, Task, BaseTask, BaseTaskID, BaseTaskInterval, 0 AS taskDaysFROM #tblTasks
WHERE TaskID = BaseTaskID
UNION ALL
SELECT t.TaskID, t.Task, t.BaseTask, t.BaseTaskID, t.BaseTaskInterval, r.taskDays+t.BaseTaskInterval
FROM #tblTasks t
INNER JOIN Recur r ON r.TaskID = t.BaseTaskID
WHERE t.TaskID <> t.BaseTaskID)
SELECT TaskID, Task, BaseTask, BaseTaskID, BaseTaskInterval,
DATEADD(DD,taskDays,
'2012-01-01' -- THIS IS THE START DATE
) AS dateOfTask
FROM Recur;
So that returns: -
TaskID Task BaseTask BaseTaskID BaseTaskInterval dateOfTask----------- -------------------------------------------------- -------- ----------- ---------------- -----------------------
4 Task 4 - the base Task 1 4 0 2012-01-01 00:00:00.000
1 Task 1 - 2 days after Task 4 0 4 2 2012-01-03 00:00:00.000
2 Task 2 - 2 days before Task 4 0 4 -2 2011-12-30 00:00:00.000
3 Task 3 - 2 days after Task 2 0 2 2 2012-01-01 00:00:00.000
8 Task 8 - 1 day after Task 2 0 2 1 2011-12-31 00:00:00.000
7 Task 7 - 3 days before Task 8 0 8 -3 2011-12-28 00:00:00.000
10 Task 10 - 6 days before Task 7 0 7 -6 2011-12-22 00:00:00.000
5 Task 5 - 2 days after Task 3 0 3 2 2012-01-03 00:00:00.000
6 Task 6 - 7 days after Task 1 0 1 7 2012-01-10 00:00:00.000
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Thank you both very much for your help. I was slow replying to Mark to thank him as I was working on the most awful bit of code - using his 'recur' CTE to populate another temp table and looping through that in a cursor to calculate the dates. The date calculations are a bit more complicated than usual - they have to allow for weekends, bank holidays and department seminars and so on. So these are done by calling a stored procedure which does all the work.
I came back here to thank Mark and saw the second reply from Cadavre - which did what I was taking 30 lines of code - and a cursor - to do, in one line. I just need to write a function instead of a stored procedure to handle my date calculations and I'm there.
Cheers.
-
sku370870 (9/6/2012)
Thank you both very much for your help. I was slow replying to Mark to thank him as I was working on the most awful bit of code - using his 'recur' CTE to populate another temp table and looping through that in a cursor to calculate the dates. The date calculations are a bit more complicated than usual - they have to allow for weekends, bank holidays and department seminars and so on. So these are done by calling a stored procedure which does all the work.I came back here to thank Mark and saw the second reply from Cadavre - which did what I was taking 30 lines of code - and a cursor - to do, in one line. I just need to write a function instead of a stored procedure to handle my date calculations and I'm there.
Cheers.
I suspect you could use a calendar table instead of the need to resort to a scalar function for this. It would end up just being another join in your query.
To read up on calendar tables take a look at this article.
http://www.sqlservercentral.com/articles/T-SQL/70482/[/url]
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
Unfortunately, the 8 or so apps that use this database - which are all timeline driven - don't use a calendar table. In other apps I have a calendar table in the database with the next 30 years worth of dates in with IDs for the year, month, week, day of week etc - and ever date calculation works on DateIDs - not datetimes.
I didn't create this database and it was full of date calculations when I got hold of it. It's too late now.
-
sku370870 (9/6/2012)
Unfortunately, the 8 or so apps that use this database - which are all timeline driven - don't use a calendar table. In other apps I have a calendar table in the database with the next 30 years worth of dates in with IDs for the year, month, week, day of week etc - and ever date calculation works on DateIDs - not datetimes.I didn't create this database and it was full of date calculations when I got hold of it. It's too late now.
Well that is too bad but I totally understand and feel your pain. Glad you managed to get the rest of it worked out at least.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
sku370870 (9/6/2012)
Unfortunately, the 8 or so apps that use this database - which are all timeline driven - don't use a calendar table. In other apps I have a calendar table in the database with the next 30 years worth of dates in with IDs for the year, month, week, day of week etc - and ever date calculation works on DateIDs - not datetimes.I didn't create this database and it was full of date calculations when I got hold of it. It's too late now.
No it's not. You're writing new code in the database that could use it. Just because the apps themselves don't use it doesn't mean that you can't in your database work.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply