Excellent -- though I did not see any mention of a status of "Left" in any previous posting other than in anecdotal comments about "Leaving". So reg1 takes the source data and labels the pstatus (previous reg_status) for each (first record for an id has no prior reg_status). The ordering is by reg_status desc so that Deceased appears after Registered if someone has both on the same date. Reg2 then only considers status changes (status IS NOT pstatus (note that pstatus can be NULL if the id had no prior status)). If the status change is to 'Registered' then the cnt is +1 and if the pstatus was 'Registered' but is now something else (like 'Deceased') then the count is '-1'. The nesting is to allow reg_status other than 'Deceased' to be included in reg1 (as you have done) and tracks only status == 'Registered'. Reg3 computes the total cnt change for each date. The final query computes the running total of each days count for each date. So the correct initial question would be to have said: I have a table reg_history_all containing columns id, date_status_added, reg_status. reg_status indicates the registration status at the time the record was added. There may be multiple entries per id per date. I am interested in the running count of unique id's with status 'Registered' on each date. A registration is terminated by a reg_status of 'Deceased' or 'Left'. There may be other status that need to be removed from the data. There is no guarantee that an id was "Registered" prior to "Deceased" or "Left" and no guarantee that they were not transitioned through all three states on one day.