OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Add Months

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Add Months [message #262373]

Sun, 26 August 2007 12:47

ravi.megharaj
Messages: 3
Registered: August 2007

Junior Member

Hi,

If anybody could help me out in writing a procedure for the following case:

I need to get the output based on count or sum of a particular field along with each month ends. Example:

Month End Count
07/31/2007 10
06/30/2007 20
05/31/2007 40
04/30/2007 100
.
.
.
.
08/31/2006 20

07/31/2007 as Start_Date and 08/31/2006 being End Date.

I would like this procedure to be written using cursors rather that direct insert.

Please provide me the solution for the same.

Thx in Advance Cool

Report message to a moderator

Re: Add Months [message #262374 is a reply to message #262373]

Sun, 26 August 2007 13:02

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

This can done with one SQL query.
Why don't you want SQL but absolutly want this in PL/SQL?

In addition,
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Finally, you said what you want but not what you have. Post a description of your table. Post a test case (create table and insert statements to fill it) if you want us to provide a working solution.

Regards
Michel

Report message to a moderator

Re: Add Months [message #262375 is a reply to message #262374]

Sun, 26 August 2007 13:27

ravi.megharaj
Messages: 3
Registered: August 2007

Junior Member

I want to run this procedure every month. Hence I want to give Procedure Input, with stat date and end date.

Regards,
Ravi

Report message to a moderator

Re: Add Months [message #262376 is a reply to message #262375]

Sun, 26 August 2007 13:31

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

You can put it in a script with the same parameters.
Or put the query in a procedure with the same parameters and don't use cursor just a query.

Regards
Michel

Report message to a moderator

Re: Add Months [message #262378 is a reply to message #262376]

Sun, 26 August 2007 16:22

cmerry
Messages: 109
Registered: November 2005
Location: Idaho

Senior Member

Quote:

I would like this procedure to be written using cursors rather that direct insert.

Technically an INSERT (which I think you meant SELECT???) is a cursor too...just an implicit one

Anyway, here's a query based on the following data

     EMPNO ENAME      JOB              MGR HIREDATE     
---------- ---------- --------- ---------- -----------
      7369 SMITH      CLERK           7902 17-DEC-1980
      7499 ALLEN      SALESMAN        7698 20-FEB-1981
      7521 WARD       SALESMAN        7698 22-FEB-1981
      7566 JONES      MANAGER         7839 02-APR-1981
      7654 MARTIN     SALESMAN        7698 28-SEP-1981
      7698 BLAKE      MANAGER         7839 01-MAY-1981
      7782 CLARK      MANAGER         7839 09-JUN-1981
      7788 SCOTT      ANALYST         7566 19-APR-1987
      7839 KING       PRESIDENT            17-NOV-1981
      7844 TURNER     SALESMAN        7698 08-SEP-1981
      7876 ADAMS      CLERK           7788 23-MAY-1987
      7900 JAMES      CLERK           7698 03-DEC-1981
      7902 FORD       ANALYST         7566 03-DEC-1981
      7934 MILLER     CLERK           7782 23-JAN-1982

SELECT LAST_DAY(hiredate), COUNT(*)
FROM   emp
GROUP  BY LAST_DAY(hiredate);

LAST_DAY(HI   COUNT(*)
----------- ----------
28-FEB-1981          2
30-JUN-1981          1
31-MAY-1987          1
30-APR-1987          1
30-APR-1981          1
31-DEC-1980          1
30-NOV-1981          1
31-MAY-1981          1
30-SEP-1981          2
31-DEC-1981          2
31-JAN-1982          1

Then just add it to a procedure with parameters

CREATE OR REPLACE PROCEDURE p 
 ( p_start_date DATE
 , p_end_date   DATE )
IS
  CURSOR cur IS
  SELECT LAST_DAY(hiredate) month_end, COUNT(*) count
  FROM   emp
  WHERE  LAST_DAY(hiredate) BETWEEN p_start_date AND p_end_date
  GROUP  BY LAST_DAY(hiredate);
BEGIN
  FOR rec IN cur LOOP
    -- Process data
    dbms_output.put_line(rec.month_end || ': ' || rec.count);
  END LOOP;
END p;
/

SET SERVEROUT ON
CALL P(TO_DATE('31-DEC-1980', 'DD-MON-YYYY'), TO_DATE('31-MAY-1981', 'DD-MON-YYYY'));

28-FEB-1981: 2
30-APR-1981: 1
31-DEC-1980: 1
31-MAY-1981: 1

Report message to a moderator