Please, see my comment to the question.
Try it:
SELECT userid, [IN], [OUT]
FROM (
SELECT *
FROM access_event_logs
) AS DT
PIVOT(MAX(timestamps) FOR eventid IN ([IN],[OUT])) AS PT
If
[total]
means the difference in hours, try it:
SELECT userid, [IN], [OUT], DATEDIFF(hh,[IN], [OUT]) AS TotalHrs
FROM (
SELECT userid, [IN], [OUT]
FROM (
SELECT *
FROM access_event_logs
) AS DT
PIVOT(MAX(timestamps) FOR eventid IN ([IN],[OUT])) AS PT
) AS FT
See:
DATEDIFF (sql)[
^]
[EDIT]
Another solution:
CREATE TABLE #tmp (ID INT IDENTITY(1,1), [USERID] VARCHAR(10), [TIMESTAMPS] DATETIME, [EVENTID] VARCHAR(10))
INSERT INTO #tmp ([USERID], [TIMESTAMPS], [EVENTID])
VALUES('019', '2014-03-06 07:50:48.000', 'IN'),
('019', '2014-03-06 17:02:39.000', 'OUT'),
('019', '2014-03-09 07:43:37.000', 'IN'),
('019', '2014-03-09 14:34:59.000', 'OUT'),
('019', '2014-03-10 07:43:34.000', 'IN'),
('019', '2014-03-10 14:30:24.000', 'OUT'),
('020', '2014-03-10 07:40:14.000', 'IN'),
('020', '2014-03-10 14:31:28.000', 'OUT'),
('021', '2014-03-10 07:45:39.000', 'IN')
SELECT t1.RowNo, t1.[USERID], t1.CurrDate, t1.[IN], t2.[OUT], DATEDIFF(hh, t1.[IN], t2.[OUT]) AS WorkHrs
FROM (
SELECT ROW_NUMBER() OVER( PARTITION BY [USERID] ORDER BY [TIMESTAMPS]) AS RowNo, [USERID], CONVERT(DATETIME, CONVERT(VARCHAR(10),[TIMESTAMPS],121)) AS CurrDate, [TIMESTAMPS] AS [IN]
FROM #tmp
WHERE [EVENTID] = 'IN'
) AS t1 INNER JOIN (
SELECT ROW_NUMBER() OVER( PARTITION BY [USERID] ORDER BY [TIMESTAMPS]) AS RowNo, [USERID], CONVERT(DATETIME, CONVERT(VARCHAR(10),[TIMESTAMPS],121)) AS CurrDate, [TIMESTAMPS] AS [OUT]
FROM #tmp
WHERE [EVENTID] = 'OUT'
) AS t2 ON t1.RowNo = t2.RowNo AND t1.USERID = t2.USERID
DROP TABLE #tmp
[/EDIT]