Results 1 to 3 of 3

Thread: Projection of times on timeline

  1. #1
    Join Date
    Feb 2011
    Posts
    1

    Smile 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. #2
    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. #3
    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
  •