Home » SQL & PL/SQL » SQL & PL/SQL » Add Months
Add Months [message #262373] Sun, 26 August 2007 12:47 Go to next message
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
Re: Add Months [message #262374 is a reply to message #262373] Sun, 26 August 2007 13:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Add Months [message #262375 is a reply to message #262374] Sun, 26 August 2007 13:27 Go to previous messageGo to next message
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
Re: Add Months [message #262376 is a reply to message #262375] Sun, 26 August 2007 13:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Add Months [message #262378 is a reply to message #262376] Sun, 26 August 2007 16:22 Go to previous messageGo to next message
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
Re: Add Months [message #262447 is a reply to message #262378] Mon, 27 August 2007 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I hope you had to fun to post that solution.

Regards
Michel
Re: Add Months [message #262580 is a reply to message #262447] Mon, 27 August 2007 11:08 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Yes it was rather enjoyable...thank you for your concern!
Re: Add Months [message #262589 is a reply to message #262378] Mon, 27 August 2007 12:20 Go to previous message
ravi.megharaj
Messages: 3
Registered: August 2007
Junior Member
Thank u so much. Let me try with this one.
Previous Topic: Sql qyery
Next Topic: Forgot login info
Goto Forum:
  


Current Time: Thu Dec 08 08:38:56 CST 2016

Total time taken to generate the page: 0.10576 seconds