Search code examples
sqlselectsql-server-2014

Find Row Changes (with Duplicates) and Output to Table


I posted a question (Find Row Changes and Output to Table) regarding determining the rows in a table where a column values changes, and outputting the results to a table. The successful query (with help from the accepted answer, of course) was as follows:

;with x as (
select *, row_number() over(partition by ModelNum order by transdate) rn
from table
)
select ModelNum, TransDate as 'StartTime', lead(TransDate) over (order by TransDate) as 'EndTime' from x where rn = 1

My issue, though, is that the original table has duplicates for ModelNum, but the code that I'm using from the solution doesn't account for them. It just scans through the table and looks for the first and last value. In other words, if the original table is like this:

id     TransDate            PartType
======================================
1     2016-06-29 10:23:00   A
2     2016-06-29 10:30:00   A
3     2016-06-29 10:32:00   C
4     2016-06-29 10:33:00   C
5     2016-06-29 10:35:00   C
6     2016-06-29 10:39:00   A
7     2016-06-29 10:41:00   A
8     2016-06-29 10:43:00   B

How do I output the results to a table, so that duplicates are listed too, like so:

PartType  StartTime             EndTime       
=======================================================
A         2016-06-29 10:23:00   2016-06-29 10:32:00
C         2016-06-29 10:32:00   2016-06-29 10:39:00
A         2016-06-29 10:39:00   2016-06-29 10:43:00
B         2016-06-29 10:43:00   NULL

Also, in the original question, I was using SQLServer 2008, but now I am using 2014.


Solution

  • This is a gap and islands problem. I like to solve it with the difference of row numbers approach:

    with x as (
          select t.*,
                 row_number() over (order by transdate) as seqnum,
                 row_number() over (partition by ModelNum order by transdate) as seqnum_m
          from table t
         )
    select ModelNum, min(TransDate) as StartTime, max(TransDate) as EndTime
    from x 
    group by ModelNum, (seqnum - seqnum_m);
    

    How this works is a little tricky. If you run the CTE and stare at the results, you will probably "get" how the difference finds the groups you are looking for.