Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2014
    Posts
    6

    Unanswered: Calculating duration of activities per hour

    ey guys

    My data is in the following form:

    CREATE TABLE Temp (ORG_NAME VARCHAR(20), START_TIME datetime, END_TIME datetime)

    INSERT INTO Temp VALUES('Org Name', '2014-06-20 14:25:00.000', '2014-06-20 15:25:00.000') - AND many more like these with different START_TIME and END_TIME.

    The Task:

    - I need to calculate the duration of the activities PER HOUR.
    i.e. in the example above, if I want to see the productivity for 2PM (i.e. activity duration from 2-3PM), I should only get 35 mins (as the activity started at 14:25). In the same way, if I see the productivity for 3PM (i.e activity duration from 3-4 PM), I should only see 25 minutes (as activity ended at 15:25).

    There would technically be many activities with overlapping times - for example, there might be 5 activities starting at 14:30 and ending at 15:10. In this case, if I were to see the productivity for 2PM, I'd see 150 minutes (as each activity starts at 14:30, so 30 min per activity = 150 min). In the same way, if I saw the productivity for 3PM for those 5 activities, I'd see 50 minutes.

    Hope I explained that well.

    Thanks in advance!

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    DROP TABLE DaTable
    CREATE TABLE DaTable (
    	ORG_NAME VARCHAR(20)	NOT NULL, 
    	START_TIME datetime	NOT NULL, 
    	END_TIME datetime	NOT NULL
    )
    
    INSERT INTO DaTable VALUES
    ('Org Name',  '2014-06-20 14:25:00.000', '2014-06-20 15:25:00.000'),
    
    ('overlap 1', '2014-06-21 14:30:00.000', '2014-06-21 15:10:00.000'), 
    ('overlap 2', '2014-06-21 14:30:00.000', '2014-06-21 15:10:00.000'), 
    ('overlap 3', '2014-06-21 14:30:00.000', '2014-06-21 15:10:00.000'), 
    ('overlap 4', '2014-06-21 14:30:00.000', '2014-06-21 15:10:00.000'), 
    ('overlap 5', '2014-06-21 14:30:00.000', '2014-06-21 15:10:00.000')
    
    --SELECT * FROM DaTable
    
    DECLARE @ProductionHour	datetime, @ProductionHourEnd datetime
    DECLARE @ExpectedResult int
    
    SET @ProductionHour = '2000-01-01 01:00:00.000'	-- expected result 0 min
    SET @ExpectedResult = 0
    
    --SET @ProductionHour = '2114-12-31 01:00:00.000'	-- expected result 0 min
    --SET @ExpectedResult = 0
    
    --SET @ProductionHour = '2014-06-20 14:00:00.000'	-- expected result 35 min
    --SET @ExpectedResult = 35
    
    --SET @ProductionHour = '2014-06-20 15:00:00.000'	-- expected result 25 min
    --SET @ExpectedResult = 25
    
    --SET @ProductionHour = '2014-06-21 14:00:00.000'	-- expected result 150 min
    --SET @ExpectedResult = 150
    
    --SET @ProductionHour = '2014-06-21 15:00:00.000'	-- expected result 50 min
    --SET @ExpectedResult = 50
    
    SET @ProductionHourEnd = DATEADD(hour, 1, @productionHour)
    
    PRINT '@productionHour = ' + CONVERT(VARCHAR(20), @productionHour, 120) + '; @ProductionHourEnd = ' + CONVERT(VARCHAR(20), @ProductionHourEnd, 120)
    
    ;WITH AllActivitiesInPH AS (
    -- select those activities that took place during the ProductionHour of interest
    SELECT START_TIME, END_TIME
    FROM DaTable as DT
    WHERE START_TIME < @ProductionHourEnd
    	AND END_TIME > @ProductionHour
    ), AdjustStartAndEndTimes AS (
    -- Correct starting and ending time when the occur before or after the ProductionHour of interest
    SELECT CASE WHEN START_TIME < @ProductionHour THEN @ProductionHour ELSE START_TIME END as START_TIME, 
    		CASE WHEN END_TIME > @ProductionHourEnd THEN @ProductionHourEnd ELSE END_TIME END as END_TIME
    FROM AllActivitiesInPH as T
    ), Durations AS (
    -- summarise the duration times
    SELECT 
    	SUM(DATEDIFF(minute, START_TIME, END_TIME)) as Duration
    FROM AdjustStartAndEndTimes
    )
    -- compare the result with the expected result, show 'OK' when the validation passes, otherwise show the calculated value and the expected one
    SELECT 
    	COALESCE(Duration, 0) as Duration, 
    	CASE WHEN COALESCE(Duration, 0) = @ExpectedResult 
    		THEN 'OK' 
    		ELSE 'Error: calculated ' + CAST(COALESCE(Duration, 0) as VARCHAR(10)) + ', expected ' + CAST(@ExpectedResult as VARCHAR(10)) 
    	END AS validation
    FROM Durations
    I used multiple CTE's to show the different steps involved. You can merge them together.
    Last edited by Wim; 07-27-14 at 21:05. Reason: corrected some typo's
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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