TimeSheet - Date Time
-
I have a table which is show below. i want to write a SQL which gives me the following result
FEmpID In Out Hours Worked
--------------------------------------
210 08:59 19:10 10:11
210 20:00 21:00 01:00
Table
FEmpID FTime FType
------ ------------------------- -----
210 2004-03-03 08:59:00.000 IN
210 2004-03-03 19:10:00.000 OUT
210 2004-03-03 20:00:00.000 IN
210 2004-03-03 21:00:00.000 OUT
-
This will work ... can probably be simplified
declare @temp table ( FEmpID int, FTime datetime, FType char(3) ) insert into @temp values ( 210, '2004-03-03 08:59:00.000', 'IN' ) insert into @temp values ( 210, '2004-03-03 19:10:00.000', 'OUT') insert into @temp values ( 210, '2004-03-03 20:00:00.000', 'IN' ) insert into @temp values ( 210, '2004-03-03 21:00:00.000', 'OUT')
select t1.FEmpID, convert(char(5),t1.FTime,8) as 'In', convert(char(5),t2.FTime,8) as 'Out', convert(char(5),t2.FTime - t1.FTime,8) as 'Hours Worked' from @temp t1 inner join @temp t2 on t2.FEmpID = t1.FEmpID and t2.FType = 'OUT' and t2.FTime = (select min(FTime) from @temp t3 where t3.FEmpID = t1.FEmpID and t3.FType = 'OUT' and t3.FTime > t1.FTime) where t1.FType = 'IN' order by t1.FTime
FEmpID In Out Hours Worked 210 08:59 19:10 10:11 210 20:00 21:00 01:00
(2 row(s) affected)
-
Considering of having the column that shows the Session when the user logs in/out.
So if u have to make a little change in your design, by adding a column "Session" of type INT. Thus your Final tab.le "TimeSheet" must have the schema,
TimeSheet( FEmpID INT, Session INT, FTime DATETIME, FType VARCHAR(3))
Thus your required query is as written below:
SELECT FEmpID,
RIGHT( '00' + CAST( DATEPART(HH, [IN]) AS VARCHAR(2)),2) + ':' + LEFT( CAST( DATEPART(MI, [IN]) AS VARCHAR(2)) +'00', 2) AS [IN],
RIGHT( '00' + CAST( DATEPART(HH, [OUT]) AS VARCHAR(2)),2) + ':' + LEFT( CAST( DATEPART(MI, [OUT]) AS VARCHAR(2)) +'00', 2) AS [OUT],
RIGHT( '00' + CAST( DATEPART(HH, [OUT]-[IN]) AS VARCHAR(2)),2) + ':' + LEFT( CAST( DATEPART(MI, [OUT]-[IN]) AS VARCHAR(2)) +'00', 2) AS [Hours Worked]
FROM ( SELECT FEmpID, Session, MAX( CASE WHEN [IN] IS NOT NULL THEN [IN]END ) AS [IN],
MAX( CASE WHEN [OUT] IS NOT NULL THEN [OUT] END ) AS [OUT]
FROM ( SELECT FEmpID, session, CASE WHEN Ftype = 'IN' THEN FTIME END AS [IN],
CASE WHEN Ftype = 'OUT' THEN FTIME END AS [OUT]
FROM timeSheet ) AS A
GROUP BY FEmpID, session ) AS B
Hope this will suffice ur need and much more better than the solution above.
The query is becomes Unformatted, Just copy it and paste it in your query analyser and it will be formatted.
Regards,
Prashant Thakwanithakwani_prashant@yahoo.co.in
-
SELECT
FEmpID
, (CAse When Q.N = 1 Then [IN] Else [OUT] END) As FTime
, (CAse When Q.N = 1 Then 'IN' Else 'OUT' END) As FType
FROM
OriginalTable Cross Join (Select 1 as N Union all Select 2 ) Q
As you didn't mentioned How is the date provided I didn't included in the query but it will be very easy to modify the above to show the date
HTH
* Noel -
Question...why was the table designed with the "IN" and "OUT" as separate Records? I'm not harrassing, I just want to know if there is some type of advantage to that?
-
SELECT FEmpID, In AS 'FTime', 'IN' AS 'FType'
FROM TableTimeSheet
-- WHERE ...
UNION ALL
SELECT FEmpID, Out AS 'FTime', 'OUT' AS 'FType'
FROM TableTimeSheet
-- WHERE ...
ORDER BY FEmpID, FTime
-
thank you guys for your time. The data i have shows was in an ideal situation. There can be multiple "IN" if the user forgets to logout or multiple "OUT"
something like this can happen.
FEmpId FTime FType
------ ------------------------- -----
210 2004-03-03 08:59:00.000 IN
210 2004-03-03 09:30:00.000 IN
210 2004-03-03 19:10:00.000 OUT
210 2004-03-03 20:00:00.000 IN
210 2004-03-03 21:00:00.000 OUT
in such cases i would require the result like
FEmpId FTimeIn FTimeOut Hours
------ ---------------- ---------------- -------
210 2004-03-03 08:59 NULL NULL
210 2004-03-03 09:30 2004-03-03 19:10 09:40
210 2004-03-03 20:00 2004-03-03 21:00 01:00
-
I mis-understood the question backward. Try following.
SELECT U.FEmpId, U.FTimeIn, U.FTimeOut, U.Hours
FROM (
SELECT A.FEmpId, A.FTime AS 'FTimeIn',
(SELECT TOP 1 case when B.FType = 'IN' THEN NULL ELSE B.FTime END FROM TableTimeSheet B WHERE B.FTime = (SELECT MIN(C.FTime) FROM TableTimeSheet C WHERE C.FTime > A.FTime)) AS 'FTimeOut',
(SELECT TOP 1 case when B.FType = 'IN' THEN NULL ELSE LEFT(CONVERT(varchar(10), B.FTime - A.FTime, 8), 5) END FROM TableTimeSheet B WHERE B.FTime = (SELECT MIN(C.FTime) FROM TableTimeSheet C WHERE C.FTime > A.FTime)) AS 'Hours'
from TableTimeSheet A
where A.FType = 'IN'
UNION ALL
SELECT A.FEmpId, NULL AS 'FTimeIn', A.FTime AS 'FTimeOut', NULL AS 'Hours'
FROM TableTimeSheet A
WHERE A.FType = 'OUT'
AND (SELECT TOP 1 B.FType FROM TableTimeSheet B WHERE B.FTime = (SELECT MAX(C.FTime) FROM TableTimeSheet C WHERE C.FTime < A.FTime)) = 'OUT'
) U
ORDER BY U.FEmpId, ISNULL(U.FTimeIn, U.FTimeOut)
-
SELECT i.FEmpID, i.FTime FTimeIn, o.FTime FTimeOut, CONVERT(char(5),o.FTime - i.FTime,14) Hours
FROM Punches i LEFT JOIN Punches o ON i.FEmpID = o.FEmpID AND o.FType = 'OUT' AND o.Ftime =
(SELECT MIN(FTime)
FROM Punches
WHERE FEmpID = i.FEmpID AND FTime > i.FTime)
WHERE i.FType = 'IN'
ORDER BY i.FEmpID, FTimeIn
--Jonathan -
Try following data.
create table Punches(FEmpId int, FTime datetime, FType varchar(3))
insert into Punches(FEmpId, FTime, FType)
select 210, '2004-03-03 08:59:00.000', 'IN'
union all
select 210, '2004-03-03 09:30:00.000', 'IN'
union all
select 210, '2004-03-03 19:10:00.000', 'OUT'
union all
select 210, '2004-03-03 20:00:00.000', 'IN'
union all
select 210, '2004-03-03 21:00:00.000', 'OUT'
union all
select 210, '2004-03-03 21:59:00.000', 'OUT'
If you don't care the last one record...
FEmpId FTimeIn FTimeOut Hours
----------- ------------------------------------------ ------------------------------------------------------ -----
210 2004-03-03 08:59:00.000 NULL NULL
210 2004-03-03 09:30:00.000 2004-03-03 19:10:00.000 09:40
210 2004-03-03 20:00:00.000 2004-03-03 21:00:00.000 01:00
210 NULL 2004-03-03 21:59:00.000 NULL
-
If that's what you want...
SELECT ISNULL(i.FEmpID,o.FEmpID) FEmpID, i.FTime FTimeIn, o.FTime FTimeOut, CONVERT(char(5),o.FTime - i.FTime,14) Hours
FROM Punches i FULL JOIN Punches o ON i.FEmpID = o.FEmpID AND o.FType = 'OUT' AND i.FType = 'IN' AND o.Ftime =
(SELECT MIN(FTime)
FROM Punches
WHERE FEmpID = i.FEmpID AND FTime > i.FTime)
WHERE i.FType = 'IN' OR o.FType = 'OUT'
ORDER BY FEmpID, ISNULL(i.FTime,o.FTime)
--Jonathan -
-
Thanks Jonathan it works like a german sedan. And thanks for every one for you valuable time.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply