SQL Server Employee Clock in/out times reformatting?

Question

I am relatively new to SQL and have written a bunch of various views.

I have been asked to created a report for our monthly payroll using clockings from SQL Server.

Currently SQL formats things:

Date       | Name | Clock In                | Clock Out
-----------+------+-------------------------+------------------------
01/02/2018 | Name | 2018-02-01 07:55:08.563 | 2018-02-01 10:21:42.183
01/02/2018 | Name | 2018-02-01 10:32:37.153 | 2018-02-01 13:12:33.773
01/02/2018 | Name | 2018-02-01 13:33:10.123 | 2018-02-01 15:04:16.880

However I would like it something like this:

Date | Name | Clock In | Clock Out | Clock In | Clock Out | Clock In | Clock Out
--
01/02/2018 | Name | 2018-02-01 07:55:08.563 | 2018-02-01 10:21:42.183 | 2018-02-01 10:32:37.153 | 2018-02-01 13:12:33.773 | 2018-02-01 13:33:10.123 | 2018-02-01 15:04:16.880

I have things currently working in excel using the formula:

{=INDEX(Payroll_Clockings[Start Time], SMALL(IF(1 = ((--($A9=Payroll_Clockings[Date Value])) * (--($J$4=Payroll_Clockings[Name]))), ROW(Payroll_Clockings[Start Time]) -1, ""), COLUMN() -1))}

But being an Array formula this can take a long time to refresh when there are 50+ records.

So it would be ideal to rearrange things through SQL.

the SQL view i am using is:

SELECT TOP (100) PERCENT 
    CONVERT(VARCHAR(19), ShopFloor.EmployeeActivity.StartEvent, 103) AS Date, 
    Admin.Employee.FirstName + ' ' + Admin.Employee.Surname AS Name, 
    ShopFloor.EmployeeActivity.StartEvent AS [Start Time],
    ShopFloor.EmployeeActivity.FinishEvent AS [Finish Time], 
    ShopFloor.EmployeeActivity.ClockNumber AS [Clock No.]
FROM
    ShopFloor.EmployeeActivity 
LEFT OUTER JOIN
    Admin.Employee ON ShopFloor.EmployeeActivity.ClockNumber = Admin.Employee.ClockNumber
WHERE
    (ShopFloor.EmployeeActivity.StartEvent BETWEEN GETDATE() - 180 AND GETDATE()) 
    AND (NOT (ShopFloor.EmployeeActivity.ClockNumber IN (100, 10090, 10000, 777, 999, 10001, 10098, 10002))) 
    AND (ShopFloor.EmployeeActivity.ActivityTypeCode = 1)
ORDER BY 
    Date, Name, [Start Time]

Hopefully this makes sense and any help would be appreciated!

After looking here: SQL Server - PIVOT - two columns into rows

This seems to be the right idea however they are only working on 5 rows, my current data is coming to around 10,000 rows... would there be a way around that?

Thanks

EDIT

Further to Daniel's suggestion below I have the following;

`WITH CTE AS (SELECT CONVERT(VARCHAR(19), StartEvent, 103) AS Date, 
StartEvent AS Start, FinishEvent AS Finish, ClockNumber AS Clock
FROM            ShopFloor.EmployeeActivity
WHERE        (StartEvent >= CONVERT(DATETIME, '2021-01-01 00:00:01', 102)) 
AND (NOT (ClockNumber IN (100))) AND (ActivityTypeCode = 1)
ORDER BY Date, Start)

select a.date, a.Clock, a.[1],b.[1],a.[2],b.[2],a.[3],b.[3] from (
select date, Clock, max([1])[1],max([2])[2],max([3])[3] from(
select *, row_number() over(partition by date, Clock order by date)b from 
CTE) a 

pivot(max(Start) for b in ([1],[2],[3]))c
group by date, Clock)a
left join
(
select date, Clock, max([1])[1],max([2])[2],max([3])[3]  from(
select *, row_number() over(partition by date, Clock order by date)b from 
CTE) a 

pivot(max(Finish) for b in ([1],[2],[3]))c
group by date, Clock)
b on a.date=b.date and a.Clock=b.Clock `

Don't do this in your Data Engine, Pivot your data in your Presentation layer. Especially considering you want all the columns to have the same name (not ideal). — Thom A, Apr 16 '18 at 15:12
Possible duplicate of [Three column SQL PIVOT](https://stackoverflow.com/questions/8393020/three-column-sql-pivot) — Tripp Kinetics, Apr 16 '18 at 15:12
Possible duplicate of [SQL Server - PIVOT - two columns into rows](https://stackoverflow.com/questions/21250631/sql-server-pivot-two-columns-into-rows) — Tab Alleman, Apr 16 '18 at 15:14
Thank you for your suggestions; I will look into Pivot and see what i can come up with. How would you recommend tackling this if it were you? — Dean Cohen, Apr 16 '18 at 15:19
Same thing as @Larnu said, don't do it in the database. It's not designed for variable number of columns. — James Z, Apr 16 '18 at 16:12

score 1 · Accepted Answer · answered Apr 16 '18 at 15:35

1

Something like this will work if you know the max number of login/logout combinations for each employee. If not, you will have to use dynamic sql to generate the pivot. Give this a try and let me know how it works for you:

select a.date, a.name, a.[1],b.[1],a.[2],b.[2],a.[3],b.[3] from (
select date, name, max([1])[1],max([2])[2],max([3])[3] from(
select *, row_number() over(partition by date, name order by date)b from #temp) a 

pivot(max(clockin) for b in ([1],[2],[3]))c
group by date, name)a
left join
(
select date, name, max([1])[1],max([2])[2],max([3])[3]  from(
select *, row_number() over(partition by date, name order by date)b from #temp) a 

pivot(max(clockout) for b in ([1],[2],[3]))c
group by date, name)
b on a.date=b.date and a.name=b.name

answered Apr 16 '18 at 15:35

Daniel Marcus

2,686
1
7
13

Thank you; There would be 3 In & Outs for each employee each day. anything other than that will be fixed/enforced by HR. I will have a go at trying your suggestion now :) – Dean Cohen Apr 17 '18 at 08:17
Great let me know how it goes – Daniel Marcus Apr 17 '18 at 11:02
Sorry, I think I am confusing myself/maybe out of my depth a little? - I have tried substitute my own info into your code however I am a little confused with what the [1], [2] and [3] would refer to? i have tried to simplify things and only use the ShopFloor.EmployeeActivity table so i would have the following columns: Clock Number, StartEvent, FinishEvent, Date (converted from StartEvent) and ActivityTypeCode=1 – Dean Cohen Apr 17 '18 at 12:04
One two three is the 1st login, 1st logout, 2nd login, 2nd logout etc – Daniel Marcus Apr 17 '18 at 13:07
Thank you for your help. I ended up leaving things with Excel as i dont think i know enough about SQL yet to pursue your method. – Dean Cohen May 10 '18 at 09:08
I am revisiting this and trying again to use your code again and getting myself into a mess. I have the error `'Invalid object name '#temp'` is this something i need to configure? – Dean Cohen Feb 11 '21 at 12:03
#temp is just the name of a sample table. Replace this with the name of the actual table you are pulling your data from. – Daniel Marcus Feb 12 '21 at 15:38
Thank you for your help, I have got this working in SQL! time to get it tested but I think you have cracked it. – Dean Cohen Feb 15 '21 at 13:24

SQL Server Employee Clock in/out times reformatting?

1 Answers1