SQL combining clocking data and round timing

Step 1

I have a table similar to that below that represent employee clock-in throughout the week, one day is shown below. The in-out value represents the morning clock-in, lunch clock-out, lunch-in and the final clock-out at end of day.

ID         USERID      WHEN                                         INOUT
1313    1                 2015-05-12 07:49:44:000         0
1314    2                 2015-05-12 07:50:24:000         0
1315    1                 2015-05-12 13:01:17:000         2
1316    2                 2015-05-12 13:03:22:000         2
1317    2                 2015-05-12 13:29:45:000         3
1318    1                 2015-05-12 13:30:46:000         3
1319    1                 2015-05-12 16:59:53:000         1
1320    2                 2015-05-12 17:00:49:000         1

I would like a query that placed the four clocks in a single day into a single row and then create a calculated field of the total hours worked

1 2015-05-12 07:49:44:000  0  2015-05-12 13:01:17:000 2 2015-05-12 13:30:46:000 3 2015-05-12 16:59:53:000 1 [total hours worked]

Step 2,
A step 2 may push my luck but instead of showing the clocked times I would like to show them rounded forward to the nearest 15 minutes prior to 9am and rounded backwards after 5pm so

08:35 equals 08:45, 08:59 equals 09:00 and 17:07 is 17:00, 17:14 equals 17:00, 17:16 equals 17:15

This I hope will be implemented in SQL reporting services but would like to see results in MSSMS if possible.

Any help appreciated.
SimonJohnGAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
Questions:

How do you want to handle situations where there are not exactly 4 transactions per day?
Do you need to be able to handle day boundaries (shifts that cross midnight)?
Scott PletcherSenior DBACommented:
If no day boundaries are crossed, the query is rather straightforward:

SELECT
    UserId, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, [WHEN]), 0) AS date) AS WorkDate,
    MAX(CASE WHEN INOUT = 0 THEN [WHEN] END) AS FirstIn,
    MAX(CASE WHEN INOUT = 2 THEN [WHEN] END) AS LunchOut,
    MAX(CASE WHEN INOUT = 3 THEN [WHEN] END) AS LunchIn,
    MAX(CASE WHEN INOUT = 1 THEN [WHEN] END) AS FinalOut
FROM #clockings
GROUP BY USERID, DATEADD(DAY, DATEDIFF(DAY, 0, [WHEN]), 0)

I'll add the 15-minute time adjustments soon.
SimonJohnGAuthor Commented:
Hi Brian,

the first and last clock would take precedence over any other clock giving total time, the lunch clocks give an indicator that a user took 30 minutes lunch (whatever the actual clock states) those not clocking at lunch time defaults to have taken a full hour lunch. Any other clock may be noted but not take part in any calculation. We don't expect shifts to cross over midnight. We only operate a dayshift and the clocking system is a method to calculate flexi time accrued outwith the core 09:00 to 17:00 time period in 15 minute chunks. As mentioned above a user clocking at lunch has taken a 30 minute  lunch and accrued 30 mins flexi, a user not clocking a lunch has taken a full hour and accrued not flexi. I hope I answered your question and not fogged it even further :-)
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Brian CroweDatabase AdministratorCommented:
I would recommend handling the time adjustment to the 15 minute mark with a function but here is a start:

DECLARE @TimeClock TABLE
(
      ID                        INT,
      UserID                  INT,
      [When]                  DATETIME,
      InOut                  TINYINT
);

INSERT INTO @TimeClock (ID, UserID, [When], InOut)
VALUES (1313, 1, '2015-05-12 07:49:44:000', 0),
      (1314, 2, '2015-05-12 07:50:24:000', 0),
      (1315, 1, '2015-05-12 13:01:17:000', 2),
      (1316, 2, '2015-05-12 13:03:22:000', 2),
      (1317, 2, '2015-05-12 13:29:45:000', 3),
      (1318, 1, '2015-05-12 13:30:46:000', 3),
      (1319, 1, '2015-05-12 16:59:53:000', 1),
      (1320, 2, '2015-05-12 17:00:49:000', 1)

SELECT T0.UserID, T0.[When] AS ShiftStart,
      T2.[When] AS LunchStart,
      T3.[When] AS LunchEnd,
      T1.[When] AS ShiftEnd,
      (DATEDIFF(MINUTE, T0.[When], T1.[When]) - DATEDIFF(MINUTE, T2.[When], T3.[When])) / 60.0
FROM @TimeClock AS T0                        -- Shift Start
LEFT OUTER JOIN @TimeClock AS T1      -- Shift End
      ON T0.UserID = T1.UserID
      AND CAST(T0.[When] AS DATE) = CAST(T1.[When] AS DATE)
      AND T1.InOut = 1
LEFT OUTER JOIN @TimeClock AS T2      -- Lunch Start
      ON T0.UserID = T2.UserID
      AND CAST(T0.[When] AS DATE) = CAST(T2.[When] AS DATE)
      AND T2.InOut = 2
LEFT OUTER JOIN @TimeClock AS T3      -- Lunch End
      ON T0.UserID = T3.UserID
      AND CAST(T0.[When] AS DATE) = CAST(T3.[When] AS DATE)
      AND T3.InOut = 3
WHERE T0.InOut = 0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
SELECT UserId, WorkDate, FirstIn, LunchOut, LunchIn, FinalOut,
    CONVERT(varchar(5), DATEADD(MINUTE, DATEDIFF(MINUTE, FirstIn, FinalOut) -
        ISNULL(DATEDIFF(MINUTE, LunchOut, LunchIn), 60), 0), 8) AS Time_Worked
