Muthu Annamalai schrieb:

> I am trying to create a stored procedure for a report which provides
> monthly total sales.
>
> The report I am trying to generate is to get total sales for each
> month for last 12 months. I have values in my Receipt Table.
>
> The incomplete stored procedure is as follows
>
> ----Stored Procedure------
>
> CREATE PROCEDURE REPORT_MONTHLY_SALES
> RETURNS (
> JAN NUMERIC(15,2),
> FEB NUMERIC(15,2),
> MAR NUMERIC(15,2),
> APR NUMERIC(15,2),
> MAY NUMERIC(15,2),
> JUN NUMERIC(15,2),
> JUL NUMERIC(15,2),
> AUG NUMERIC(15,2),
> SEP NUMERIC(15,2),
> OCT NUMERIC(15,2),
> NOV NUMERIC(15,2),
> DCR NUMERIC(15,2))
> AS

DECLARE VARIABLE MONTHNUM INTEGER;
DECLARE VARIABLE AMT NUMERIC(15,2);

> BEGIN

/* intialize all return vars, since they don't have to be filled
by the select */
JAN = 0;
FEB = 0;
...

FOR SELECT EXTRACT ( MONTH FROM RECEIPTDATE ), SUM ( AMOUNT )
FROM RECEIPT
WHERE .. /* add your other where here (like YEAR..) */
GROUP BY 1 /* group it by the month */
INTO :MONTHNUM, :AMT
DO BEGIN
IF ( MONTHNUM ) = 1 then JAN = AMT;
ELSE IF ( MONTHNUM ) = 2 then FEB = AMT;
...
END


Luc.