Nested Ranking function to Get an Answer



create table #Data
(
  Date date,
  Balance money
)

insert into #Data values('2014-01-01', 1000)
insert into #Data values('2014-01-02', 1000)
insert into #Data values('2014-01-03', 2000)
insert into #Data values('2014-01-04', 2000)
insert into #Data values('2014-01-05', 1000)
insert into #Data values('2014-01-06', 2000)
insert into #Data values('2014-01-07', 500)


select Date, Balance,RN, dense_rank() over (partition by RNS order by date) Seq, RNS 
from ( select *,  row_number() over (partition by Balance, rn2 order by date) RNS 
from ( select *,  row_number() over ( order by date) RN
 , row_number() over ( order by date) - row_number() over (partition by  balance order by date) rn2  
 from #Data) t1   ) t2
Order by [Date]

 

drop table #Data

/*
Date	Balance	RN	Seq	RNS
2014-01-01	1000.00	1	1	1
2014-01-02	1000.00	2	1	2
2014-01-03	2000.00	3	2	1
2014-01-04	2000.00	4	2	2
2014-01-05	1000.00	5	3	1
2014-01-06	2000.00	6	4	1
2014-01-07	500.00	7	5	1
*/


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/b39cc65a-a83b-4302-8288-a0ceaa0f637b/detecting-change-in-values-of-a-column?forum=transactsql

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