With the reordered table after doing: insert into reg_history_all2 select id, date_status_added, links_reg_status, reg_status, patient_type, date_status_ended from reg_history_all order by id asc, date_status_added asc, reg_status desc the explanation of what the SQL needs to do to count the registered patients at a given date (call this dateX) is simple: 1. Get the rows with max(rowid) for a given ID (so grouped by ID) 2. Loop through these rows 3. if the value of date_status_added is <= dateX then 4. if the text of reg_status = 'Registered' then add 1 to the count Something like this will do that (where dateX = 32335): select 32335, count(*) as cnt from (select date_status_added 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 <= 32335 group by id having reg_status = 'Registered'))) I have tested this on various dates and this gives the right counts. Just need to figure out now how to change this to a CTE, given all dates and all counts. It doesn't actually need to be all dates, only the unique dates of date_status_added will be fine. RBS