Advanced aggregate pivot on datetime for employee status

Question

I'm working with a table which stores timestamps for a variety of status types and i want it pivoted to get a clearer picture of each employees actual "busy" time... (so i can run work load/stats)

There are a couple of caveat's..

• Employee can assign themself in progress twice by mistake (See Row 5 for EMP1 in sample data), I want the pivot to use the earliest time

• Employee can either set themself as FRO or FRV (both are variations of being free for new assignments) so whether employee is FRO or FRV it should display either of the two status types in the FREE column

Status Types:

• ST = Start/Assigned
• IP = Work in progress
• FRV = Free in vehicle
• FRO = Free in office

Sample Data for the status table:

 DateTime              emp      status
 3/26/2017 14:20:30    EMP1      ST
 3/26/2017 14:21:16    EMP1      FRO
 3/26/2017 14:26:11    EMP1      ST
 3/26/2017 14:26:42    EMP1      IP
 3/26/2017 14:26:45    EMP1      IP
 3/26/2017 14:28:35    EMP1      FRO
 3/26/2017 14:21:15    EMP2      ST
 3/26/2017 14:22:35    EMP2      IP
 3/26/2017 14:55:53    EMP2      FRV
 3/26/2017 14:20:48    EMP3      ST
 3/26/2017 14:23:49    EMP3      FRV

The desired result after the pivot would be as shown below:
(ST = Start, IP = Work In Progress, FREE = Not busy)

emp     ST                      IP                     FREE        
EMP1    3/26/2017 14:20:30      null                   3/26/2017 14:21:16
EMP1    3/26/2017 14:26:11      3/26/2017 14:26:42     3/26/2017 14:28:35
EMP2    3/26/2017 14:21:15      3/26/2017 14:22:35     3/26/2017 14:55:53
EMP3    3/26/2017 14:20:48      null                   3/26/2017 14:23:49

test table with sample data.

CREATE TABLE test (
      [DateTime] [datetime] NULL,
      [emp] [varchar](6) NOT NULL,
      [status] [varchar](6) NOT NULL
      )

INSERT INTO test values 
('2017-03-26 14:20:30.000', 'EMP1', 'ST'),
('2017-03-26 14:21:16.000', 'EMP1', 'FRO'),
('2017-03-26 14:26:11.000', 'EMP1', 'ST'),
('2017-03-26 14:26:42.000', 'EMP1', 'IP'),
('2017-03-26 14:26:45.000', 'EMP1', 'IP'),
('2017-03-26 14:28:35.000', 'EMP1', 'FRO'),
('2017-03-26 14:21:15.000', 'EMP2', 'ST'),
('2017-03-26 14:22:35.000', 'EMP2', 'IP'),
('2017-03-26 14:55:53.000', 'EMP2', 'FRV'),
('2017-03-26 14:20:48.000', 'EMP3', 'ST'),
('2017-03-26 14:23:49.000', 'EMP3', 'FRV')

Any help much appreciated, happy to further explain if the request is too vague......

Thanks

Answer 1

I look at this logic as: You want to group the records based on each start. Once you have the group, then the rest is just aggregation.

In SQL Server 2012+, assigning the group is easy . . . it is the cumulative sum of the number of starts up to each record. In SQL Server 2008, this is trickier. But you can do it with a correlated subquery:

select emp,
       min(case when status = 'ST' then datetime end) as st,
       min(case when status = 'IP' then datetime end) as ip,
       max(case when status in ('FRQ', 'FRV') then datetime end) as fr
from (select t.*,
             (select count(*)
              from test t2
              where t2.emp = t.emp and t2.datetime <= t.datetime and
                    t2.status = 'ST'
             ) as grp
      from test t
     ) t
group by emp, grp;

Here is a SQL Fiddle.