FROM (
    SELECT
        UserId, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, [when_adjusted]), 0) AS date) AS WorkDate,
        MAX(CASE WHEN INOUT = 0 THEN [when_adjusted] END) AS FirstIn,
        MAX(CASE WHEN INOUT = 2 THEN [when_adjusted] END) AS LunchOut,
        MAX(CASE WHEN INOUT = 3 THEN [when_adjusted] END) AS LunchIn,
        MAX(CASE WHEN INOUT = 1 THEN [when_adjusted] END) AS FinalOut
    FROM #clockings
    CROSS APPLY (
        SELECT
            CASE WHEN DATEPART(HOUR, [WHEN]) < 9 THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, [WHEN]) / 15.0) * 15, 0)
                 WHEN DATEPART(HOUR, [WHEN]) >= 17 THEN DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, [WHEN]) / 15.0) * 15, 0)
                 ELSE [WHEN]
                 END AS when_adjusted
    ) AS adjust_when
    GROUP BY USERID, DATEADD(DAY, DATEDIFF(DAY, 0, [when_adjusted]), 0)
) AS derived
ORDER BY UserId, WorkDate
Brian CroweDatabase AdministratorCommented:
Here is some SQL that can be wrapped in a UDF to ceiling/floor a time value by the given interval.

DECLARE @DateTime      DATETIME = '2015-05-12 07:49:44:000',
      @Interval            INT = 15,
      @Function            VARCHAR(20) = 'CEILING';

SELECT
      CASE @Function
            WHEN 'CEILING' THEN DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(@DateTime AS DATE), @DateTime) + @Interval - (DATEDIFF(MINUTE, CAST(@DateTime AS DATE), @DateTime) % @Interval), CAST(CAST(@DateTime AS DATE) AS DATETIME))
            WHEN 'FLOOR' THEN DATEADD(MINUTE, DATEDIFF(MINUTE, CAST(@DateTime AS DATE), @DateTime) - (DATEDIFF(MINUTE, CAST(@DateTime AS DATE), @DateTime) % @Interval), CAST(CAST(@DateTime AS DATE) AS DATETIME))
      END AS Result
SimonJohnGAuthor Commented:
Fantastic stuff guys!

I've spent most time looking at Scotts script and I was wondering if this could include a further couple of columns. I was wondering from the clocking would it be possible to calculate a total of hours worked and a balanced based on the standard hours worked per day being 7?

so

UserId, Workdate, FirstIn, LunchOut, LunchIn, FinalOut, Roster (always 7), HoursWorked, Balance, CummlativeFlexi

Roster is their standard 7 hour day
HoursWorked is the time while clocked in
Balance- Hours worked minus roster
Cummlative flexi - number of hours worked over the standard hours cumulative over week.

Ideally anyone clocking a LunchOut and LunchIn will default to 30 minutes lunch as anything shorter is not accepted. Those not clocking lunch will have taken 1 hour.

Feel like I'm asking a bit much now but your assistance is greatly appreciated.
S.
Scott PletcherSenior DBACommented:
I don't know what that "balance" hours would be.  "Balance -/minus " what value am I subtracting from?  It seems like this is some type of running total ... does it have a specific starting value, like 40, or what?  Will you only ever process one week at a time, meaning the balance won't have to be reset during the process?
SimonJohnGAuthor Commented:
I was thinking of a balance for the week so roster so

               Roster  worked balance cum
Mon        7            8           1              1
Tue         7             8.5       1.5           2.5
Wed        7            9            2             4.5

Maybe this should be something I roll into reporting services

Is it possible for the lunch-out be recorded as a fixed 30 minutes (shortest lunch break) If the lunch-in is within 30 minutes of the lunch-out then keep as 30 if the lunch-in is more than 30 minutes after then keep rounding forward similar to the clock out rounding method?
SimonJohnGAuthor Commented:
I would like a combine total column showing time_worked + LunchFlex - Roster to give total amount of flexi but my knowledge is letting me down. Could you please add that final stroke?

SELECT UserId, WorkDate, FirstIn, LunchOut, LunchIn, FinalOut, LunchFlex, '07:00' As Roster,
     CONVERT(varchar(5), DATEADD(MINUTE, DATEDIFF(MINUTE, FirstIn, FinalOut) -
         ISNULL(DATEDIFF(MINUTE,'2014/04/28 08:00', '2014/04/28 08:30'), 60), 0), 8) AS Time_Worked
 FROM (
     SELECT
         UserId, CAST(DATEADD(DAY, DATEDIFF(DAY, 0, [when_adjusted]), 0) AS date) AS WorkDate,
         MAX(CASE WHEN INOUT = 2 THEN '00:50' END) AS LunchFlex,
         MAX(CASE WHEN INOUT = 0 THEN [when_adjusted] END) AS FirstIn,
         MAX(CASE WHEN INOUT = 2 THEN [when_adjusted] END) AS LunchOut,
         MAX(CASE WHEN INOUT = 3 THEN [when_adjusted] END) AS LunchIn,
         MAX(CASE WHEN INOUT = 1 THEN [when_adjusted] END) AS FinalOut
     FROM [Safescan].[dbo].[attendant]
     CROSS APPLY (
         SELECT
             CASE WHEN DATEPART(HOUR, [WHEN]) < 9 THEN DATEADD(MINUTE, CEILING(DATEDIFF(MINUTE, 0, [WHEN]) / 15.0) * 15, 0)
                  WHEN DATEPART(HOUR, [WHEN]) >= 17 THEN DATEADD(MINUTE, FLOOR(DATEDIFF(MINUTE, 0, [WHEN]) / 15.0) * 15, 0)
                  ELSE [WHEN]
                 END AS when_adjusted
     ) AS adjust_when
     GROUP BY USERID, DATEADD(DAY, DATEDIFF(DAY, 0, [when_adjusted]), 0)
 ) AS derived

 ORDER BY WorkDate
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.