Count records that existed in a year for each year it has been issued until its end date-CodePudding

I asked a question a couple weeks ago and I thought I asked the correct question, but I needed to modify it. I am posting a new question, however the previous post can be found here Thanks to @Serg for the quick reply to my previous question.

I've copied and pasted the question below. The modification to my question is, I assumed that the end date was sysdate, whereas I actually have an END_DATE column to work from. So, instead of count all files that were active each year until sysdate, I want to count all files there were active each year until END_DATE.

My previous question edited for my new question:

I have an Oracle table containing a number of active files. Each row is a file and has unique file #, issue date and end date

File #	ISSUE_DATE	END_DATE
1254	15-OCT-1997	11-NOV-2005
5245	22-MAY-2005	15-OCT-2008
7852	02-APR-2015	05-DEC-2023
9852	11-MAR-2021	22-OCT-2028
etc

I want to query a count of how many files were active each year between the year in which it was issued and the year of its end date. So, if it was issued in 2010, then I want to include that file in the count for each year since it was issued (2010, 2011, 2012, etc), up to its end date.

I'd like my end table to loo like:

Year	COUNT_OF_FILES
1997	20
1998	32
1999	55
2000	42
...	...
2019	130
2020	155
2021	151
2022	101
2023	98
2024	61

CodePudding user response：

Just use simple generator:

Step 1: get all years for each record:

with 
t(File#, ISSUE_DATE, END_DATE) as (
    select 1254, to_date('15-OCT-1997','dd-mon-yyyy'),to_date('11-NOV-2005','dd-mon-yyyy') from dual union all
    select 5245, to_date('22-MAY-2005','dd-mon-yyyy'),to_date('15-OCT-2008','dd-mon-yyyy') from dual union all
    select 7852, to_date('02-APR-2015','dd-mon-yyyy'),to_date('05-DEC-2023','dd-mon-yyyy') from dual union all
    select 9852, to_date('11-MAR-2021','dd-mon-yyyy'),to_date('22-OCT-2028','dd-mon-yyyy') from dual
)
select
     t.*, yyyy
from t
    cross apply(
        select
            extract(year from ISSUE_DATE) level-1 as yyyy
        from dual
        connect by extract(year from ISSUE_DATE) level-1<=extract(year from END_DATE)
    ) gen_years

this will return:

     FILE# ISSUE_DATE          END_DATE                  YYYY
---------- ------------------- ------------------- ----------
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       1997
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       1998
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       1999
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2000
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2001
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2002
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2003
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2004
      1254 1997-10-15 00:00:00 2005-11-11 00:00:00       2005
      5245 2005-05-22 00:00:00 2008-10-15 00:00:00       2005
      5245 2005-05-22 00:00:00 2008-10-15 00:00:00       2006
      5245 2005-05-22 00:00:00 2008-10-15 00:00:00       2007
      5245 2005-05-22 00:00:00 2008-10-15 00:00:00       2008
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2015
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2016
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2017
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2018
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2019
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2020
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2021
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2022
      7852 2015-04-02 00:00:00 2023-12-05 00:00:00       2023
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2021
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2022
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2023
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2024
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2025
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2026
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2027
      9852 2021-03-11 00:00:00 2028-10-22 00:00:00       2028

Then just aggregate them:

select
    yyyy, count(*) cnt
from t
    cross apply(
        select
            extract(year from ISSUE_DATE) level-1 as yyyy
        from dual
        connect by extract(year from ISSUE_DATE) level-1<=extract(year from END_DATE)
    ) gen_years
group by yyyy;

Full test case with test data:

with 
t(File#, ISSUE_DATE, END_DATE) as (
    select 1254, to_date('15-OCT-1997','dd-mon-yyyy'),to_date('11-NOV-2005','dd-mon-yyyy') from dual union all
    select 5245, to_date('22-MAY-2005','dd-mon-yyyy'),to_date('15-OCT-2008','dd-mon-yyyy') from dual union all
    select 7852, to_date('02-APR-2015','dd-mon-yyyy'),to_date('05-DEC-2023','dd-mon-yyyy') from dual union all
    select 9852, to_date('11-MAR-2021','dd-mon-yyyy'),to_date('22-OCT-2028','dd-mon-yyyy') from dual
)
select
    yyyy, count(*) cnt
from t
    cross apply(
        select
            extract(year from ISSUE_DATE) level-1 as yyyy
        from dual
        connect by extract(year from ISSUE_DATE) level-1<=extract(year from END_DATE)
    ) gen_years
group by yyyy
order by yyyy;

Output:

      YYYY        CNT
---------- ----------
      1997          1
      1998          1
      1999          1
      2000          1
      2001          1
      2002          1
      2003          1
      2004          1
      2005          2
      2006          1
      2007          1
      2008          1
      2015          1
      2016          1
      2017          1
      2018          1
      2019          1
      2020          1
      2021          2
      2022          2
      2023          2
      2024          1
      2025          1
      2026          1
      2027          1
      2028          1