CODEWITHSUNDEEP
oracle-databaseplsql
A.D
A.D

Reputation: 35

Stored procedure to get one year of data splitted per months

I am relatively new to Oracle so i'd really appreciate some help.

I have a query like:

SELECT * FROM reservations WHERE date between (date1) and (date2).

What I need is to get all the reservations within the interval : today's date and today's date -1 year, so basically 1 year of history.

I want to run the above query with interval of 1 months, and export the query set to excel. I need some help in the logic of the loop (create a stored procedure or function), as i will think later for the export to excel.

Upvotes: 1

Views: 193

Answers (2)

kanagaraj
kanagaraj

Reputation: 442

Here is the procedure to create the queries month-wise starting from sysdate . 

create or replace procedure monthwise_query 
as
t_date DATE := TRUNC(SYSDATE) ;
c_date DATE ;
BEGIN

FOR i in 1..12
LOOP
c_date := t_date;

  dbms_output.put_line('select * from reservations where rev_date between to_date('''||c_date||''',''DD-MON-YY'') and (select add_months(to_date('''||c_date||''',''DD-MON-YY''),-1) from dual);');

 select add_months(c_date,-1)  
 into t_date
 from dual;

END LOOP;
END;

- It can be extended with UTIL_FILE utility inside this procedure to write each query result to single/multiple files.

- SPOOL from SQL*plus for each query will be easy

- UNIX script to loop through each monthwise-query to load to file also possible

Upvotes: 0

krokodilko
krokodilko

Reputation: 36137

This will give all records from 1 year back to today:

SELECT * FROM reservations 
WHERE date >= trunc( sysdate ) - interval '1' year
  AND date < trunc( sysdate ) + interval '1' day

I want to run the above query with interval of 1 months,

I understand that you want to run this query 12 times, each time for another monthly period. If yes, then run this query 12 times changing the parameter X (within SELECT 1 As X FROM dual subquery), beginning from 12 to 1 (or 1 to 12):

SELECT * FROM reservations 
CROSS JOIN (
   SELECT 1 As X FROM dual
) x
WHERE date >= trunc( sysdate ) - x * interval '1' month
  AND date < trunc( sysdate ) + interval '1' day - ( x - 1 ) * interval '1' month

Upvotes: 1

Related Questions