To make this SQL a bit neater and faster: select dateX, count(*) as cnt from (select 1 from reg_history_all where rowid in (select last_rowid from (select id, max(rowid) as last_rowid from reg_history_all where date_status_added <= dateX group by id having reg_status = 'Registered'))) I guess the simplest and fastest way to approach this is to make a table holding only all the final date_status_added dates (final per ID) where reg_status = 'Registered' and run the CTE on that. Will look at that later. RBS