# Miscellaneous > SQL Scripts >  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.

----------

