Counting records between records in SQL server 2005

I recently worked my through this problem and thought I would share the solution. Here is the problem as was presented.

I have a table that stores Calls data for each customer... and whether the Sales Calls were Successful:

Table1
ID_field TheDate Successful
100 07/03/09 N
100 07/01/09 N
100 06/26/09 Y
100 06/21/09 N
100 06/18/09 N
100 06/12/09 N
100 06/10/09 Y
100 06/06/09 N
100 06/02/09 N
100 05/31/09 Y
100 05/28/09 N

Question: For the each Success(Y), I need to know how many unsuccessful (N) calls there were before the Success occurred. So for ID 100, he had a Y on 6/26/09 ... and it took 3 N's (6/21, 6,18, and 6/12) before it happened. The result would look like this:

ID_Field Success Attempts
100 06/26/09 3
100 06/10/09 2
100 05/31/09 1

So What we have to do is to build a list of the successes, and then find out how many unsuccessful records we had between this success and the last one. Here is the code to do just that.


Select ID_Field, TheDate,
(
Select Count(*) From Table1 T3
Where TheDate <>
(
Select Coalesce(Max(TheDate),
(Select DateAdd(d,-1,Min(TheDate)) From Table1))
From Table1 T1
Where (T1.TheDate < id="SPELLING_ERROR_9" class="blsp-spelling-error">TheDate)
And T1.Successful = 'Y'
)
) Attempts
From Table1 T2
Where Successful = 'Y'
Order by TheDate

The use of sub queries is very handy here to be able to use the data from any given record to look up data on other records. The important thing to remember here is that if you are using a sub query on the same table as the main query, you will need to Alias the table so that the query processor does not get confused as to which Table1 you are referring to at any given point.