Sequential Record Processing - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sequential Record Processing

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-08-10 : 07:44:29

Mike writes "I am needing to write a query to find the maximum sequential occurances of a record with a certain value. For example, I have a bank account, account #1. I need to find the maximum number of sequential transactions (in order of DateTimeofTransaction) where the balance has been left negative (Ending Balance). In the example below, I will have 2 as the maximum number of sequential transactions that left the ending balance 0.

I have been able to do this in a cursor - but haven't figured out how to do this in a query. I cannot use a cursor because I am working with hundreds of thousands of records. Any help you can provide would be greatly appreciated!

Account# DateTimeofTransaction Ending Balance
1 7/1/2005 10:00am 25.22
1 7/1/2005 11:00am 10.19
1 7/1/2005 1:00pm -22.25
1 7/2/2005 9:00am -31.22
1 7/3/2005 10:00am 19.22
1 7/4/2005 11:00am -10.11
1 7/5/2005 12:00pm 15.00"

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-08-10 : 08:38:07

[code]
Declare @myTable table (acct int, tranDate datetime, bal money)
Insert Into @myTable
Select 1, '7/1/2005 10:00am', 25.22 Union All
Select 1, '7/1/2005 11:00am', 10.19 Union All
Select 1, '7/1/2005 1:00pm', -22.25 Union All
Select 1, '7/2/2005 9:00am', -31.22 Union All
Select 1, '7/3/2005 10:00am', 19.22 Union All
Select 1, '7/4/2005 11:00am', -10.11 Union All
Select 1, '7/5/2005 12:00pm', 15.00

Select *
From @myTable

Select
runEnding = nextPos,
count(*)
From
(
Select
A.acct,
A.tranDate,
A.bal,
nextPos = (Select min(tranDate) From @myTable Where acct = A.acct and tranDate > A.tranDate and bal>0)
From @myTable A
Where bal<0
) Z
Group By nextPos

Select acct, maxRun = max(cnt)
From
(
Select
acct,
runEnding = nextPos,
cnt = count(*)
From
(
Select
A.acct,
A.tranDate,
A.bal,
nextPos = (Select min(tranDate) From @myTable Where acct = A.acct and tranDate > A.tranDate and bal>0)
From @myTable A
Where bal<0
) Z
Group By acct, nextPos
) A
Group By acct
[/code]

Corey

Co-worker on The Wizard of Oz "...those three midgets that came out and danced, the freaked me out when I was little. But they are ok now."

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources