(Resending this since the newsgroups were apparently down)

I'm trying to create a trending report and can't get the difference
calculation field to have any values. I want it to return the
difference of the current minutes and that of the previous record of the
same group. Could someone look at the following stored procedures and
give me some hints?

ALTER PROCEDURE TRENDING( PFROM DATE
, PTHRU DATE )
RETURNS ( CARRIER VARCHAR( 6 )
, USAGE_DATE DATE
, NPA CHAR( 3 )
, NXX CHAR( 3 )
, MSG INTEGER
, MSG_T INTEGER
, MINUTES DECIMAL ( 18, 2 )
, MIN_T DECIMAL ( 18, 2 )
, MIN_DIFF DECIMAL ( 18, 2 ) )
AS
DECLARE VARIABLE TMINS DECIMAL(18,2);
/*DECLARE VARIABLE MINDIFF DECIMAL(18,2);*/
BEGIN
FOR SELECT CARRIER
, USAGE_DATE
, NPA
, NXX
, SUM(MSG) AS MSG
, SUM(MSG_T) AS MSG_T
, SUM(MINUTES) AS MINUTES
, SUM(MIN_T) AS MIN_T
FROM TRENDING_PRE(:PFROM, :PTHRU)
GROUP BY
CARRIER
, USAGE_DATE
, NPA
, NXX


INTO
CARRIER
, USAGE_DATE
, NPA
, NXX
, MSG
, MSG_T
, MINUTES
, MIN_T
DO
begin

SELECT MINUTES
FROM TRENDING_PRE(:PFROM, :PTHRU) T1
WHERE T1.CARRIER = CARRIER
AND T1.NPA = NPA
AND T1.NXX = NXX
AND T1.USAGE_DATE =
(SELECT NEWDATE
FROM INCMONTH(T1.USAGE_DATE, -1))
INTO
TMINS;

MIN_DIFF = MINUTES - TMINS;

SUSPEND;
end
END


The Trending_Pre is used to bring the 2 kinds of minutes (Min_T and
Minutes) onto the same record, like a pivot table. It is as follows:

ALTER PROCEDURE TRENDING_PRE( PFROM DATE
, PTHRU DATE )
RETURNS ( CARRIER VARCHAR( 6 )
, USAGE_DATE DATE
, NPA CHAR( 3 )
, NXX CHAR( 3 )
, MSG INTEGER
, MSG_T INTEGER
, MINUTES DECIMAL ( 18, 2 )
, MIN_T DECIMAL ( 18, 2 ) )
AS
BEGIN
FOR SELECT
CARRIER
, USAGE_DATE
, NPA
, NXX
, CAST(SUM(MSG) AS INTEGER) AS MSG
, CAST(0 AS INTEGER) AS MSG_T
, CAST(SUM(MINUTES) AS NUMERIC(15,2)) AS MINUTES
, CAST(0 AS NUMERIC(15,2)) AS MIN_T
FROM MSGMOU
WHERE
CALL_DATE >= :PFROM
AND CALL_DATE <= :PTHRU
AND ORIG_TERM = 'O'
GROUP BY
CARRIER
, USAGE_DATE
, NPA
, NXX

UNION
SELECT
CARRIER
, USAGE_DATE
, NPA
, NXX
, CAST(0 AS INTEGER) AS MSG
, CAST(SUM(MSG) AS INTEGER) AS MSG_T
, CAST(0 AS NUMERIC(15,2)) AS MINUTES
, CAST(SUM(MINUTES) AS NUMERIC(15,2)) AS MIN_T
FROM MSGMOU
WHERE
CALL_DATE >= :PFROM
AND CALL_DATE <= :PTHRU
AND ORIG_TERM = 'T'
GROUP BY
CARRIER
, USAGE_DATE
, NPA
, NXX

INTO
CARRIER
, USAGE_DATE
, NPA
, NXX
, MSG
, MSG_T
, MINUTES
, MIN_T
DO
SUSPEND;
END

Here is the IncMonth procedure (Yes, I know it won't work in all
situations, like the 31st of the month, but will for all instances we
will be using it in). We don't want to add UDFs of our own because we
don't want to complicate the install of Firebird:

ALTER PROCEDURE INCMONTH( PDATE DATE
, PNUM INTEGER )
RETURNS ( NEWDATE DATE )
AS
DECLARE VARIABLE VDATE DATE;
DECLARE VARIABLE VMTH INTEGER;
DECLARE VARIABLE VDAY INTEGER;
DECLARE VARIABLE VYEAR INTEGER;
DECLARE VARIABLE NUMYRS INTEGER;
BEGIN
/* DECODE DATE */
VMTH = EXTRACT(MONTH FROM PDATE);
VDAY = EXTRACT(DAY FROM PDATE);
VYEAR = EXTRACT(YEAR FROM PDATE);

/* CALCULATE NEW DATE */
VMTH = VMTH + PNUM;
NUMYRS = DIV(VMTH, 12);
VYEAR = VYEAR + NUMYRS;
VMTH = MOD(VMTH, 12);

IF (VMTH < 1) THEN
BEGIN
VMTH = 12 + VMTH;
VYEAR = VYEAR - 1;
END

/* ENCODE AND RETURN NEW DATE */
NEWDATE = CAST(VMTH AS VARCHAR(2)) || '/' ||
CAST(VDAY AS VARCHAR(2)) || '/' ||
CAST(VYEAR AS VARCHAR(4));
SUSPEND;
END

Thanks




Don Gollahon
gollahon@...
"The Original GenSoft Prodigal"