SQLite Forum

How to convert to CTE
Login
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