Reformatting Single columned IN/OUT swiping data as InTime & OutTime as separate columns

Bandi
Posted by Bandi under Sql Server category on | Points: 40 | Views : 341
The below reformatting of Swiping data helps the user to do any type of calculation such as Total of InTime, Total of OutTime, Leave/Absent Calculations

DECLARE @ATTENDANCE TABLE( EID INT, Date DATETIMe, Time DATETIMe, Status varchar(4))
INSERT INTO @ATTENDANCE
SELECT 26359, '2013-01-01 13:00:00.000', '2013-01-01 09:50:00.000', 'IN' union all
SELECT 26359, '2013-01-01 13:00:00.000', '2013-01-01 11:47:00.000', 'OUT' union all
SELECT 26359, '2013-01-01 13:00:00.000', '2013-01-01 17:21:00.000', 'IN' union all
SELECT 26359, '2013-01-01 13:00:00.000', '2013-01-01 18:40:00.000', 'OUT' union all
SELECT 26359, '2013-01-02 13:00:00.000', '2013-01-02 09:40:00.000', 'IN' union all
SELECT 26359, '2013-01-02 13:00:00.000', '2013-01-02 18:55:00.000', 'OUT'

SELECT eid,[date],
MAX([time]) AS timein,
MinDate AS timeout
FROM @ATTENDANCE t
OUTER APPLY (SELECT TOP 1 Time AS MinDate
FROM @ATTENDANCE
WHERE Time > t.Time
AND EID = t.EID
AND Status = 'OUT'
ORDER BY Time
)t1
WHERE Status= 'IN'
GROUP BY EID,DATE,MinDate


OutPut:

eid date timein timeout
26359 2013-01-01 13:00:00.000 2013-01-01 09:50:00.000 2013-01-01 11:47:00.000
26359 2013-01-01 13:00:00.000 2013-01-01 17:21:00.000 2013-01-01 18:40:00.000
26359 2013-01-02 13:00:00.000 2013-01-02 09:40:00.000 2013-01-02 18:55:00.000

Comments or Responses

Login to post response