# 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

```