Reputation: 55
I want to Pivot the "Person_Log" table data.. Its Columns are as follows:
EmployeeID-> Foreign key
Log-> DateTime type
format of "Log" is "1/22/2013 2:02:34 PM"
I want to create pivots based on checks on Date from Log column and then display the minimum and maximum values for the time on each date... its a sort of attendance report.. Required columns are like...
EmployeeID, 01-Jan IN, 01-Jan OUT, 02-Jan IN, 02-Jan OUT, 03-Jan IN, 03-Jan OUT.....and so on..
columns other than the EmployeeID are supposed to contain only time extracted from "Log" column.. For extraction i am using convert(char(10), Log, 101) for Date and convert(char(5), Log, 108) for Time extraction Purpose..
The best i reached for a single day was this:
SELECT dbo.DoorLog.EmployeeID,
CONVERT(char(10),
MIN(dbo.DoorLog.DateTime), 101) AS Date,
CONVERT(char(8), MIN(dbo.DoorLog.DateTime), 108) AS INTime,
CONVERT(char(8), MAX(dbo.DoorLog.DateTime), 108) AS OUTTime,
dbo.Person.Name, dbo.Person.Department, dbo.Person.Sex,
dbo.Person.WorkUnit,
dbo.Person.Position
FROM dbo.DoorLog
INNER JOIN dbo.Person ON dbo.DoorLog.EmployeeID = dbo.Person.EmployeeID
GROUP BY CONVERT(char(10), dbo.DoorLog.DateTime, 101),
dbo.DoorLog.EmployeeID, dbo.Person.Name, dbo.Person.Department,
dbo.Person.Sex, dbo.Person.WorkUnit, dbo.Person.Position;
Please reply as i am running on deadline of two days.. Thanks in advance
As u asked...sample data..
Log EmployeeID
2013/01/31 12:31 11
2013/01/25 10:31 10
2013/01/23 13:29 8
2013/01/20 11:49 4
Upvotes: 1
Views: 1735
Reputation: 247880
This data transformation is a PIVOT
. In SQL Server 2005+ there is a function that will rotate the data for you. There are a few ways that you can get the result that you want. Both version will implement both the UNPIVOT
and then the PIVOT
function.
Sample Data:
CREATE TABLE Person ([EmployeeId] int, [Name] varchar(4));
INSERT INTO Person ([EmployeeId], [Name])
VALUES
(11, 'Jim'),
(10, 'John'),
(8, 'Mary'),
(4, 'Tim');
CREATE TABLE DoorLog([EmployeeId] int, [DoorDate] datetime);
INSERT INTO DoorLog ([EmployeeId], [DoorDate])
VALUES
(11, '2013-01-31 12:31:00'),
(11, '2013-01-31 16:50:00'),
(11, '2013-01-31 17:50:00'),
(10, '2013-01-25 10:31:00'),
(10, '2013-01-25 16:45:00'),
(8, '2013-01-23 13:29:00'),
(8, '2013-01-23 18:25:00'),
(4, '2013-01-20 11:49:00'),
(4, '2013-01-20 19:10:00'),
(11, '2013-01-15 11:15:00'),
(11, '2013-01-15 16:25:00'),
(10, '2013-01-10 09:21:00'),
(10, '2013-01-10 15:45:00'),
(8, '2013-01-08 01:29:00'),
(8, '2013-01-08 02:25:00'),
(4, '2013-01-06 10:17:00'),
(4, '2013-01-06 19:10:00');
Your query starts with getting the list of employees with the min/max value for each date:
select p.employeeid,
p.name,
convert(char(10),d.doordate, 101) date,
min(d.doordate) [In],
max(d.doordate) [Out]
from person p
left join doorlog d
on p.employeeid = d.employeeid
group by p.employeeid, p.name,
convert(char(10),d.doordate, 101)
The next step is the UNPIVOT
which will take the separate columns for IN/OUT time and place them into multiple rows:
select employeeid, name,
convert(char(8), doortime, 108) DoorTime,
date + '_'+ col as col_names
from
(
select p.employeeid,
p.name,
convert(char(10),d.doordate, 101) date,
min(d.doordate) [In],
max(d.doordate) [Out]
from person p
left join doorlog d
on p.employeeid = d.employeeid
group by p.employeeid, p.name,
convert(char(10),d.doordate, 101)
) src
unpivot
(
doortime
for col in ([In], [Out])
) unpiv
See SQL Fiddle with Demo. The result will look like this:
| EMPLOYEEID | NAME | DOORTIME | COL_NAMES |
-------------------------------------------------
| 4 | Tim | 10:17:00 | 01/06/2013_In |
| 4 | Tim | 19:10:00 | 01/06/2013_Out |
| 4 | Tim | 11:49:00 | 01/20/2013_In |
| 4 | Tim | 19:10:00 | 01/20/2013_Out |
Once you have this result, you can then apply the pivot. If you know the date values ahead of time, then you can hard-code the values similar to this:
select *
from
(
select employeeid, name,
convert(char(8), doortime, 108) DoorTime,
date + '_'+ col as col_names
from
(
select p.employeeid,
p.name,
convert(char(10),d.doordate, 101) date,
min(d.doordate) [In],
max(d.doordate) [Out]
from person p
left join doorlog d
on p.employeeid = d.employeeid
group by p.employeeid, p.name,
convert(char(10),d.doordate, 101)
) src
unpivot
(
doortime
for col in ([In], [Out])
) unpiv
) p
pivot
(
max(doortime)
for col_names in ([01/06/2013_In], [01/06/2013_Out],
[01/08/2013_In], [01/08/2013_Out],
[01/10/2013_In], [01/10/2013_Out],
[01/15/2013_In], [01/15/2013_Out],
[01/20/2013_In], [01/20/2013_Out],
[01/23/2013_In], [01/23/2013_Out],
[01/31/2013_In], [01/31/2013_Out])
) piv
See SQL Fiddle with Demo.
But for your situation you will probably need to use dynamic SQL to generate the result since you will most likely want the results on the fly for any month. A dynamic SQL version of this is:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT ',' + QUOTENAME(date +'_'+Logname)
from
(
select doordate,
convert(char(10),doordate, 101) date,
LogName
from DoorLog
cross apply
(
select 'In' LogName
union all
select 'Out'
) l
) s
group by convert(char(10), doordate, 112), date, Logname
order by convert(char(10), doordate, 112)
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query
= 'select employeeid, name, '+@cols+'
from
(
select employeeid, name,
convert(char(8), doortime, 108) DoorTime,
date + ''_''+ col col_names
from
(
select p.employeeid,
p.name,
convert(char(10),d.doordate, 101) date,
min(d.doordate) [In],
max(d.doordate) [Out]
from person p
left join doorlog d
on p.employeeid = d.employeeid
group by p.employeeid, p.name,
convert(char(10),d.doordate, 101)
)src
unpivot
(
doortime
for col in ([In], [Out])
) unpiv
) p
pivot
(
max(doortime)
for col_names in('+@cols+')
) piv'
execute(@query)
See SQL Fiddle with Demo.
The result of both queries is:
| EMPLOYEEID | NAME | 01/06/2013_IN | 01/06/2013_OUT | 01/08/2013_IN | 01/08/2013_OUT | 01/10/2013_IN | 01/10/2013_OUT | 01/15/2013_IN | 01/15/2013_OUT | 01/20/2013_IN | 01/20/2013_OUT | 01/23/2013_IN | 01/23/2013_OUT | 01/25/2013_IN | 01/25/2013_OUT | 01/31/2013_IN | 01/31/2013_OUT |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 11 | Jim | (null) | (null) | (null) | (null) | (null) | (null) | 11:15:00 | 16:25:00 | (null) | (null) | (null) | (null) | (null) | (null) | 12:31:00 | 17:50:00 |
| 10 | John | (null) | (null) | (null) | (null) | 09:21:00 | 15:45:00 | (null) | (null) | (null) | (null) | (null) | (null) | 10:31:00 | 16:45:00 | (null) | (null) |
| 8 | Mary | (null) | (null) | 01:29:00 | 02:25:00 | (null) | (null) | (null) | (null) | (null) | (null) | 13:29:00 | 18:25:00 | (null) | (null) | (null) | (null) |
| 4 | Tim | 10:17:00 | 19:10:00 | (null) | (null) | (null) | (null) | (null) | (null) | 11:49:00 | 19:10:00 | (null) | (null) | (null) | (null) | (null) | (null) |
Upvotes: 5