2

I tried for 1 1/2 shifts to solve my problem set-based but couldn't quite get there. Solved it in about 15 mins with cursor and it runs fast enough. But I wonder if there is a way to do it set-based.

We have records of employee status changes extracted from 3rd party HR app: empid,recorddate,status. I need to identify the recorddate and status for each change of an emp's status over time. However there is a problem in the data. Sometimes there will be rows with different record dates for an emp, but the status does NOT change. 

declare @test table (empid int, recorddate date,status varchar(10))
insert into @test (empid,recorddate,status) values
(1,'1/1/2000','a'),
(1,'2/1/2000','b'),
(1,'3/1/2000','b'),
(1,'3/3/2000','b'),
(1,'4/1/2000','c'),
(2,'2/1/2000','a'),
(2,'3/1/2000','c'),
(1,'5/1/2000','a')
(1,'6/1/2000','a')
(2,'7/1/2000','c')

I need to return the recordate and status for any change in status for an emp.

So in the example below, there is no record returned for record date of 3/1/2000 for emp #1 because the status is same as for preceeding record date of 2/1/2000, and no record for emp #1 for 6/1/2000 because the status value did not change vs. record with closest earlier recorddate.

And same concept for emp #2, no record returned with 7/1/2000 recorddate for that emp because status did not change for closest earlier recordate

empid, recorddate, status
--------------------------------------
1,'1/1/2000','a'
1/'2/1/2000','b'
1,'4/1/2000','c'
1,'5/1/2000','a'
2,'2/1/2000','a'
2,'3/1/2000','c'

I tried numbering the unchanging sequences of status using partion by empid and status, order by empid, recorddate and then selecting the rownumber 1 from each window "frame" to get the earliest occurence, but no luck. The rownumber would not reset to 1 when the status might occur > 1 times in the records for an emp, but discontiguous across recorddates.

thanks ken

John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
user3389773
  • 21
  • 2

1 Answers1

0

You can easily solve this using LAG window function:

select empid, recorddate, status
from (
   select empid, recorddate, status,
          coalesce(lag(status) over (partition by empid 
                                     order by recorddate), '') as prevstatus
   from @test) as t
where status <> prevstatus
order by empid, recorddate
Giorgos Betsos
  • 71,379
  • 9
  • 63
  • 98