When you say "for all dates between 1000 and 43000", do you mean for all days that have data, or for all dates that exist, regardless of having data on that day? This would be the requirement if one wants to draw a graph or such over time - and indeed the only reason a CTE would be needed.
I think Keith did a great job of showing how the grouped query should work, so with hopefully his blessing I'm going to simply plagiarize that into a CTE query to make that all-dates variant.
I'm simply making the first CTE table be what Keith already showed, the second CTE table builds a list of all dates, and the final select JOINs these together to fill the values as needed.
[EDIT: Fixed the Query by adding the very needed LEFT JOIN that Keith's astute eye thankfully caught!]
```
WITH date_ids(last_date, id_count) AS ( -- Keith's magic
select last_date,
count(distinct id)
from (
select id,
max(date_status_added) as last_date
from reg_history_all
where not id in (
select id
from reg_history_all
where reg_status = 'Deceased'
)
and date_status_added < 43000
group by id
having reg_status = 'Registered'
)
group by last_date
), all_dates(day_date) AS ( -- The full dates list from 1000 to 43,000
select 1000
union all
select day_date + 1 from all_dates where day_date < 43000
)
SELECT day_date, id_count
FROM all_dates
LEFT JOIN date_ids ON last_date = day_date
;
```
Couldn't test it, so syntax errors may exist, but it should work in principle.
You can even add calendar dates by figuring out the Julianday number for 1900-01-01 and add that to the day_date field and use the date/time functions in SQLite to display it any way you like.
HTH