CODEWITHSUNDEEP
sqlsql-server-2005pivotunpivot
humorousdragon
humorousdragon

Reputation: 55

How to pivot Datetime type implementing checks based on Date

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

Answers (1)

Taryn
Taryn

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)

See SQL Fiddle with Demo

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

Related Questions