CODEWITHSUNDEEP
sqloracle-database
greg
greg

Reputation: 35

Oracle SQL: loop through year

I need to run this query to get 5 years data (2015 to 2019), and I am wondering if there is a way to automatically loop through year, instead of manually changing the year (e.g., from 2015 to 2016) and running this query 5 times? Any help will be appreciated! Thanks!

Select ID,program, open_date, close_date
From clients
Where open_date=to_date('01/01/2015','mm/dd/yyyy')
 and close_date=to_date('12/31/2015','mm/dd/yyyy')

Upvotes: 3

Views: 568

Answers (3)

astentx
astentx

Reputation: 6751

You can always generate calendar you need and join it with your query.

with cal as (
  select add_months(date '2015-01-01', (level - 1)*12) as start_dt,
    add_months(date '2015-12-31', (level - 1)*12) as end_dt
  from dual
  connect by level <= 5
)
Select c.ID, c.program, c.open_date, c.close_date
From clients c
  join cal
    on c.open_date=cal.start_dt and c.close_date=cal.end_dt

Upvotes: 2

davidm
davidm

Reputation: 1750

Take a look at the following code snippet:

SELECT EXTRACT(YEAR FROM TO_DATE('01.01.2015', 'dd.mm.yyyy')) + ROWNUM - 1 AS "YEAR"
    FROM dual
CONNECT BY ROWNUM <= 5

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142720

Row generator it is. For example:

SQL> with period (start_year, end_year) as
  2    (select 2015, 2020 from dual)
  3  select d.dummy, p.start_year + level - 1 as year
  4  from dual d cross join period p
  5  connect by level <= end_year - start_year
  6  order by year;

D       YEAR
- ----------
X       2015
X       2016
X       2017
X       2018
X       2019

SQL>

Applied to your code (can't test it, don't have your tables):

with 
period (start_year, end_year) as
  (select 2015, 2020 from dual),
select c.id, c.program, c.open_date, c.close_date
from clients c cross join period p
where open_date  = add_months(trunc(to_date(p.start_year, 'yyyy'), 'yyyy'), 12 * (level - 1))
  and close_date = add_months(trunc(to_date(p.start_year, 'yyyy'), 'yyyy'), 12 * (level )) - 1
connect by level <= p.end_year - p.start_year;

because those values produce

SQL> with
  2  period (start_year, end_year) as
  3    (select 2015, 2020 from dual)
  4  select add_months(trunc(to_date(p.start_year, 'yyyy'), 'yyyy'), 12 * (level - 1)) a,
  5         add_months(trunc(to_date(p.start_year, 'yyyy'), 'yyyy'), 12 * (level )) - 1 b
  6  from period p
  7  connect by level <= end_year - start_year;

A          B
---------- ----------
01.01.2015 31.12.2015
01.01.2016 31.12.2016
01.01.2017 31.12.2017
01.01.2018 31.12.2018
01.01.2019 31.12.2019

SQL>

Upvotes: 2

Related Questions