""Don Gollahon"" <gollahon@...> wrote in message
news:<!~!UENERkVCMDkAAQACAAAAAAAAAAAAAAAAABgAAAAAAAAAEkdDtKXDjUiZkxfIbAy
u28KAAAAQAAAADlhtfeoXmU+EVXZWZQ1k+QEAAAAA@...>...

> (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 )

...

I changed this procedure to the following and now min_diff gets values
but they are based using the First Minutes value as the Tmin value for
all records. Therefore it is still incorrect. What I'm looking for is
running differences to be returned:

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(:USAGE_DATE, -1))
INTO
TMINS;

IF (:TMINS IS NULL) THEN
TMINS = :MINUTES;

MIN_DIFF = :MINUTES - :TMINS;

SUSPEND;

end
END



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