I have a table that looks like this…

Account      Status        Date         Attempt(*) 

A            In_PROGRESS   12/12/2017   1 A            In_PROGRESS   13/12/2017   1 A            SUCCESS       15/12/2017   1 B            ERROR         10/12/2017   1 B            SUCCESS       13/12/2017   2 C            IN_PROGRESS   11/12/2017   1 C            ERROR         13/12/2017   1 C            ERROR         14/12/2017   2 C            IN_PROGRESS   15/12/2017   3 C            IN_PROGRESS   17/12/2017   3 

I want to get the Attempt(*) column.

ERROR and SUCCESS are end states; i.e and attempt ends with either ERROR or SUCCESS. IN_PROGRESS is a transitory status. For example, the entries for Account C read as follows:

ROW 1: The first attempt is IN_PROGRESS

ROW 2: the first attempt produced an ERROR

ROW 3: The second attempt produced an ERROR

ROW 4: The third attempt is IN_PROGRESS

ROW 5: The third attempt is still IN_PROGRESS

The table gets appended every day with an update on the status of each account.

I am using SQL Server management Studio 2008, so I do not have access to LAG and LEAD. I am also fairly new to SQL, so an explanation of the logic behind the solution would be great for me.

Many Thanks