Find Account Number with Negative Balance for Consecutive 5 Days


http://social.msdn.microsoft.com/Forums/en-US/b5bd3d76-ca87-4900-a804-37212436de07/how-to-get-the-account-numbers-which-maintains-negative-balance-for-consecutive-5-days?forum=transactsql


create table #p(cal date, ac# int, bal int)

insert into #p values  ( GETDATE(), 1, -1),
                       ( GETDATE(), 2, 1),
                       ( GETDATE(), 3, 1),
                       ( GETDATE(), 4, -2),
                       ( GETDATE(), 5, -6),
                       ( GETDATE()-1, 1, -1),
                       ( GETDATE()-1, 2, 1),
                       ( GETDATE()-1, 3, 1),
                       ( GETDATE()-1, 4, 2),
                        ( GETDATE()-1, 5, -7),
                        ( GETDATE()-2, 1, -2),
                       ( GETDATE()-2, 2, -1),
                       ( GETDATE()-2, 3, -1),
                       ( GETDATE()-2, 4, 2),
                       ( GETDATE()-2, 5, -6),
                       ( GETDATE()-3, 1, -2),
                       ( GETDATE()-3, 2, -1),
                       ( GETDATE()-3, 3, -1),
                       ( GETDATE()-3, 4, 2),
                       ( GETDATE()-3, 5, -2),
                       ( GETDATE()-4, 1, -4),
                       ( GETDATE()-4, 2, 1),
                       ( GETDATE()-4, 3, 1),
                       ( GETDATE()-4, 4, -2),
                       ( GETDATE()-4, 5, -2),
                       ( GETDATE()-5, 1, -4),
                       ( GETDATE()-5, 2, 1),
                       ( GETDATE()-5, 3, 1),
                       ( GETDATE()-5, 4, 2),                       
                       ( GETDATE()-5, 5, -1),
					     ( GETDATE()-6, 5, -1) 

	--update #p
	--		  set bal=-1*bal
	--		  where cal='2014-09-22' and [ac#]=5


  ;with mycte as ( Select *
,row_number() Over(Order by  [ac#], cal) - row_number() Over(Partition by [ac#], sign(bal) Order by  [ac#], cal) rnDelta
			   
from #p)

,mycte1 as ( 
select * , 
sum(Case when bal< 0 Then 1 Else 0 End) Over(Partition by [ac#], rnDelta Order by [ac#],cal ) cnt  
from mycte
)

		
 SELECT  distinct [ac#] from mycte1
  Where cnt>=5
 



 drop table #p
 

 
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