I think I need nested loop or maybe not 2

[teqtaq]

I think I need nester loop which I had never written before. Please, help me to decide.

I have only one table. Like illustrated below with data as shown.

DATE___________ID____SalaryCode_____ACTIONcode
11/11/2005 --12345-----010------------HIRED
11/11/2005 --12345-----010------------PROMO
11/11/2005 --12345-----020------------REVIEW


So I am thinking I am getting a loop like
loop until ACTION Code='PROMO' (note when this condition is met what date there (11/11/2006 in my example))
then
loop to find greater date (just one - next after 11/11/2006) and see if SalaryCode is greater then the one for 'PROMO'.
If not - return result so it can be investigated and fixed.

So in my example there is going to be NULL result.
_______________________________________________________
However in example below result will be 11/11/2008 ---12345---010--REVIEW
because after PROMO(tion) salary code had not changed to a greater one.

DATE___________ID____SalaryCode_____ACTIONcode
11/11/2005 --12345-----010------------HIRED
11/11/2005 --12345-----010------------PROMO
11/11/2005 --12345-----010------------REVIEW


I want to say one more time I have only one table with tons of records. I need generic way of running code - fishing out 'bad' once and send them out to a client to investigate.

Thanks and good week!

 

[MajP]

I guess my sarcasm did not come through. I really do not suggest using the code because the query is the correct way to go. I only wanted to demonstrate that this is doable, but far more complicated using code. I got this to work on a small set of data, but there is no error checking and probably will not account for special cases.