# Thread: Calculating duration of activities per hour

1. Registered User
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.

- 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.

2. Registered User
Join Date
Nov 2004
Posts
1,428
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
-- 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
)
-- 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

#### Posting Permissions

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