Ok, this is starting to form a clearer picture - and it means my previous assumption was wrong about id being in any way unique (also pointed out by Keith in another post).
Now your further statements say that this is what needs to be achieved:
- Get the rows with max(rowid) for a given ID (so grouped by ID)
- Loop through these rows
- if the value of date_status_added is <= dateX then
- if the text of reg_status = 'Registered' then add 1 to the count
This still doesn't answer the question though, it pre-supposes the solution to a non-stated question.
Let me try to state a question I *think* might be what you want, then you say if it is correct or needs to be adjusted:
"Give a list of the cumulative registration counts per day for any registration type of all people who didn't die at some point".
Is this accurate?
If not, please give the change to that statement that will work. If that is correct, then this below query works on the example data you've given, though it doesn't yield pretty results hard to be sure, all but 1 entry has a deceased ID in the example.
```
WITH non_deceased_registrations(added_date, added_count) AS (
SELECT A.DATE_STATUS_ADDED, COUNT(*)
FROM reg_history A
WHERE A.REG_STATUS = 'Registered' AND NOT EXISTS(SELECT 1 FROM reg_history B WHERE B.ID = A.ID AND B.REG_STATUS = 'Deceased')
GROUP BY A.DATE_STATUS_ADDED
ORDER BY A.DATE_STATUS_ADDED
), all_dates(day_date) AS (
SELECT 10000 UNION ALL SELECT day_date+1 FROM all_dates WHERE day_date<45000
), cumulative_registrations(day_date, added_total_to_date) AS (
SELECT day_date, SUM(added_count) OVER (ORDER BY day_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM all_dates
LEFT JOIN non_deceased_registrations ON added_date = day_date
)
SELECT day_date, COALESCE(added_total_to_date,0) AS added_total_to_date
FROM cumulative_registrations
```
Feel free to adjust those start and end dates (10000 & 45000) to suit your needs or use your excel date convert to state them, I don't have it so couldn't test with it.
Note that this gives a full range of dates between 10000 and 45000 which is a lively 35K of rows to deal with.
Also note, this gives the total of ALL registrations, be it "Notification of Registration" or "Application Form FP1 submitted" or Record Receive, etc. etc. Are you sure that is wanted?
I would think we only want to count something like "Notification of Registration" - but then that has to be stated in the question.
Lastly - Your question about "This works, just show me how to put this query in a CTE" is not viable, that query will run forever in a CTE for even a mediocre date-range. The above query will already take some time, but will at least finish in human time scales.