need help - 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)

need help

Author

Topic

tanu
Yak Posting Veteran

57 Posts

Posted - 2007-09-12 : 09:44:57

I have a table like this. Employee comes in and clock in. If [Clock out] is 'True' then the time on that record is for clocking out.

Time Employeeid Clockout Julian RecordNumber
8/22/2007 9:47:34 AM 1 False 39376 78
8/22/2007 9:47:47 AM 1 True 39376 79
8/22/2007 9:50:18 AM 1 False 39376 80
8/22/2007 10:41:34 AM 1 True 39376 82
8/22/2007 10:44:34 AM 1 False 39376 84
8/22/2007 11:48:34 AM 1 True 39376 86
8/28/2007 4:47:34 AM 1 False 39386 140
8/28/2007 5:47:34 AM 1 True 39386 141

8/22/2007 10:42:34 AM 2 False 39376 83
8/22/2007 11:40:34 AM 2 True 39376 85

1.I need to find out how long did employee 1 work on 8/22/2007 and 8/28/2007 ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-12 : 10:14:54

[code]DECLARE @sample TABLE
(
[Time] datetime,
Employeeid int,
Clockout varchar(10),
Julian int,
RecordNumber int
)

INSERT INTO @sample
SELECT '8/22/2007 9:47:34 AM', 1, 'False', 39376, 78 UNION ALL
SELECT '8/22/2007 9:47:47 AM', 1, 'True', 39376, 79 UNION ALL
SELECT '8/22/2007 9:50:18 AM', 1, 'False', 39376, 80 UNION ALL
SELECT '8/22/2007 10:41:34 AM', 1, 'True', 39376, 82 UNION ALL
SELECT '8/22/2007 10:44:34 AM', 1, 'False', 39376, 84 UNION ALL
SELECT '8/22/2007 11:48:34 AM', 1, 'True', 39376, 86 UNION ALL
SELECT '8/28/2007 4:47:34 AM', 1, 'False', 39386, 140 UNION ALL
SELECT '8/28/2007 5:47:34 AM', 1, 'True', 39386, 141 UNION ALL
SELECT '8/22/2007 10:42:34 AM', 2, 'False', 39376, 83 UNION ALL
SELECT '8/22/2007 11:40:34 AM', 2, 'True', 39376, 85

SELECT [Date], ClockIn, ClockOut, [work] = DATEDIFF(minute, ClockIn, ClockOut)
FROM
(
SELECT [Date] = DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0),
ClockIn = MIN(CASE WHEN Clockout = 'False' THEN [Time] END),
ClockOut = MAX(CASE WHEN Clockout = 'True' THEN [Time] END)
FROM @sample
GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, [Time]), 0)
) t[/code]

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-12 : 10:32:41

[code]DECLARE @Sample TABLE (Time DATETIME, Employeeid INT, Clockout VARCHAR(5), Julian INT, RecordNumber INT)

INSERT @Sample
SELECT '8/22/2007 9:47:34 AM', 1, 'False', 39376, 78 UNION ALL
SELECT '8/22/2007 9:47:47 AM', 1, 'True', 39376, 79 UNION ALL
SELECT '8/22/2007 9:50:18 AM', 1, 'False', 39376, 80 UNION ALL
SELECT '8/22/2007 10:41:34 AM', 1, 'True', 39376, 82 UNION ALL
SELECT '8/22/2007 10:44:34 AM', 1, 'False', 39376, 84 UNION ALL
SELECT '8/22/2007 11:48:34 AM', 1, 'True', 39376, 86 UNION ALL
SELECT '8/28/2007 4:47:34 AM', 1, 'False', 39386, 140 UNION ALL
SELECT '8/28/2007 5:47:34 AM', 1, 'True', 39386, 141

SELECT c.EmployeeID,
DATEADD(DAY, DATEDIFF(DAY, 0, ClockIn.Time), 0) AS ClockDate,
CONVERT(CHAR(10), DATEADD(SECOND, SUM(DATEDIFF(SECOND, ClockIn.Time, ClockOut.Time)), 0), 108) AS WorkTime
FROM (
SELECT s1.EmployeeID,
s1.RecordNumber AS ClockIn,
MIN(s2.RecordNumber) AS ClockOut
FROM @Sample AS s1
INNER JOIN @Sample AS s2 ON s2.EmployeeID = s1.EmployeeID
WHERE s1.ClockOut = 'False'
AND s1.RecordNumber < s2.RecordNumber
AND s2.ClockOut = 'True'
GROUP BY s1.EmployeeID,
s1.RecordNumber
) AS c
INNER JOIN @Sample AS ClockIn ON ClockIn.EmployeeID = c.EmployeeID
INNER JOIN @Sample AS ClockOut ON ClockOut.EmployeeID = c.EmployeeID
WHERE ClockIn.RecordNumber = c.ClockIn
AND ClockOut.RecordNumber = c.ClockOut
GROUP BY c.EmployeeID,
DATEADD(DAY, DATEDIFF(DAY, 0, ClockIn.Time), 0)
ORDER BY c.EmployeeID,
DATEADD(DAY, DATEDIFF(DAY, 0, ClockIn.Time), 0)[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-12 : 10:42:02

Oops. Did not read the requirement properly