Home Forums SQL Server 2005 Development Converting sequential time records into IN and OUT times RE: Converting sequential time records into IN and OUT times

  • March 27, 2014 at 7:15 pm

    #1700954

    Hi Jeff & Pietlinden

    Thanks to both of you for your tips. Using these, I have written this code that provides me with the time in and out for a day and the number of minutes worked.

    -- Code Start----

    CREATE TABLE #TempAttendance (

    [PayPunchID] [varchar](15) NOT NULL,

    [PayrollID] [varchar](15) NOT NULL,

    [FirstName] [varchar](255) NULL,

    [LastName] [varchar](255) NULL,

    [ActualTime] [DateTime] NOT NULL,

    [RoundedTime] [DateTime] NOT NULL,

    [Date] [DateTime] NULL,

    [TimeN] [INT] NULL,

    [INOUT] [VARCHAR] (3) NULL

    );

    WITH CTEEmpAttendance AS

    (SELECT A.EmployeeID AS PayPunchID, E.EmployeeID AS PayrollID, E.FirstName, E.LastName, A.DateTime, A.RoundDateTime, DATEADD(dd,0,DATEDIFF(dd,0,A.DateTime)) AS DateasDate, row_number()

    OVER (partition BY A.EmployeeID, DATEADD(dd,0,DATEDIFF(dd,0,A.DateTime))

    ORDER BY A.EmployeeID, A.DateTime) AS TimeN

    FROM tblAttendance A INNER JOIN tblEmployees E

    ON A.EmployeeID = E.ID

    WHERE [DateTime] >= '15 Mar 2014'

    AND [DateTime] < '22 Mar 2014'

    )

    INSERT INTO #TempAttendance ( PayPunchID, PayrollID, FirstName, LastName, ActualTime, RoundedTime, Date, TimeN, INOUT)

    SELECT PayPunchID, PayrollID, FirstName, LastName, [DateTime], RoundDateTime, DateasDate, TimeN,INOUT =

    CASE

    WHEN (TimeN % 2) != 0 THEN 'IN'

    WHEN (TimeN % 2) = 0 THEN 'OUT'

    END

    FROM CTEEmpAttendance

    -- SELECT * from #TempAttendance

    SELECT PayPunchID, PayrollID, FirstName, LastName, Date, MIN(ActualTime) AS Actual_IN, MIN(RoundedTime) AS IN_Time, MAX(ActualTime) AS Actual_OUT, MAX(RoundedTime) AS OUT_Time, DATEDIFF(n, MIN(RoundedTime), MAX(RoundedTime)) AS MinsWorked

    FROM #TempAttendance

    GROUP BY PayPunchID, PayrollID, FirstName, LastName, Date

    DROP TABLE #TempAttendance

    -- Code end --

    Using the above code, I am able to get the number of minutes worked based on the first and last punches in a day. But our employees are required to punch out and in when they go on breaks. How can we get the number of minutes worked when the data looks like this...

    T&AIDEmp IDFirstNameLastNameActual timeRounded TimeDatecounterIN/OUT

    841273JohnSmith15/03/2014 10:5615/03/2014 11:0015/03/2014 0:001IN

    841273JohnSmith15/03/2014 13:5715/03/2014 13:4515/03/2014 0:002OUT

    841273JohnSmith15/03/2014 14:3915/03/2014 14:4515/03/2014 0:003IN

    841273JohnSmith15/03/2014 20:4615/03/2014 20:4515/03/2014 0:004OUT

    841273JohnSmith16/03/2014 7:2016/03/2014 7:3016/03/2014 0:001IN

    841273JohnSmith16/03/2014 10:5816/03/2014 10:4516/03/2014 0:002OUT

    841273JohnSmith16/03/2014 11:1916/03/2014 11:3016/03/2014 0:003IN

    841273JohnSmith16/03/2014 16:1316/03/2014 16:0016/03/2014 0:004OUT

    841273JohnSmith17/03/2014 9:5117/03/2014 10:0017/03/2014 0:001IN

    841273JohnSmith17/03/2014 13:3317/03/2014 13:3017/03/2014 0:002OUT

    841273JohnSmith17/03/2014 14:0717/03/2014 14:1517/03/2014 0:003IN

    841273JohnSmith17/03/2014 19:5317/03/2014 19:4517/03/2014 0:004OUT

    841273JohnSmith18/03/2014 9:4818/03/2014 10:0018/03/2014 0:001IN

    841273JohnSmith18/03/2014 13:5118/03/2014 13:4518/03/2014 0:002OUT

    841273JohnSmith18/03/2014 14:3018/03/2014 14:4518/03/2014 0:003IN

    841273JohnSmith18/03/2014 19:5418/03/2014 19:4518/03/2014 0:004OUT

    841273JohnSmith19/03/2014 6:4819/03/2014 7:0019/03/2014 0:001IN

    841273JohnSmith19/03/2014 10:2119/03/2014 10:1519/03/2014 0:002OUT

    841273JohnSmith19/03/2014 10:3819/03/2014 10:4519/03/2014 0:003IN

    841273JohnSmith19/03/2014 13:3919/03/2014 13:3019/03/2014 0:004OUT

    841273JohnSmith19/03/2014 14:0919/03/2014 14:1519/03/2014 0:005IN

    841273JohnSmith19/03/2014 17:0019/03/2014 17:0019/03/2014 0:006OUT

    841273JohnSmith20/03/2014 10:3820/03/2014 10:4520/03/2014 0:001IN

    841273JohnSmith20/03/2014 14:2320/03/2014 14:1520/03/2014 0:002OUT

    841273JohnSmith20/03/2014 14:5420/03/2014 15:0020/03/2014 0:003IN

    841273JohnSmith20/03/2014 20:2020/03/2014 20:1520/03/2014 0:004OUT

    841273JohnSmith21/03/2014 6:4721/03/2014 7:0021/03/2014 0:001IN

    841273JohnSmith21/03/2014 10:1021/03/2014 10:0021/03/2014 0:002OUT

    841273JohnSmith21/03/2014 10:3521/03/2014 10:4521/03/2014 0:003IN

    841273JohnSmith21/03/2014 13:2721/03/2014 13:1521/03/2014 0:004OUT

    841273JohnSmith21/03/2014 14:0121/03/2014 14:1521/03/2014 0:005IN

    841273JohnSmith21/03/2014 20:4021/03/2014 20:3021/03/2014 0:006OUT

    I need to use the rounded time... Thanks for all the great guidance