# Thread: Projection of times on timeline

1. ohv
Registered User
Join Date
Feb 2011
Posts
1

## Projection of times on timeline

Hi,

I am looking for a script giving me the total time spent on tasks in a table. The problem is that i do not need sum(datediff(day, a, b)) but I need the projection on the timeline. A sample (all including the border day): Activity a takes from day 3 to day 8, activity b from 5 to 9, activity c from 11 to 12, activity d from 4 to 5. Total projection would be from 3 to 9 plus from 11 to 12 giving a total of 9 days. I have written a function which is way too complex and way too slow. Grateful for any hint.

thanks
ohv

2. Registered User
Join Date
Feb 2006
Posts
4
This will work if you don't mind using a cursor.

CREATE TABLE #ttask
(seqno INT IDENTITY
,Task VARCHAR(10)
,StartDayNum INT
,EndDayNum INT
)

INSERT INTO #ttask (task,startDayNum,EndDayNum) VALUES ('a',3,9)
INSERT INTO #ttask (task,startDayNum,EndDayNum) VALUES ('b',5,9)
INSERT INTO #ttask (task,startDayNum,EndDayNum) VALUES ('c',11,12)
INSERT INTO #ttask (task,startDayNum,EndDayNum) VALUES ('d',4,5)

DECLARE @MinDay INT
DECLARE @MaxDay INT
DECLARE @Task VARCHAR(10)
DECLARE @StartDay INT
DECLARE @EndDay INT
DECLARE @AccumDays INT

SET @MaxDay = 0
SET @MinDay = 0
SET @AccumDays = 0

DECLARE task_cursor CURSOR FOR
SELECT Task,StartDayNum,EndDayNum
FROM #ttask
ORDER BY StartDayNum,EndDayNum

OPEN task_cursor
FETCH NEXT FROM task_cursor INTO @task,@StartDay,@EndDay
WHILE @@fetch_status = 0
BEGIN

IF @MinDay = 0
BEGIN
SET @MinDay = @StartDay
SET @MaxDay = @EndDay
END

IF @StartDay = @MinDay
IF @EndDay > @MaxDay
SET @MaxDay = @EndDay

IF @StartDay < @MaxDay
IF @EndDay > @MaxDay
SET @MaxDay = @EndDay

IF @StartDay > @MaxDay
BEGIN
SET @AccumDays = @AccumDays + ((@MaxDay - @MinDay) +1)
SET @MinDay = @StartDay
SET @MaxDay = @EndDay
END

FETCH NEXT FROM task_cursor INTO @task,@StartDay,@EndDay
END

SET @AccumDays = @AccumDays + ((@MaxDay - @MinDay) +1)

SELECT @AccumDays

CLOSE task_cursor
DEALLOCATE task_cursor

DROP TABLE #ttask

3. Banned
Join Date
Dec 2011
Location
Austria
Posts
6

## Projection of times on timeline

Thanks for your script. You have to solve this within one script, I dont think its possible with multiple scripts.

But you can simplify your script to make things easier. I have created a sample script, maybe this will be a starting point for your solution.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•