CODEWITHSUNDEEP
sqlselectsql-server-2014
controller
controller

Reputation: 195

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.

Upvotes: 0

Views: 33

Answers (2)

Chris Stewart
Chris Stewart

Reputation: 333

If I understand your question and all you want to do is include the duplicates, you can just remove the where clause "where rn = 1" from the query.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270011

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.

Upvotes: 1

Related Questions