Find Row Changes And Output To Table

I have an SQL Server table of the following structure: id TransDate PartType ====================================== 1 2016-06-29 10:23:00 A1 2 2016-06-29 1

Solution 1:

Hmmm, here is one method using outer apply and group by:

select t1.PartType, min(t1.TransDate) as StartTime, t2.TransDate
from t t1
outer apply
     (select top 1 t2.*
      from t t2
      where t2.PartType <> t1.PartType and t2.TransDate > t1.TransDate
      order by t2.TransDate asc
     ) t2
groupby t1.PartType, t2.TransDate;

Solution 2:

With SQL Server 2012 and later, you can use this:

declare@ttable (id int, transdate datetime2(0), parttype char(2))

insert@tvalues
(1,     '2016-06-29 10:23:00',   'A1'),
(2,     '2016-06-29 10:30:00',   'A1'),
(3,     '2016-06-29 10:32:00',   'A2'),
(4,     '2016-06-29 10:33:00',   'A2'),
(5,     '2016-06-29 10:35:00',   'A2'),
(6,     '2016-06-29 10:39:00',   'A3'),
(7,     '2016-06-29 10:41:00',   'A4')

;with x as (
select*, row_number() over(partitionby parttype orderby transdate) rn
from@t
)
select parttype, transdate starttime, lead(transdate) over (orderby transdate) from x where rn =1