I have an Oracle database table that stores licenses. Each License has a unique number and a date range from the time it was issued to the time it expired. Something like this:

I am trying to count the number of licenses that existed on each day for all of 2023. The resultant table will look something like this:

I need to evaluate each day of the year against the range between ISSUE_DATE and EXPIRY_DATE to determine if the license was active and then add it to the count of each day that is has been active. So for example:

License 1111 would only get included in the count for each day between Jan. 1, 2023 and it’s expiry date on Sept. 2, 2023

License 1114 would only get included in the count for each dday between it’s issue date on Aug. 17, 2023 to it’s expiry on Nov. 25, 2023

etc

This is the part I am getting hung up on. Any help is appreciated.

>Solution :

Use a row generator to generate a calendar and then use a correlated sub-query:

WITH calendar (day) AS (
  SELECT TRUNC(SYSDATE, 'YY')
  FROM   DUAL
UNION ALL
  SELECT day + INTERVAL '1' DAY
  FROM   calendar
  WHERE  day + INTERVAL '1' DAY < ADD_MONTHS(TRUNC(SYSDATE, 'YY'), 12)
)
SELECT c.day,
       ( SELECT COUNT(license_number)
         FROM   licenses l
         WHERE  c.day BETWEEN l.issue_date AND l.expired_date ) AS license_count
FROM   calendar c;

Or, use a row generator (in this case a hierarchical query) and OUTER JOIN and then aggregate:

SELECT c.day,
       COUNT(l.license_number) AS license_count
FROM   (
         SELECT TRUNC(SYSDATE, 'YY') + LEVEL - 1 AS day
         FROM   DUAL
         CONNECT BY TRUNC(SYSDATE, 'YY') + LEVEL - 1
                      < ADD_MONTHS(TRUNC(SYSDATE, 'YY'), 12)
       ) c
       LEFT OUTER JOIN licenses l
       ON c.day BETWEEN l.issue_date AND l.expired_date
GROUP BY c.day;

Which, for the sample data:

CREATE TABLE licenses (LICENSE_NUMBER, ISSUE_DATE, EXPIRED_DATE) AS
SELECT 1111, DATE '2022-11-22', DATE '2023-09-02' FROM DUAL UNION ALL
SELECT 1112, DATE '2022-12-05', DATE '2023-10-11' FROM DUAL UNION ALL
SELECT 1113, DATE '2023-02-14', DATE '2024-04-04' FROM DUAL UNION ALL
SELECT 1114, DATE '2023-08-17', DATE '2023-11-25' FROM DUAL;

Which both output:

fiddle