CODEWITHSUNDEEP
sqloracle-databaserecursive-query
Mike
Mike

Reputation: 4405

Calculate Age of License for each year, since issued. Oracle SQL

I have a table that contains records of licenses. The table contains a unique license number and the date it was issued.

License #      Issue Date      
1234           2018-FEB-01
5678           2016-MAR-15
9012           1991-JAN-22
3456           2005-SEP-25

I need to create a report that calculates the age of the license each year since is was issued (rounded down to the nearest year) on a specific date. In this case, sysdate is fine.

So, I am looking for something like this:

License#     Year    License_Age
1234         2018    0
1234         2019    1
1234         2020    2
5678         2016    0 
5678         2017    1
5678         2018    2
5678         2019    3
5678         2020    4

This is going to feed into more query that calculates fees for each year based on a fee schedule. Fees increment ever 5 years or so, however if I can figure out the nuts and bolts of this part of the query, I should be good on the rest.

Here is my basic syntax. I can get the current age of the license for right now, but I really am not sure how to tabulate the age for each year since it was issued:

select floor(months_between(sysdate, a.issue_date) /12) as Years 
from
myTable a
where
a.license_number = 1234 --using this specific license number to see if my result returns what I want

Upvotes: 1

Views: 59

Answers (2)

MT0
MT0

Reputation: 167972

You can use a recursive query and add 12 months and then use EXTRACT to get the year at the end (don't use just the year in the recursive query or you will get rows for current year where the start date would be after the current date):

WITH license_years ( license_no, issue_date, license_age ) AS (
  SELECT license_no, issue_date, 0
  FROM   table_name
UNION ALL
  SELECT license_no, ADD_MONTHS( issue_date, 12 ), license_age + 1
  FROM   license_years
  WHERE  issue_date <= ADD_MONTHS( SYSDATE, -12 )
)
SELECT license_no,
       issue_date AS start_of_license_year,
       EXTRACT( YEAR FROM issue_date ) AS year,
       license_age
FROM   license_years
ORDER BY license_no,
       license_age

Which, for your test data:

CREATE TABLE table_name ( License_no, Issue_Date ) AS
SELECT 1234, DATE '2018-02-01' FROM DUAL UNION ALL
SELECT 5678, DATE '2016-03-15' FROM DUAL UNION ALL
SELECT 9012, DATE '1991-01-22' FROM DUAL UNION ALL
SELECT 3456, DATE '2005-09-25' FROM DUAL;

Outputs:

LICENSE_NO | START_OF_LICENSE_YEAR | YEAR | LICENSE_AGE
---------: | :-------------------- | ---: | ----------:
      1234 | 2018-02-01            | 2018 |           0
      1234 | 2019-02-01            | 2019 |           1
      1234 | 2020-02-01            | 2020 |           2
      3456 | 2005-09-25            | 2005 |           0
      3456 | 2006-09-25            | 2006 |           1
      3456 | 2007-09-25            | 2007 |           2
      3456 | 2008-09-25            | 2008 |           3
      3456 | 2009-09-25            | 2009 |           4
      3456 | 2010-09-25            | 2010 |           5
      3456 | 2011-09-25            | 2011 |           6
      3456 | 2012-09-25            | 2012 |           7
      3456 | 2013-09-25            | 2013 |           8
      3456 | 2014-09-25            | 2014 |           9
      3456 | 2015-09-25            | 2015 |          10
      3456 | 2016-09-25            | 2016 |          11
      3456 | 2017-09-25            | 2017 |          12
      3456 | 2018-09-25            | 2018 |          13
      3456 | 2019-09-25            | 2019 |          14
      5678 | 2016-03-15            | 2016 |           0
      5678 | 2017-03-15            | 2017 |           1
      5678 | 2018-03-15            | 2018 |           2
      5678 | 2019-03-15            | 2019 |           3
      5678 | 2020-03-15            | 2020 |           4
      9012 | 1991-01-22            | 1991 |           0
      9012 | 1992-01-22            | 1992 |           1
      9012 | 1993-01-22            | 1993 |           2
      9012 | 1994-01-22            | 1994 |           3
      9012 | 1995-01-22            | 1995 |           4
      9012 | 1996-01-22            | 1996 |           5
      9012 | 1997-01-22            | 1997 |           6
      9012 | 1998-01-22            | 1998 |           7
      9012 | 1999-01-22            | 1999 |           8
      9012 | 2000-01-22            | 2000 |           9
      9012 | 2001-01-22            | 2001 |          10
      9012 | 2002-01-22            | 2002 |          11
      9012 | 2003-01-22            | 2003 |          12
      9012 | 2004-01-22            | 2004 |          13
      9012 | 2005-01-22            | 2005 |          14
      9012 | 2006-01-22            | 2006 |          15
      9012 | 2007-01-22            | 2007 |          16
      9012 | 2008-01-22            | 2008 |          17
      9012 | 2009-01-22            | 2009 |          18
      9012 | 2010-01-22            | 2010 |          19
      9012 | 2011-01-22            | 2011 |          20
      9012 | 2012-01-22            | 2012 |          21
      9012 | 2013-01-22            | 2013 |          22
      9012 | 2014-01-22            | 2014 |          23
      9012 | 2015-01-22            | 2015 |          24
      9012 | 2016-01-22            | 2016 |          25
      9012 | 2017-01-22            | 2017 |          26
      9012 | 2018-01-22            | 2018 |          27
      9012 | 2019-01-22            | 2019 |          28
      9012 | 2020-01-22            | 2020 |          29

(Note: there is no 2020 row for license no. 3456 as it is not yet September.)

db<>fiddle here

Upvotes: 1

GMB
GMB

Reputation: 222462

You can use a recursive query for this:

with cte (license#, year, age) as (
    select license#, extract(year from issue_date), 0 from mytable
    union all
    select license#, year + 1, age + 1 from cte where year < extract(year from sysdate)
)
select * from cte order by license#, year

For the first two rows of your sample data, this query yields:

LICENSE# | YEAR | AGE
-------: | ---: | --:
    1234 | 2018 |   0
    1234 | 2019 |   1
    1234 | 2020 |   2
    5667 | 2016 |   0
    5667 | 2017 |   1
    5667 | 2018 |   2
    5667 | 2019 |   3
    5667 | 2020 |   4

Upvotes: 1

Related Questions