CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Edward Rhoades
Edward Rhoades

Reputation: 31

Calculate Employee Timesheet Data

Suppose you have a timeclock table with only a timestamp entry for each timeclock event:

Userid  CheckTime
312     2018-05-08 05:52:00
312     2018-05-08 18:06:00
312     2018-05-10 05:55:00
312     2018-05-10 18:00:00
312     2018-05-11 05:58:00
312     2018-05-11 18:00:00
312     2018-05-12 05:35:00
312     2018-05-12 18:00:00

How can I tally the events in SQL Server to show like this?

Day       Date       In      Out        Reg      OT
Tuesday   5/8/2018   5:52AM  6:06PM     12.00    0.00
Thursday  5/10/2018  5:55AM  6:00PM     12.00    0.00
Friday    5/11/2018  5:58AM  6:00PM     12.00    0.00
Saturday  5/12/2018  5:35AM  6:00PM     12.00    0.42

Plus, we have overnight people who start their shift in one day and carry over to another day.

I'm not sure how to calculate this since it is row based vs column based.

I've tried this...but it doesn't work correctly...

         ;WITH emp
                 AS (SELECT [UserID],
                            [CheckTime],
                    CAST([CheckTime] AS DATE) AS [Day],
                    Row_Number()
                    OVER( PARTITION BY [UserID], CAST([CheckTime] AS DATE)
                     ORDER BY [UserID], [CheckTime]) AS [RowNumber]
                     FROM   [dbo].[Clock_Data] WHERE CHECKTIME 
                     BETWEEN '2018-05-06' AND '2018-05-13')
            SELECT 
             t1.[UserID], 
             E.[Last Name]AS [EMPID],
                   MIN(t1.[CheckTime]) AS [time_in],
                   MAX(t2.[CheckTime]) AS [time_out],
                   CAST((SUM(ISNULL(DATEDIFF(ss,  t1.[CheckTime], 
            t2.[CheckTime]) , 0)) / 3600)-1 AS VARCHAR(10)) + '.' +
            FROM   emp AS t1
                   LEFT JOIN emp AS t2
                          ON ( t1.[UserID] = t2.[UserID]
                               AND t1.[Day] = t2.[Day]
                               AND t1.[RowNumber] = ( t2.[RowNumber] - 1 )
                               AND t2.[RowNumber] % 2 = 0 
                               )
                    INNER JOIN Employees as E on t1.Userid = E.[ID Number]
            GROUP  BY t1.[UserID], E.[Last Name]
            ORDER  BY t1.[UserID]

Upvotes: 0

Views: 864

Answers (1)

Sean Lange
Sean Lange

Reputation: 33580

As mentioned in the comments above there are a LOT of complexities in this type of query. Missed/duplicate punches. Daylight saving time. Holidays, weekends. The types of things that might count as O/T for whatever rules you need. But for a nice clean set of data like you have you can do this fairly easily. This is by no means a complete solution because you have a LOT of things to iron out for details. But this should serve as a decent starting point.

declare @Something table
(
    Userid int
    , CheckTime datetime
)

insert @Something values
    (312, '2018-05-08 05:52:00')
    , (312, '2018-05-08 18:06:00')
    , (312, '2018-05-10 05:55:00')
    , (312, '2018-05-10 18:00:00')
    , (312, '2018-05-11 05:58:00')
    , (312, '2018-05-11 18:00:00')
    , (312, '2018-05-12 05:35:00')
    , (312, '2018-05-12 18:00:00');

with OrderedResults as
(
    select *
        , RowNum = ROW_NUMBER() over(partition by Userid order by CheckTime)
    from @Something
)
, InPunch as
(
    select *
        , GroupNum = Row_Number () over(partition by Userid order by RowNum)
    from OrderedResults
    where RowNum % 2 = 1
)
, OutPunch as 
(
    select *
        , GroupNum = Row_Number () over(partition by Userid order by RowNum)
    from OrderedResults
    where RowNum % 2 = 0
)

select ip.Userid
    , PunchDate = convert(date, ip.CheckTime)
    , CheckIn = ip.CheckTime
    , CheckOut = op.CheckTime
from InPunch ip
join OutPunch op on op.GroupNum = ip.GroupNum

Upvotes: 2

Related Questions