Ok... first of all, take a look at the following link to see why a thing called Triangular Joins are bad... real bad...

http://www.sqlservercentral.com/articles/T-SQL/61539/

I'd post the link to the "running totals" article, but I'm in the process of rewriting it. So, you'll have to trust me. 😉 Read the comments in the code below. Also, see the link in my signature below for a really helpful way to post data. Thanks.

--===== Create a test table.

-- This is NOT part of the solution.

IF OBJECT_ID('TempDB..#YourTable') IS NOT NULL

DROP TABLE #YourTable

CREATE TABLE #YourTable

(

AccountNbr INT,

CreateDate DATETIME

)

--===== Populate the test table with data.

-- This is NOT part of the solution.

INSERT INTO #YourTable

(AccountNbr,CreateDate)

SELECT '59961','01/05/09' UNION ALL

SELECT '59961','01/06/09' UNION ALL

SELECT '59961','01/07/09' UNION ALL

SELECT '32187','01/05/09' UNION ALL

SELECT '32187','01/06/09' UNION ALL

SELECT '22195','01/10/09' UNION ALL

SELECT '22195','01/12/09' UNION ALL

SELECT '22195','01/13/09' UNION ALL

SELECT '22195','01/15/09' UNION ALL

SELECT '69248','01/11/09' UNION ALL

SELECT '69248','01/12/09'

--===== You might be tempted to use this, but it contains a

-- triangular join and will slow down in a hurry if you

-- have more than just a handful of rows for each AccountNbr.

-- I posted this to warn you that it's bad.

SELECT AccountNbr,

CreateDate,

Seq = (SELECT COUNT(*)

FROM #yourtable i

WHERE i.AccountNbr = o.AccountNbr

AND i.CreateDate <= o.CreateDate)

FROM #yourtable o

ORDER BY o.AccountNbr, o.CreateDate

--===== Instead, use the following method which will solve a

-- million rows in about 7 seconds.

IF OBJECT_ID('TempDB..#SeqWork') IS NOT NULL

DROP TABLE #SeqWork

DECLARE @PrevAccountNbr INT,

-- @PrevCreateDate DATETIME,

@PrevSeq INT

SELECT ISNULL(AccountNbr,0) AS AccountNbr,

ISNULL(CreateDate,0) AS CreateDate,

CAST(0 AS INT) AS Seq

INTO #SeqWork

FROM #yourtable

ORDER BY AccountNbr, CreateDate

ALTER TABLE #SeqWork

ADD PRIMARY KEY CLUSTERED (AccountNbr, CreateDate)

UPDATE #SeqWork

SET @PrevSeq = Seq = CASE WHEN AccountNbr = @PrevAccountNbr THEN @PrevSeq+1 ELSE 1 END,

@PrevAccountNbr = AccountNbr

FROM #SeqWork WITH(INDEX(0),TABLOCKX)

SELECT *

FROM #SeqWork

ORDER BY AccountNbr, CreateDate