MySQL - how to count rows within date range while grouping by date

Question

I have to count amount of employee_id's whose row apply to the rule: date>=valid_from and date<valid_to in the table in given time, grouping by date and company_id. Fiddle with example:

http://sqlfiddle.com/#!9/994b8d/1

so the expected outcome would be for example:

company_id, date,       employees
1           2019-01-01  1
1           2020-01-01  1
1           2021-01-01  0
2           2020-01-01  3
2           2020-02-01  3
2           2020-03-01  2
2           2020-04-01  1
2           2020-05-01  0

I found quite similar question: MySQL: Grouping by date ranges but I would need some variable that could be larger or smaller than valid_to and valid_from. Plus I don't want to divide the year into periods except the date itself.

It can be done only with regard to dates when it changes, or going through all dates, though it would be much better if it was done along dates with changes to minimise the number of rows. I'm using MySql 8 and newer. Is there some special built in function for such operations? If not, how to do it in reasonable way?

question from:https://stackoverflow.com/questions/66065017/mysql-how-to-count-rows-within-date-range-while-grouping-by-date

Answer 1

Waitting for answers