November 18, 2010 at 3:57 am
Hello!
I work for a bank. I have a set of customers and their account balances per day.
Now I need to create a list that contains customers who have had a negative balance for 10 consecutive days at some point during the current year.
I can solve this by cursors and looping, but isn't there a smarter way?
November 18, 2010 at 4:55 am
something like
select distinct custid
from
(
select custid, baltype, ROW_NUMBER() over (partition by custid, baltype order by dte) as seq
from
(select custid, dte, baltype = case when bal >= 0 then 1 else 0 end
from tbl
where dte >= CONVERT(varchar(4),getdate(),112) + '0101'
) a
) b
where baltype = 0
and seq >= 10
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 5:01 am
Plz post your table schema and sample data.
Ram
MSSQL DBA
November 18, 2010 at 5:02 am
I would guess from the question
custid, dte, bal.
Sample data should be easy to create if you need it.
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 5:16 am
posting sample data is usually expected from these sorts of questions, and helps cut down on the guess work..
also it allows someone with a few minutes spare to have a go at helping
November 18, 2010 at 5:51 am
Would have thought with this question it would be quicker for someone with a few minutes to read it as it is rather than look at table structures and test data - depends on the person though I guess.
Cursors never.
DTS - only when needed and never to control.
November 18, 2010 at 6:19 am
Here is some sample data in an attachment
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply