Get max min per group in sequence — A solution for the question


You can find the original question from MSDN and I tried to come up with a solution but with requirement changes, here is the final version. I hope this is.

 



if object_id('test','U') is not null drop table test 
Create table test (Car int, X Char(1), [Time] int)

INSERT  INTO test
VALUES  ( 11, 'A', 5 ),
        ( 11, 'B', 6 ),
        ( 11, 'B', 7 ),
        ( 11, 'B', 8 ),		 
        (  11,'C', 9 ),
        (  11,'C', 10 ),
        (  11,'C', 11 ),
		(  11,'A', 12 ),
		(  11,'B', 13 ) ;


	with mycte as (	 	
	Select  Car, X,  [Time] time1, lead( [Time],1) Over( Order by  [Time]) leadTime 
	,row_number()Over(order by [Time]) - ROW_NUMBER() OVER (PARTITION BY X ORDER BY [Time]) delta from test )

 ,mycte1 as (
 Select Car, X,  time1,leadTime, delta
 , ROW_NUMBER() OVER (PARTITION BY delta ORDER BY [Time1] DESC) rn
 , count(*) OVER (PARTITION BY X, delta ) cnt   
 from mycte)





 Select Car, X, min(time1) minTime, max(leadTime)  maxTime,  0 as moving 
 from mycte1
 WHERE rn>1
Group by  Car, X, delta 
 UNION ALL
Select  Car, X,  min(time1) minTime, max(CASE WHEN cnt> 1 Then leadTime Else time1 END )  maxTime , 1 as Moving 
from mycte1
 WHERE rn=1
GROUP BY Car, X, delta
Order by minTime


/*
Car	X	minTime	maxTime	moving
11	A	5	5	1
11	B	6	8	0
11	B	8	9	1
11	C	9	11	0
11	C	11	12	1
11	A	12	12	1
11	B	13	13	1
*/



 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s