Projection of times on timeline

[ohv]

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

[wcikanek]

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

[JereArtity]

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.