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