June 13, 2005 at 9:52 am
I ran this code:
--K. Mike Bradley
--with help from:
--Remi Gregoire and Michael Du Bois @ qa.sqlservercentral.com
CREATE PROCEDURE TrendPeakTotal
-- declare the input parameters
@Tid INT,
@StartDate datetime,
@EndDate datetime,
-- declare the output parameters
@Total decimal(19,4) = NULL OUTPUT
AS
DECLARE @DataValue VARCHAR(50),
@PreviousPeak VARCHAR(50),
@StartValue VARCHAR(50),
@TotalUsed Decimal(19,4), --added
@PeakValues Decimal(19,4) --added
DECLARE curTrend CURSOR FOR
SELECT DATA_VALUE_
FROM TrendData
WHERE TID_ = @TID AND DATE_STAMP_ >= @Startdate AND DATE_STAMP_ < @EndDate AND RECORD_TYPE_ = 2
ORDER BY DATE_STAMP_ ASC
OPEN curTrend
FETCH NEXT FROM curTrend INTO @DataValue
-- Save the initial value for subtraction as a last step.
INSERT INTO #Test(FirstValue) Values(cast(@DataValue AS Decimal(19,4))) --Added
SET @StartValue = @DataValue
SET @PreviousPeak = @DataValue --Added or 1st If would fail as PreviousPeak = -1
WHILE @@FETCH_STATUS=0
BEGIN
IF (cast(@DataValue AS Decimal(19,4))<Cast(@PreviousPeak AS Decimal(19,4))) -- we've just gone over a reset
Begin
INSERT INTO #Test(PeakValue) Values(cast(@PreviousPeak AS Decimal(19,4))) --Added
End
SET @PreviousPeak = @DataValue
FETCH NEXT FROM curTrend INTO @DataValue
END
INSERT INTO #Test(PeakValue) Values(cast(@DataValue AS Decimal(19,4))) --Added
SELECT @PeakValues = Sum(PeakValue)From #Test
Set @Total = cast(@PeakValues as decimal(19,4)) - cast(@StartValue AS Decimal(19,4))
CLOSE curTrend
DEALLOCATE curTrend
--sets the value of total to the output var
Select @Total
return 0
GO
and I get this error:
Invalid object name '#Test'
??????
June 13, 2005 at 9:54 am
The temp table #Test must be created before you can run this code.
Did you copy our exemples exactly?
Did you forget to swap the table names?
June 13, 2005 at 10:57 am
I just re-wrote the whole thing. Does this look like what I want?
--K. Mike Bradley
--with help from:
--Remi Gregoire and Michael Du Bois @ qa.sqlservercentral.com
CREATE PROCEDURE TrendPeakTotal
-- declare the input parameters
@Tid INT,
@StartDate datetime,
@EndDate datetime,
-- declare the output parameters
@Total decimal(19,4) = 0 OUTPUT
AS
DECLARE
@CurentValue VARCHAR(50),
@PreviousValue Decimal(19,4),
@StartValue Decimal(19,4)
DECLARE curTrend CURSOR FOR
SELECT DATA_VALUE_
FROM TrendData
WHERE TID_ = @TID AND DATE_STAMP_ >= @Startdate AND DATE_STAMP_ < @EndDate AND RECORD_TYPE_ = 2
ORDER BY DATE_STAMP_ ASC
OPEN curTrend
--Get the first Data_Value_ and save it for subtraction as a last step.
FETCH NEXT FROM curTrend INTO @CurentValue
SET @StartValue = cast(@CurentValue AS Decimal(19,4))
--initialize @PreviousPeak to first Data_Value_
SET @PreviousValue = @StartValue
WHILE @@FETCH_STATUS=0
FETCH NEXT FROM curTrend INTO @CurentValue
BEGIN
IF (cast(@CurentValue AS Decimal(19,4))) < @PreviousValue -- we've just gone over a reset
Begin
Set @Total = @Total + @PreviousValue
End
SET @PreviousValue = cast(@CurentValue AS Decimal(19,4))
END
--subtract our first DATA_VALUE_ from the total.
SET @Total = @Total - @StartValue
--add the final value
SET @Total = @Total + cast(@CurentValue AS Decimal(19,4))
CLOSE curTrend
DEALLOCATE curTrend
return 0
GO
June 13, 2005 at 11:19 am
You tell me. I can't see what's the result of the execution.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply