Working on a complex SQL query and need help in finding the best way to achieve it. The DB table has a time range based usage record. We have to create the time series usage. For Example:
Sample Table:
**BEG_DT END_DT REGION**
2020-01-01 2021-06-09 region_a
2020-06-29 2021-06-09 region_a
2020-01-01 2020-06-29 region_a
2020-01-01 2021-06-09 region_b
2020-01-01 2021-06-09 region_b
2020-01-01 2021-06-09 region_a
2020-01-01 2021-06-09 region_a
2020-07-08 2021-06-09 region_a
2020-01-01 2020-07-08 region_a
2021-05-10 2021-06-09 region_a
2020-01-01 2021-05-10 region_a
2020-01-01 2021-06-09 region_a
......
2020-01-01 2021-06-09 region_a
Result:
**Date: Active count:**
2020-01-01 9000
2020-01-02 8940
......
2021-06-09 8067
What is the best way to write such query?
Solution1: Do a join query. But it is will very resource intensive.
Solution 2: Do application level processing and run select query in for loop for all the time series.
Which solution is better 1 or 2? Or there is better way we can achieve this. (edited)