quote:

---

Originally posted by nigelrivett

You need something to order by and there doesn't seem to be anything here you have three rows with date 20110305 17:32:10 - what order are they in?


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

Originally posted by nigelrivett

222147,2011-02-08 17:32:10.000,S,REC_REV,admin
222147,2011-02-08 17:32:10.000,D,REC_DECL,willis4
222147,2011-02-08 17:32:10.000,D,DECL, yorkb

These still look like they have the same app date.

If you sort that out it could be something like

with cte as
(
select credit_application_id, Recommendation_code, seq = row_number() over (partition by credit_application_id order by app_date)
)
select credit_application_id, count(*)
from cte c
where Recommendation_code = 'REC_REV'
and not exists (select * from cte c2 where c.credit_application_id = c2.credit_application_id and c2.seq = c.seq - 1 and c2.Recommendation_code = 'REC_REV')
group by credit_application_id


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---