Hi:

Have two sp's here. Summary1Join populates a working table to run
the counts against.
Is there some "better" way to do this avoiding use of a working table?
I think I did this
because trying it all in one fell swoop became incomprehensible.

Thanks.

Best regards

SET TERM ## ;

CREATE PROCEDURE SUMMARY1COUNT(START_DATE DATE, CLOSE_DATE DATE,
ASSLEVY_START DATE, ASSLEVY_CANCEL DATE, REPORT_YEAR INT)
RETURNS (TotExist INT, TotCancel INT, AssessCnt INT, AssessPaid
DOUBLE PRECISION, TotAssPaid DOUBLE PRECISION, TRGRecvd INT, TotAssoc
INT)
AS
BEGIN

EXECUTE PROCEDURE SUMMARY1JOIN;

SELECT COUNT (DISTINCT S1.ID)
FROM Sum1 S1
WHERE ((S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' )
AND
S1.INITPAID < :START_DATE ) OR (S1.STATUSMEM = 'CANCELLED'
AND
S1.MEMCANCEL > :CLOSE_DATE AND S1.INITPAID < :START_DATE
)
INTO TotExist;

SELECT COUNT (DISTINCT S1.ID)
FROM Sum1 S1
WHERE S1.STATUSMEM = 'CANCELLED' AND S1.MEMCANCEL >= :START_DATE
AND
S1.MEMCANCEL <= :CLOSE_DATE
INTO TotCancel;

SELECT COUNT (S1.ASSESSLEV)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' OR
S1.STATUSMEM = 'CANCELLED') AND
(S1.ASSESSLEV = :ASSLEVY_START )
INTO AssessCnt;

SELECT SUM (S1.ASSESSAMT)
FROM Sum1 S1
WHERE ((S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' OR
STATUSMEM = 'CANCELLED') AND S1.ASSESSLEV = :ASSLEVY_START
AND
S1.ASSESSPD >= :START_DATE AND S1.ASSESSPD <= :CLOSE_DATE
AND
S1.ASSESSPD <= :ASSLEVY_CANCEL )
INTO AssessPaid;

SELECT SUM (S1.ASSESSAMT)
FROM Sum1 S1
WHERE ((S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' OR
STATUSMEM = 'CANCELLED') AND S1.ASSESSLEV = :ASSLEVY_START
AND
S1.ASSESSPD <= :ASSLEVY_CANCEL AND S1.ASSESSPD <=
:CLOSE_DATE)
INTO TotAssPaid;

SELECT COUNT (S1.REFRECVD)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ACTIVE' OR S1.STATUSMEM = 'ASSOCIATE' OR
STATUSMEM = 'CANCELLED') AND S1.REFRECVD IS NOT NULL AND
S1.REFRECVD >= :START_DATE AND S1.REFRECVD <= :CLOSE_DATE
INTO TRGRecvd;

SELECT COUNT (*)
FROM Sum1 S1
WHERE (S1.STATUSMEM = 'ASSOCIATE' AND S1.STATUSASOC = 'ACTIVE' AND
S1.ASOCACTIVE <= :CLOSE_DATE ) OR (S1.STATUSASOC =
'CANCELLED' AND
S1.ASOCANCEL > :CLOSE_DATE )
INTO TotAssoc;

SUSPEND;
END ##

SET TERM ; ##


SET TERM ## ;
CREATE PROCEDURE SUMMARY1JOIN
AS
BEGIN

DELETE FROM SUM1;

Insert into Sum1
(ID, STATUSMEM, RENEWDTE, IDINITPAY, INITFEE, INITDUES,
INITPAID, MEMCANCEL, REVRECVD, REFRECVD, ASSESSLEV, ASSESSAMT,
ASSESSPD, IDPARENT, IDASSOC, STATUSASOC, ASOCACTIVE, ASOCANCEL)
SELECT M.ID AS ID, M.STATUS_MEM AS STATUSMEM,
M.RENEW_DTE AS RENEWDTE, M1.ID AS IDINITPAY,
M1.APPL_FEE AS INITFEE, M1.FIRST_DUES AS INITDUES,
M1.PAID1_MEM AS INITPAID, M1.CANCL_DATE AS MEMCANCEL,
M1.REV_RECVD AS REVRECVD, M1.REF_RECVD AS REFRECVD,
M1.REFAS_LEV AS ASSESSLEV, M1.REFAS_FEE AS ASSESSAMT,
M1.REFAS_PAID AS ASSESSPD, A.ID AS IDPARENT, A.ASSOCID AS IDASSOC,
A.STATUS_MEM AS STATUSASOC, A.INITL_DATE AS ASOCACTIVE,
A.CANCL_DATE AS ASOCANCEL
FROM members M
INNER JOIN mempay1 M1
ON (M.ID = M1.ID)
LEFT OUTER JOIN Assoc_c A
ON (M1.ID = A.ID);

END ##
SET TERM ; ##