#minutes worked by hour - SQL Server Forums

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)

#minutes worked by hour

Author

Topic

cgkitab
Starting Member

6 Posts

Posted - 2008-05-23 : 14:55:10

I am trying to prepare a report that shows the number of minutes worked by hour of day by day for our employees.
I have clockin and clockout times and want to get a by hour run down of minutes worked by employees to determine productivity.
The problem lies with crossing over to another day.

example an employee clockin at 7pm 5/23 and clock out 2:50AM 5/24:
DATE HOUR MINUTES WORKED
5-23-2008 0 0
5-23-2008 1 0
5-23-2008 2 0
5-23-2008 3 0
5-23-2008 4 0
5-23-2008 5 0
5-23-2008 6 0
5-23-2008 7 0
5-23-2008 8 0
5-23-2008 9 0
5-23-2008 10 0
5-23-2008 11 0
5-23-2008 12 0
5-23-2008 13 0
5-23-2008 14 0
5-23-2008 15 0
5-23-2008 16 0
5-23-2008 17 0
5-23-2008 18 0
5-23-2008 19 60
5-23-2008 20 60
5-23-2008 21 60
5-23-2008 22 60
5-23-2008 23 60
5-23-2008 24 60
5-24-2008 01 60
5-24-2008 02 60

I found the following code that achieves mostly what I want but the end result needs ot be as above- I am stuck...

--Create a table for the hours of the day
CREATE TABLE #tmp_hours (
HourOfDay int NULL)

--Populate the table with hours 0 - 23
DECLARE @hour int
SET @hour = 0
WHILE @hour <= 23
BEGIN
INSERT INTO #tmp_hours VALUES(@hour)
SET @hour = @hour +1
END

--Run SELECT statement off of hours table, based on ClockIn and ClockOut times
DECLARE @ClockIn datetime, @ClockOut datetime

SET @ClockIn = '2006-10-04 16:00:00'
SET @ClockOut = '2006-10-05 01:00:00'

SELECT
HourOfDay = CONVERT(varchar(5), DATEADD(hour, HourOfDay, 0), 108),

Minutes_Worked = DATEDIFF(minute,
CASE WHEN DATEDIFF(minute, DATEADD(hour, HourOfDay, 0), CONVERT(varchar, @ClockIn, 108)) BETWEEN 0 AND 59
THEN CONVERT(varchar, @ClockIn, 108)
ELSE DATEADD(hour, HourOfDay, 0) END,
CASE WHEN DATEDIFF(minute, CONVERT(varchar, @ClockOut, 108), DATEADD(hour, HourOfDay +1, 0)) BETWEEN 0 AND 59
THEN CONVERT(varchar, @ClockOut, 108)
ELSE
CASE WHEN CONVERT(varchar, @ClockIn, 108) < CONVERT(varchar, @ClockOut, 108) THEN
CASE WHEN DATEADD(hour, HourOfDay, 0) <= DATEADD(hour, -1, CONVERT(varchar, @ClockIn, 108))
OR DATEADD(hour, HourOfDay, 0) >= CONVERT(varchar, @ClockOut, 108) THEN DATEADD(hour, HourOfDay, 0)
ELSE DATEADD(hour, HourOfDay +1, 0)
END
ELSE
CASE WHEN DATEADD(hour, HourOfDay, 0) <= DATEADD(hour, -1, CONVERT(varchar, @ClockIn, 108))
AND DATEADD(hour, HourOfDay, 0) >= CONVERT(varchar, @ClockOut, 108)
THEN DATEADD(hour, HourOfDay, 0)
ELSE DATEADD(hour, HourOfDay +1, 0)
END
END
END
)
FROM #tmp_hours
ORDER BY HourOfDay

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-24 : 01:26:35

You can try this. It may require some testing and tweaking to cover all your scenarios.

DECLARE @StartDate,@EndDate

SELECT DATEADD(dd,DATEDIFF(dd,0,t.DATE),0) AS Date,
DATEPART(hh,t.DATE) AS HOUR,
CASE WHEN DATEDIFF(hh,t.DATE,@StartDate) >0 THEN 0
WHEN DATEDIFF(hh,t.DATE,@StartDate)=0 AND DATEDIFF(mi,t.DATE,@StartDate)>0
THEN DATEDIFF(mi,t.DATE,@StartDate)
WHEN DATEDIFF(hh,t.DATE,@StartDate)<0 AND DATEDIFF(hh,t.DATE,@EndDate)>=0 THEN 60
END AS MINUTESWORKED

FROM
(SELECT DATEADD(hh,number-1,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0)) AS DATE,
FROM master..spt_values
WHERE type='p'
AND DATEADD(hh,number-1,DATEADD(dd,DATEDIFF(dd,0,@StartDate),0))<=@EndDate)t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources