Ok, now answer me this: Do these two queries below give the same result?
Because according to SQL rules they must (unless I am missing something), so if they don't we need to really see your data and Schema:
Your original:
```
SELECT xsd2xld(22/04/2021) as Cnt_Date, count(*) as Cnt
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 < xsd2xld(22/04/2021)
GROUP BY id
HAVING reg_status = 'Registered'
)
;
```
vs. This simplified version:
```
SELECT MAX(date_status_added) as Cnt_Date, COUNT(*) AS Cnt,
FROM reg_history_all
WHERE reg_status = 'Registered' AND date_status_added < xsd2xld(22/04/2021)
;
```
EDIT: Perhaps I should explain why the above two queries should be equivalent:
In the first query, you essentially ask in the inner query to first give you a list of all the records, except the records where the status is "Deceased". Once you have that list, Group by id (which does nothing other than allow a HAVING clause) and then using the having clause only give the records where the status is "Registered".
This is very much like me saying to you: Give me a list of all cars on earth, but exclude any Toyotas. Finally, from that, show me only the Fords.
There was no need to exclude the Toyotas... you could have just directly asked for the Fords, which already excludes all Toyotas. In this way, asking for "Registered" statuses in the latter part of the query already excludes any "Deceased" statuses, there really is no need to first exclude them specifically.
Further to that, the internal GROUP BY is completely irrelevant as it groups on a Unique value, and even if it isn't a unique value, the implied grouping in the outer query "SELECT xsd2xld(22/04/2021) as Cnt_Date, count(*) as Cnt" actually groups by the real thing you wish to group by, and that is the entire set.
So please test the two queries, and if they actually give different results, there is a vast amount of information we are missing about your schema.