Calculate time worked by 15 min intervals.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Calculate time worked by 15 min intervals.

Author

Topic

Yammi
Starting Member

1 Post

Posted - 2007-12-19 : 15:34:17

Ok, I know that there is a very smart programmer out there that can resovle my issue.

I am trying to calculate time worked by 15 minute intervals.

Example:
Emp 1 started work at 13:00:00 and worked 183 minutes
Emp 2 started work at 17:15:00 and worked 150 minutes
Emp 3 started work at 08:30:00 and worked 17 minutes

I need to show the following results:

time employee #of_min_worked
----------------------------------------------
08:30:00 3 15
08:45:00 3 2
09:00:00
08:30:00
08:45:00
09:00:00
09:15:00
09:30:00
09:45:00
10:00:00
10:15:00
10:30:00
10:45:00
11:00:00
11:15:00
11:30:00
11:45:00
12:00:00
12:15:00
12:30:00
12:45:00
13:00:00 1 15
13:15:00 1 15
13:30:00 1 15
13:45:00 1 15
14:00:00 1 15
14:15:00 1 15
14:30:00 1 15
14:45:00 1 15
15:00:00 1 15
15:15:00 1 15
15:30:00 1 15
15:45:00 1 15
16:00:00 1 3
16:15:00
16:30:00
16:45:00
17:00:00
17:15:00 2 15
17:30:00 2 15
17:45:00 2 15
18:00:00 2 15
18:15:00 2 15
18:30:00 2 15
18:45:00 2 15
19:00:00 2 15
19:15:00 2 15
19:30:00 2 15
19:45:00
20:00:00
20:15:00
20:30:00
20:45:00
21:00:00
21:15:00
21:30:00
21:45:00
22:00:00
22:15:00
22:30:00
22:45:00
23:00:00
23:15:00
23:30:00
23:45:00

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-12-19 : 16:30:33

[code]-- Prepare sample data
DECLARE @Sample TABLE (EmployeeID INT, StartTime DATETIME, WorkMinutes INT)

INSERT @Sample
SELECT 1, '13:00', 183 UNION ALL
SELECT 2, '17:15', 150 UNION ALL
SELECT 3, '08:30', 17

-- Show the expected output
SELECT CONVERT(CHAR(5), e.FromTime, 108) AS Clock,
CASE DATEDIFF(MINUTE, e.FromTime, q.EndTime)
WHEN 0 THEN NULL
ELSE q.EmployeeID
END AS EmployeeID,
CASE
WHEN q.EndTime > e.ToTime THEN 15
ELSE NULLIF(DATEDIFF(MINUTE, e.FromTime, q.EndTime), 0)
END AS Work
FROM (
SELECT DATEADD(MINUTE, 15 * Number, '19000101') AS FromTime,
DATEADD(MINUTE, 15 * Number + 15, '19000101') AS ToTime
FROM master..spt_values
WHERE Type = 'p'
AND Number < 96
) AS e
LEFT JOIN (
SELECT StartTime,
EmployeeID,
DATEADD(MINUTE, WorkMinutes, StartTime) AS EndTime
FROM @Sample
) AS q ON q.StartTime < e.ToTime AND q.EndTime >= e.FromTime[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources