计算自颁发以来每年的许可证有效期oracle sql

k4ymrczo 于 2021-08-13 发布在 Java

关注(0)|答案(2)|浏览(345)

我有一个包含许可证记录的表。该表包含唯一的许可证号及其颁发日期。

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

我需要创建一个报告，计算自某个特定日期颁发许可证（四舍五入到最近的年份）以来每年的许可证有效期。在这种情况下，sysdate可以。
所以，我在找这样的东西：

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

这将输入到更多的查询中，这些查询根据费用计划计算每年的费用。费用每增加5年左右，但如果我能弄清楚这一部分的螺母和螺栓的查询，我应该很好的休息。
这是我的基本语法。我现在可以得到许可证的当前年龄，但我真的不知道如何将自许可证颁发以来每年的年龄制成表格：

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

sql oracle recursive-query

来源：https://stackoverflow.com/questions/62054066/calculate-age-of-license-for-each-year-since-issued-oracle-sql

2条答案

按热度按时间

wn9m85ua1#

您可以使用递归查询并添加12个月，然后使用 EXTRACT 要在结束时获取年份（不要在递归查询中仅使用年份，否则您将获得当前年份的行，其中开始日期将在当前日期之后）：

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

对于你的测试数据：

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;

输出：

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

（注：第3456号许可证没有2020行，因为现在还不是9月。）
db<>在这里摆弄

赞(0）回复(0）举报 2021-08-13

i5desfxk2#

您可以对此使用递归查询：

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

对于示例数据的前两行，此查询将生成：

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

赞(0）回复(0）举报 2021-08-13

我来回答

计算自颁发以来每年的许可证有效期oracle sql

2条答案

相关问题

热门标签

最新问答