Output Parameters for a newbie
-
SET QUOTED_IDENTIFIER OFF
SET NOCOUNT ON
DECLARE @SumOfMaximums FLOAT, @DataValue FLOAT, @PreviousDataValue FLOAT, @SeqNo INT,@Tid INT, @MaximumValue FLOAT
SET @Tid=11
CREATE TABLE [#TRENDDATA] ( [SEQUENCENUMBER_] [int] NOT NULL ,
[TID_] [int] NOT NULL ,
[DATE_STAMP_] [datetime] NULL ,
[RECORD_TYPE_] [smallint] NULL ,
[DATA_VALUE_] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS_FLAGS_] [smallint] NULL
)
INSERT INTO #TrendData VALUES (1,11,'2003-06-10 14:00:00',2,"20.00",0)
INSERT INTO #TrendData VALUES (13,11,'2003-06-11 14:00:00',2,"68.234",0)
INSERT INTO #TrendData VALUES (15,11,'2003-06-11 16:00:00',2,"1.7",0)
INSERT INTO #TrendData VALUES (29,11,'2003-06-12 20:00:00',2,"75.41665",0)
INSERT INTO #TrendData VALUES (30,11,'2003-06-12 22:00:00',2,"3.2",0)
INSERT INTO #TrendData VALUES (44,11,'2003-06-13 22:00:00',2,"87.85751",0)
INSERT INTO #TrendData VALUES (45,11,'2003-06-14 00:00:00',2,"87.85751",0)
INSERT INTO #TrendData VALUES (46,11,'2003-06-14 02:00:00',2,"87.85751",0)
INSERT INTO #TrendData VALUES (47,11,'2003-06-14 04:00:00',2,"2.24",0)
INSERT INTO #TrendData VALUES (48,11,'2003-06-14 06:00:00',2,"3.5",0)
INSERT INTO #TrendData VALUES (55,11,'2003-06-14 20:00:00',2,"87.85751",0)
INSERT INTO #TrendData VALUES (56,11,'2003-06-14 22:00:00',2,"87.85751",0)
DECLARE @myT TABLE([RecordID] [int] IDENTITY (1, 1) NOT NULL ,DATA_VALUE_ FLOAT)
INSERT INTO @myT(DATA_VALUE_) VALUES (-1)
INSERT INTO @myT(DATA_VALUE_)
SELECT DATA_VALUE_
FROM #TrendData
WHERE TID_=@Tid
ORDER BY DATE_STAMP_ ASC
INSERT INTO @myT(DATA_VALUE_) VALUES (-1)--this IF I want last ROW to be included
--SELECT A.RecordID , A.DATA_VALUE_ as PREC, B.DATA_VALUE_ as val
SELECT @MaximumValue=SUM(B.DATA_VALUE_)
FROM @myT A INNER JOIN @myT B
ON A.RecordID=B.RecordID+1
WHERE A.DATA_VALUE_<B.DATA_VALUE_
DROP TABLE [#TRENDDATA]
SELECT @MaximumValue
Kindest Regards,Vasc
-
I think I got It:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRENDDATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TRENDDATA]
GO
CREATE TABLE [dbo].[TRENDDATA] (
[SEQUENCENUMBER_] [int] NOT NULL ,
[TID_] [int] NOT NULL ,
[DATE_STAMP_] [datetime] NULL ,
[RECORD_TYPE_] [smallint] NULL ,
[DATA_VALUE_] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS_FLAGS_] [smallint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[TRENDDATA] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[TID_],
[SEQUENCENUMBER_]
 
ON [PRIMARY]GO
INSERT INTO TRENDDATA_ VALUES (1,11,2003-06-10 14:00:00,2,'20.00',0);
INSERT INTO TRENDDATA_ VALUES (2,11,2003-06-10 16:00:00,2,'35.484997',0);
INSERT INTO TRENDDATA_ VALUES (3,11,2003-06-10 18:00:00,2,'65.895',0);
INSERT INTO TRENDDATA_ VALUES (4,11,2003-06-10 20:00:00,2,'65.895',0);
INSERT INTO TRENDDATA_ VALUES (5,11,2003-06-10 22:00:00,2,'65.895',0);
INSERT INTO TRENDDATA_ VALUES (6,11,2003-06-11 00:00:00,2,'65.895',0);
INSERT INTO TRENDDATA_ VALUES (7,11,2003-06-11 02:00:00,2,'67.234',0);
INSERT INTO TRENDDATA_ VALUES (8,11,2003-06-11 04:00:00,2,'67.235',0);
INSERT INTO TRENDDATA_ VALUES (9,11,2003-06-11 06:00:00,2,'67.236',0);
INSERT INTO TRENDDATA_ VALUES (10,11,2003-06-11 08:00:00,2,'68.234',0);
INSERT INTO TRENDDATA_ VALUES (11,11,2003-06-11 10:00:00,2,'68.234',0);
INSERT INTO TRENDDATA_ VALUES (12,11,2003-06-11 12:00:00,2,'68.234',0);
INSERT INTO TRENDDATA_ VALUES (13,11,2003-06-11 14:00:00,2,'68.234',0);
INSERT INTO TRENDDATA_ VALUES (15,11,2003-06-11 16:00:00,2,'1.7',0);
INSERT INTO TRENDDATA_ VALUES (16,11,2003-06-11 18:00:00,2,'1.8',0);
INSERT INTO TRENDDATA_ VALUES (17,11,2003-06-11 20:00:00,2,'1.9',0);
INSERT INTO TRENDDATA_ VALUES (18,11,2003-06-11 22:00:00,2,'2.0',0);
INSERT INTO TRENDDATA_ VALUES (19,11,2003-06-12 00:00:00,2,'3',0);
INSERT INTO TRENDDATA_ VALUES (20,11,2003-06-12 02:00:00,2,'5.57',0);
INSERT INTO TRENDDATA_ VALUES (21,11,2003-06-12 04:00:00,2,'8',0;
INSERT INTO TRENDDATA_ VALUES (22,11,2003-06-12 06:00:00,2,'22.45',0);
INSERT INTO TRENDDATA_ VALUES (23,11,2003-06-12 08:00:00,2,'65.895',0);
INSERT INTO TRENDDATA_ VALUES (24,11,2003-06-12 10:00:00,2,'65.895',0);
INSERT INTO TRENDDATA_ VALUES (25,11,2003-06-12 12:00:00,2,'65.895',0);
INSERT INTO TRENDDATA_ VALUES (26,11,2003-06-12 14:00:00,2,'75.41665',0);
INSERT INTO TRENDDATA_ VALUES (27,11,2003-06-12 16:00:00,2,'75.41665',0);
INSERT INTO TRENDDATA_ VALUES (28,11,2003-06-12 18:00:00,2,'75.41665',0);
INSERT INTO TRENDDATA_ VALUES (29,11,2003-06-12 20:00:00,2,'75.41665',0);
INSERT INTO TRENDDATA_ VALUES (30,11,2003-06-12 22:00:00,2,'3.2',0);
INSERT INTO TRENDDATA_ VALUES (31,11,2003-06-13 00:00:00,2,'7.8',0);
INSERT INTO TRENDDATA_ VALUES (33,11,2003-06-13 02:00:00,2,'22.6',0);
INSERT INTO TRENDDATA_ VALUES (35,11,2003-06-13 04:00:00,2,'35.89',0);
INSERT INTO TRENDDATA_ VALUES (36,11,2003-06-13 06:00:00,2,'42.99',0);
INSERT INTO TRENDDATA_ VALUES (37,11,2003-06-13 08:00:00,2,'75.41665',0);
INSERT INTO TRENDDATA_ VALUES (38,11,2003-06-13 10:00:00,2,'75.41665',0);
INSERT INTO TRENDDATA_ VALUES (39,11,2003-06-13 12:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (40,11,2003-06-13 14:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (41,11,2003-06-13 16:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (42,11,2003-06-13 18:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (43,11,2003-06-13 20:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (44,11,2003-06-13 22:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (45,11,2003-06-14 00:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (46,11,2003-06-14 02:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (47,11,2003-06-14 04:00:00,2,'2.24',0);
INSERT INTO TRENDDATA_ VALUES (48,11,2003-06-14 06:00:00,2,'3.5',0);
INSERT INTO TRENDDATA_ VALUES (49,11,2003-06-14 08:00:00,2,'6.9',0);
INSERT INTO TRENDDATA_ VALUES (50,11,2003-06-14 10:00:00,2,'22.5',0);
INSERT INTO TRENDDATA_ VALUES (51,11,2003-06-14 12:00:00,2,'35.7',0);
INSERT INTO TRENDDATA_ VALUES (52,11,2003-06-14 14:00:00,2,'45.98',0);
INSERT INTO TRENDDATA_ VALUES (53,11,2003-06-14 16:00:00,2,'65.3',0);
INSERT INTO TRENDDATA_ VALUES (54,11,2003-06-14 18:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (55,11,2003-06-14 20:00:00,2,'87.85751',0);
INSERT INTO TRENDDATA_ VALUES (56,11,2003-06-14 22:00:00,2,'87.85751',0);
GO
-
 
ON [PRIMARY]GO
The Close paranthesis got replaced with a smiley LOL!
-
You're creating a table named TRENDDATA and inserting into TRENDDATA_
btw, did you test my code? I ran it on the sample data you posted yesterday and got results that look correct to me.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
I loaded it and started massaging it. I still need to set up CONVERTS before it will run.
Here is what I have so far:
CREATE PROCEDURE sp_TrendPeakTotal
-- declare the input parameters
@Tid INT,
@StartDate datetime,
@EndDate datetime,
-- declare the output parameters
@Total decimal(19,4) = 0.0 OUTPUT
AS
DECLARE @SumOfMaximums FLOAT,
@DataValue VARCHAR(50),
@PreviousPeak VARCHAR(50),
@StartValue VARCHAR(50)
SET @SumOfMaximums = 0
SET @PreviousPeak = -1
DECLARE curTrend CURSOR FOR
SELECT DATA_VALUE_
FROM TrendData
WHERE TID_ = @TID AND @StartDate <= DATE_STAMP_ AND @EndDate >= DATE_STAMP_ 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.
SET @StartValue = @DataValue
WHILE @@FETCH_STATUS=0
BEGIN
IF (@DataValue<@PreviousPeak) -- we've just gone over a reset
SET @SumOfMaximums = @SumOfMaximums + @PreviousPeak
SET @PreviousPeak = @DataValue
FETCH NEXT FROM curTrend INTO @DataValue
END
-- Add the last one. Remove this if you don't want to consider the last set of measurements.
SET @SumOfMaximums = @SumOfMaximums + @PreviousPeak
CLOSE curTrend
DEALLOCATE curTrend
SET @Total = @SumOfMaximums
GO
-
Edited: Scrapped my previous post as it was returning incorrect data. Based on the following data (from K-Michael just cleaned up) the correct answer is 299.3657. My sp was returning 299.0000. I will fix this or be bald by tomorrow night. Mike
**********************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TRENDDATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TRENDDATA]
GO
CREATE TABLE [dbo].[TRENDDATA] (
[ID] [int] NOT NULL ,
[TID_] [int] NOT NULL ,
[DATE_STAMP_] [datetime] NULL ,
[RECORD_TYPE_] [smallint] NULL ,
[DATA_VALUE_] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STATUS_FLAGS_] [smallint] NULL
) ON [PRIMARY]
GO
INSERT INTO TRENDDATA VALUES (1,11,'2003-06-10 14:00:00',2,'20.00',0)--FIRST VALUE
INSERT INTO TRENDDATA VALUES (2,11,'2003-06-10 16:00:00',2,'35.484997',0)
INSERT INTO TRENDDATA VALUES (3,11,'2003-06-10 18:00:00',2,'65.895',0)
INSERT INTO TRENDDATA VALUES (4,11,'2003-06-10 20:00:00',2,'65.895',0)
INSERT INTO TRENDDATA VALUES (5,11,'2003-06-10 22:00:00',2,'65.895',0)
INSERT INTO TRENDDATA VALUES (6,11,'2003-06-11 00:00:00',2,'65.895',0)
INSERT INTO TRENDDATA VALUES (7,11,'2003-06-11 02:00:00',2,'67.234',0)
INSERT INTO TRENDDATA VALUES (8,11,'2003-06-11 04:00:00',2,'67.235',0)
INSERT INTO TRENDDATA VALUES (9,11,'2003-06-11 06:00:000',2,'67.236',0)
INSERT INTO TRENDDATA VALUES (10,11,'2003-06-11 08:00:00',2,'68.234',0)
INSERT INTO TRENDDATA VALUES (11,11,'2003-06-11 10:00:00',2,'68.234',0)
INSERT INTO TRENDDATA VALUES (12,11,'2003-06-11 12:00:00',2,'68.234',0)
INSERT INTO TRENDDATA VALUES (13,11,'2003-06-11 14:00:00',2,'68.234',0)--HERE
INSERT INTO TRENDDATA VALUES (15,11,'2003-06-11 16:00:00',2,'1.7',0)
INSERT INTO TRENDDATA VALUES (16,11,'2003-06-11 18:00:00',2,'1.8',0)
INSERT INTO TRENDDATA VALUES (17,11,'2003-06-11 20:00:00',2,'1.9',0)
INSERT INTO TRENDDATA VALUES (18,11,'2003-06-11 22:00:00',2,'2.0',0)
INSERT INTO TRENDDATA VALUES (19,11,'2003-06-12 00:00:00',2,'3',0)
INSERT INTO TRENDDATA VALUES (20,11,'2003-06-12 02:00:00',2,'5.57',0)
INSERT INTO TRENDDATA VALUES (21,11,'2003-06-12 04:00:00',2,'8',0)
INSERT INTO TRENDDATA VALUES (22,11,'2003-06-12 06:00:00',2,'22.45',0)
INSERT INTO TRENDDATA VALUES (23,11,'2003-06-12 08:00:00',2,'65.895',0)
INSERT INTO TRENDDATA VALUES (24,11,'2003-06-12 10:00:00',2,'65.895',0)
INSERT INTO TRENDDATA VALUES (25,11,'2003-06-12 12:00:00',2,'65.895',0)
INSERT INTO TRENDDATA VALUES (26,11,'2003-06-12 14:00:00',2,'75.41665',0)
INSERT INTO TRENDDATA VALUES (27,11,'2003-06-12 16:00:00',2,'75.41665',0)
INSERT INTO TRENDDATA VALUES (28,11,'2003-06-12 18:00:00',2,'75.41665',0)
INSERT INTO TRENDDATA VALUES (29,11,'2003-06-12 20:00:00',2,'75.41665',0)--HERE
INSERT INTO TRENDDATA VALUES (30,11,'2003-06-12 22:00:00',2,'3.2',0)
INSERT INTO TRENDDATA VALUES (31,11,'2003-06-13 00:00:00',2,'7.8',0)
INSERT INTO TRENDDATA VALUES (33,11,'2003-06-13 02:00:00',2,'22.6',0)
INSERT INTO TRENDDATA VALUES (35,11,'2003-06-13 04:00:00',2,'35.89',0)
INSERT INTO TRENDDATA VALUES (36,11,'2003-06-13 06:00:00',2,'42.99',0)
INSERT INTO TRENDDATA VALUES (37,11,'2003-06-13 08:00:00',2,'75.41665',0)
INSERT INTO TRENDDATA VALUES (38,11,'2003-06-13 10:00:00',2,'75.41665',0)
INSERT INTO TRENDDATA VALUES (39,11,'2003-06-13 12:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (40,11,'2003-06-13 14:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (41,11,'2003-06-13 16:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (42,11,'2003-06-13 18:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (43,11,'2003-06-13 20:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (44,11,'2003-06-13 22:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (45,11,'2003-06-14 00:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (46,11,'2003-06-14 02:00:00',2,'87.85751',0)--HERE
INSERT INTO TRENDDATA VALUES (47,11,'2003-06-14 04:00:00',2,'2.24',0)
INSERT INTO TRENDDATA VALUES (48,11,'2003-06-14 06:00:00',2,'3.5',0)
INSERT INTO TRENDDATA VALUES (49,11,'2003-06-14 08:00:00',2,'6.9',0)
INSERT INTO TRENDDATA VALUES (50,11,'2003-06-14 10:00:00',2,'22.5',0)
INSERT INTO TRENDDATA VALUES (51,11,'2003-06-14 12:00:00',2,'35.7',0)
INSERT INTO TRENDDATA VALUES (52,11,'2003-06-14 14:00:00',2,'45.98',0)
INSERT INTO TRENDDATA VALUES (53,11,'2003-06-14 16:00:00',2,'65.3',0)
INSERT INTO TRENDDATA VALUES (54,11,'2003-06-14 18:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (55,11,'2003-06-14 20:00:00',2,'87.85751',0)
INSERT INTO TRENDDATA VALUES (56,11,'2003-06-14 22:00:00',2,'87.85751',0)--HERE
-
Try this sp
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_TrendPeakTotalModified]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_TrendPeakTotalModified]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_TrendPeakTotalModified
-- declare the input parameters
@Tid INT,
@StartDate datetime,
@EndDate datetime,
-- declare the output parameters
@Total decimal(19,4) 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
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
-
Usage for the above sp
HTH Mike
Declare @TotalUsed varchar(50),
@s-2 decimal(19,4)
Create Table #Test
(
PeakValue Decimal(19,4),
FirstValue Decimal(19,4)
)
Execute @totalUsed = sp_TrendPeakTotalModified 11,'2003/5/1','2003/7/1',@S
Drop Table #Test
/*
SHOULD BE 299.36567
Returns 299.3657
*/
-
Thanks Michael,
I'll give this a try Monday!
-
Just one thing. Never start a stored procedure name with sp_
Very much recomended against due to the way SQL resolves names when executing a stored procedure.
sp_ is the prefix that MS gives to system stored procedures.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply