Output Parameters for a newbie
-
Thanks ... here is the table script: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
TRENDDATA holds the raw trends of all points in the system. A particular point (such as the "Chilled water usage" for example) has a unique TID_ of lets say '209'.
In our system a control module has an integrator which counts up the tons of Chilled Water leaving the plant. This is like your electric meter in your home. Problem is that the control module takes a dump once a year or so due to power bumps and water flooding.
So I will set the module's counter to trend into the SQL DB and the trend will start from zero and count up as Chilled water is used. Then, when the module fails, the trend in the SQL DB will start off at zero (or more likely a few notches above zero) and start back up from there.
If the SP_ is given a @StartDate of 'Jan 01' midnight, to @EndDate of 'Jan 31' midnight ... to see what that period's total accumulated usage is, we need to look at each value from start to finnish and watch it climb until it falls. Then capture that peak to a sum. Next look for the next peak and add to the accumulated sum @Temp_Sum. Finally when we get to the end we just add the last value to the @Temp_Sum and return it.
Lets say we start at 100 Jan 1st. We would need to subtract that from the total sum because that usage was accumulated before our start date. So we save 100 to subtract from the final value. Then on Jan 2nd we are at 110 and Jan 3rd we are at 120. On Jan 4th our control moduel died at precisly 12 noon and we had 122 total so we save that to @Temp_Sum. Since we replaced it, the total inside the module starts at 0. We keep looking for a new peak and lets say that the next one occurs at Jan 31st at 11:30 AM because the module got water on it and fried. At Jan 31st 11:30 AM we had 50 in the modules trend and Jan 31st 11:40 we have 1.7 so we know we have a new peak. We add this 2nd peak to @Temp_Sum which is now 172.
On Jan 31st Midnight we are up to 2.5, so to clean up, we take @Temp_Sum and subtract 100 (our start value) plus we add the 2.5 to get the true usage for the exact period between Jan 1st and Jan 31st Midnight to Midnight which is 74.5.
The value should never be negative which makes things a little easier.
DATA_VALUE_ is saved in the table as VARCHAR(50) so CONVERTS are needed throughout to decimal to do the compares.
-
I'm not sure how to get and post raw data.
How would I do this ?
-
do a select top 20 in query analyser, copy and paste the results.
It could be even better if you could paste the insert scripts instead. You can create them like so :
Select top 25 'Insert into dbo.MyTable (Col1, col2) values (' + col1 + ', ' + col2 + ')' from dbo.YourTable where month = 'january'
You'll have to cast everything to varchar() so that the concatenation works.
-
Got it but there are no failures:
Also the RECORD_TYPE_ must = 2 for all queries. I can add that in.
53740 209 6/1/2005 2 65.7 0 209 6/1/2005
53741 209 6/1/2005 12:10:00 AM 2 65.3 0 209 6/1/2005 12:10:00 AM
53742 209 6/1/2005 12:20:00 AM 2 65.0 0 209 6/1/2005 12:20:00 AM
53743 209 6/1/2005 12:30:00 AM 2 64.6 0 209 6/1/2005 12:30:00 AM
53744 209 6/1/2005 12:33:34 AM 9 32.0 0 209 6/1/2005 12:33:34 AM
53745 209 6/1/2005 12:40:00 AM 2 64.4 0 209 6/1/2005 12:40:00 AM
53746 209 6/1/2005 12:50:00 AM 2 64.2 0 209 6/1/2005 12:50:00 AM
53747 209 6/1/2005 1:00:00 AM 2 63.9 0 209 6/1/2005 1:00:00 AM
53748 209 6/1/2005 1:10:00 AM 2 63.6 0 209 6/1/2005 1:10:00 AM
53749 209 6/1/2005 1:20:00 AM 2 63.5 0 209 6/1/2005 1:20:00 AM
53750 209 6/1/2005 1:30:00 AM 2 63.2 0 209 6/1/2005 1:30:00 AM
53751 209 6/1/2005 1:40:00 AM 2 63.2 0 209 6/1/2005 1:40:00 AM
53752 209 6/1/2005 1:50:00 AM 2 62.9 0 209 6/1/2005 1:50:00 AM
53753 209 6/1/2005 2:00:00 AM 2 62.7 0 209 6/1/2005 2:00:00 AM
53754 209 6/1/2005 2:10:00 AM 2 62.6 0 209 6/1/2005 2:10:00 AM
53755 209 6/1/2005 2:20:00 AM 2 62.3 0 209 6/1/2005 2:20:00 AM
53756 209 6/1/2005 2:30:00 AM 2 62.0 0 209 6/1/2005 2:30:00 AM
53757 209 6/1/2005 2:40:00 AM 2 62.0 0 209 6/1/2005 2:40:00 AM
53758 209 6/1/2005 2:50:00 AM 2 61.7 0 209 6/1/2005 2:50:00 AM
53759 209 6/1/2005 3:00:00 AM 2 61.4 0 209 6/1/2005 3:00:00 AM
53760 209 6/1/2005 3:00:02 AM 9 -39.0 0 209 6/1/2005 3:00:02 AM
53761 209 6/1/2005 3:10:00 AM 2 61.1 0 209 6/1/2005 3:10:00 AM
53762 209 6/1/2005 3:20:00 AM 2 60.9 0 209 6/1/2005 3:20:00 AM
53763 209 6/1/2005 3:30:00 AM 2 60.6 0 209 6/1/2005 3:30:00 AM
53764 209 6/1/2005 3:40:00 AM 2 60.4 0 209 6/1/2005 3:40:00 AM
53765 209 6/1/2005 3:50:00 AM 2 60.1 0 209 6/1/2005 3:50:00 AM
53766 209 6/1/2005 4:00:00 AM 2 59.8 0 209 6/1/2005 4:00:00 AM
53767 209 6/1/2005 4:10:00 AM 2 59.7 0 209 6/1/2005 4:10:00 AM
53768 209 6/1/2005 4:20:00 AM 2 59.8 0 209 6/1/2005 4:20:00 AM
53769 209 6/1/2005 4:30:00 AM 2 59.6 0 209 6/1/2005 4:30:00 AM
53770 209 6/1/2005 4:40:00 AM 2 59.4 0 209 6/1/2005 4:40:00 AM
53771 209 6/1/2005 4:50:00 AM 2 59.2 0 209 6/1/2005 4:50:00 AM
53772 209 6/1/2005 5:00:00 AM 2 59.0 0 209 6/1/2005 5:00:00 AM
53773 209 6/1/2005 5:10:00 AM 2 59.0 0 209 6/1/2005 5:10:00 AM
53774 209 6/1/2005 5:20:00 AM 2 58.6 0 209 6/1/2005 5:20:00 AM
53775 209 6/1/2005 5:30:00 AM 2 58.4 0 209 6/1/2005 5:30:00 AM
53776 209 6/1/2005 5:40:00 AM 2 58.5 0 209 6/1/2005 5:40:00 AM
53777 209 6/1/2005 5:50:00 AM 2 58.5 0 209 6/1/2005 5:50:00 AM
53778 209 6/1/2005 6:00:00 AM 2 58.5 0 209 6/1/2005 6:00:00 AM
53779 209 6/1/2005 6:10:00 AM 2 59.1 0 209 6/1/2005 6:10:00 AM
53780 209 6/1/2005 6:20:00 AM 2 61.4 0 209 6/1/2005 6:20:00 AM
53781 209 6/1/2005 6:30:00 AM 2 64.0 0 209 6/1/2005 6:30:00 AM
53782 209 6/1/2005 6:40:00 AM 2 66.1 0 209 6/1/2005 6:40:00 AM
53783 209 6/1/2005 6:50:00 AM 2 68.3 0 209 6/1/2005 6:50:00 AM
53784 209 6/1/2005 7:00:00 AM 2 70.1 0 209 6/1/2005 7:00:00 AM
53785 209 6/1/2005 7:10:00 AM 2 71.6 0 209 6/1/2005 7:10:00 AM
53786 209 6/1/2005 7:20:00 AM 2 73.4 0 209 6/1/2005 7:20:00 AM
53787 209 6/1/2005 7:30:00 AM 2 74.5 0 209 6/1/2005 7:30:00 AM
53788 209 6/1/2005 7:40:00 AM 2 74.1 0 209 6/1/2005 7:40:00 AM
53789 209 6/1/2005 7:50:00 AM 2 75.0 0 209 6/1/2005 7:50:00 AM
53790 209 6/1/2005 8:00:00 AM 2 75.8 0 209 6/1/2005 8:00:00 AM
53791 209 6/1/2005 8:10:00 AM 2 77.2 0 209 6/1/2005 8:10:00 AM
53792 209 6/1/2005 8:20:00 AM 2 78.2 0 209 6/1/2005 8:20:00 AM
53793 209 6/1/2005 8:30:00 AM 2 78.2 0 209 6/1/2005 8:30:00 AM
53794 209 6/1/2005 8:40:00 AM 2 78.0 0 209 6/1/2005 8:40:00 AM
53795 209 6/1/2005 8:50:00 AM 2 78.5 0 209 6/1/2005 8:50:00 AM
53796 209 6/1/2005 9:00:00 AM 2 78.8 0 209 6/1/2005 9:00:00 AM
53797 209 6/1/2005 9:10:00 AM 2 80.3 0 209 6/1/2005 9:10:00 AM
53798 209 6/1/2005 9:20:00 AM 2 82.1 0 209 6/1/2005 9:20:00 AM
53799 209 6/1/2005 9:30:00 AM 2 81.0 0 209 6/1/2005 9:30:00 AM
53800 209 6/1/2005 9:40:00 AM 2 78.8 0 209 6/1/2005 9:40:00 AM
53801 209 6/1/2005 9:50:00 AM 2 77.4 0 209 6/1/2005 9:50:00 AM
53802 209 6/1/2005 10:00:00 AM 2 77.6 0 209 6/1/2005 10:00:00 AM
53803 209 6/1/2005 10:10:00 AM 2 78.3 0 209 6/1/2005 10:10:00 AM
53804 209 6/1/2005 10:20:00 AM 2 79.3 0 209 6/1/2005 10:20:00 AM
53805 209 6/1/2005 10:30:00 AM 2 79.7 0 209 6/1/2005 10:30:00 AM
53806 209 6/1/2005 10:40:00 AM 2 80.1 0 209 6/1/2005 10:40:00 AM
53807 209 6/1/2005 10:50:00 AM 2 80.8 0 209 6/1/2005 10:50:00 AM
53808 209 6/1/2005 11:00:00 AM 2 81.0 0 209 6/1/2005 11:00:00 AM
53809 209 6/1/2005 11:10:00 AM 2 81.3 0 209 6/1/2005 11:10:00 AM
-
Actually this wont wirk because it is a tempurature which dances around.
I will need to set up one of these and trend it for a day or two. Or better yet I can fugde it like this:
53740 209 6/1/2005 2 65.7 0 209 6/1/2005
53741 209 6/1/2005 12:10:00 AM 2 65.8 0 209 6/1/2005 12:10:00 AM
53742 209 6/1/2005 12:20:00 AM 2 65.9 0 209 6/1/2005 12:20:00 AM
53743 209 6/1/2005 12:30:00 AM 2 66.0 0 209 6/1/2005 12:30:00 AM
53744 209 6/1/2005 12:33:34 AM 9 66.1 0 209 6/1/2005 12:33:34 AM
53745 209 6/1/2005 12:40:00 AM 2 66.1 0 209 6/1/2005 12:40:00 AM
53746 209 6/1/2005 12:50:00 AM 2 66.1 0 209 6/1/2005 12:50:00 AM
53747 209 6/1/2005 1:00:00 AM 2 66.2 0 209 6/1/2005 1:00:00 AM
53748 209 6/1/2005 1:10:00 AM 2 66.6 0 209 6/1/2005 1:10:00 AM
53749 209 6/1/2005 1:20:00 AM 2 66.7 0 209 6/1/2005 1:20:00 AM
53750 209 6/1/2005 1:30:00 AM 2 66.9 0 209 6/1/2005 1:30:00 AM
53751 209 6/1/2005 1:40:00 AM 2 7.2 0 209 6/1/2005 1:40:00 AM
53752 209 6/1/2005 1:50:00 AM 2 7.9 0 209 6/1/2005 1:50:00 AM
53753 209 6/1/2005 2:00:00 AM 2 7.9 0 209 6/1/2005 2:00:00 AM
53754 209 6/1/2005 2:10:00 AM 2 8.6 0 209 6/1/2005 2:10:00 AM
53755 209 6/1/2005 2:20:00 AM 2 8.7 0 209 6/1/2005 2:20:00 AM
53756 209 6/1/2005 2:30:00 AM 2 8.9 0 209 6/1/2005 2:30:00 AM
53757 209 6/1/2005 2:40:00 AM 2 9.0 0 209 6/1/2005 2:40:00 AM
53758 209 6/1/2005 2:50:00 AM 2 9.7 0 209 6/1/2005 2:50:00 AM
53759 209 6/1/2005 3:00:00 AM 2 9.9 0 209 6/1/2005 3:00:00 AM
This block has one failure.
-
You got to help me out here... can you post the INSERT SCRIPT for all those lines... don't really feel like typing all that to help you.
I think this will help you to help me. Run this query in query analyser, paste the results in query analyser and check that the script doesn't have a syntaxe error (try to fix 'em if any).
If it doesn't have an error, paste that script back here.
Select top 100
'INSERT INTO dbo.TRENDDATA (SEQUENCENUMBER_, TID_, DATE_STAMP_, RECORD_TYPE_, DATA_VALUE_, STATUS_FLAGS_) VALUES(' + CAST(SEQUENCENUMBER_ as varchar(20)) + ', ' + CAST(TID as varchar(20)) + ', ' + ISNULL('''' + CAST(DATE_STAMP_ as varchar(25)) + '''', 'NULL') + ', ' + ISNULL(CAST(RECORD_TYPE_ as varchar(10)), 'NULL') + ', ' + ISNULL(CAST(DATA_VALUE_ as varchar(20)), 'NULL') + ', ' + ISNULL(CAST(STATUS_FLAGS_ AS varchar(10)), 'NULL') + ')' as InsertQry
from dbo.TRENDDATA
where month = 'january'--...
-
I'll work on that this evening.
Thanks.
-
Here is a text file I generated from DTS which you should be able to import back in using same?
"SEQUENCENUMBER_","TID_","DATE_STAMP_","RECORD_TYPE_","DATA_VALUE_","STATUS_FLAGS_","Expr1","Expr2","Expr3"
1,11,2003-06-10 14:00:00,2,"20.00",0,11,2003-06-10 14:00:00,2
2,11,2003-06-10 16:00:00,2,"35.484997",0,11,2003-06-10 16:00:00,2
3,11,2003-06-10 18:00:00,2,"65.895",0,11,2003-06-10 18:00:00,2
4,11,2003-06-10 20:00:00,2,"65.895",0,11,2003-06-10 20:00:00,2
5,11,2003-06-10 22:00:00,2,"65.895",0,11,2003-06-10 22:00:00,2
6,11,2003-06-11 00:00:00,2,"65.895",0,11,2003-06-11 00:00:00,2
7,11,2003-06-11 02:00:00,2,"67.234",0,11,2003-06-11 02:00:00,2
8,11,2003-06-11 04:00:00,2,"67.235",0,11,2003-06-11 04:00:00,2
9,11,2003-06-11 06:00:00,2,"67.236",0,11,2003-06-11 06:00:00,2
10,11,2003-06-11 08:00:00,2,"68.234",0,11,2003-06-11 08:00:00,2
11,11,2003-06-11 10:00:00,2,"68.234",0,11,2003-06-11 10:00:00,2
12,11,2003-06-11 12:00:00,2,"68.234",0,11,2003-06-11 12:00:00,2
13,11,2003-06-11 14:00:00,2,"68.234",0,11,2003-06-11 14:00:00,2
15,11,2003-06-11 16:00:00,2,"1.7",0,11,2003-06-11 16:00:00,2
16,11,2003-06-11 18:00:00,2,"1.8",0,11,2003-06-11 18:00:00,2
17,11,2003-06-11 20:00:00,2,"1.9",0,11,2003-06-11 20:00:00,2
18,11,2003-06-11 22:00:00,2,"2.0",0,11,2003-06-11 22:00:00,2
19,11,2003-06-12 00:00:00,2,"3",0,11,2003-06-12 00:00:00,2
20,11,2003-06-12 02:00:00,2,"5.57",0,11,2003-06-12 02:00:00,2
21,11,2003-06-12 04:00:00,2,"8",0,11,2003-06-12 04:00:00,2
22,11,2003-06-12 06:00:00,2,"22.45",0,11,2003-06-12 06:00:00,2
23,11,2003-06-12 08:00:00,2,"65.895",0,11,2003-06-12 08:00:00,2
24,11,2003-06-12 10:00:00,2,"65.895",0,11,2003-06-12 10:00:00,2
25,11,2003-06-12 12:00:00,2,"65.895",0,11,2003-06-12 12:00:00,2
26,11,2003-06-12 14:00:00,2,"75.41665",0,11,2003-06-12 14:00:00,2
27,11,2003-06-12 16:00:00,2,"75.41665",0,11,2003-06-12 16:00:00,2
28,11,2003-06-12 18:00:00,2,"75.41665",0,11,2003-06-12 18:00:00,2
29,11,2003-06-12 20:00:00,2,"75.41665",0,11,2003-06-12 20:00:00,2
30,11,2003-06-12 22:00:00,2,"3.2",0,11,2003-06-12 22:00:00,2
31,11,2003-06-13 00:00:00,2,"7.8",0,11,2003-06-13 00:00:00,2
33,11,2003-06-13 02:00:00,2,"22.6",0,11,2003-06-13 02:00:00,2
35,11,2003-06-13 04:00:00,2,"35.89",0,11,2003-06-13 04:00:00,2
36,11,2003-06-13 06:00:00,2,"42.99",0,11,2003-06-13 06:00:00,2
37,11,2003-06-13 08:00:00,2,"75.41665",0,11,2003-06-13 08:00:00,2
38,11,2003-06-13 10:00:00,2,"75.41665",0,11,2003-06-13 10:00:00,2
39,11,2003-06-13 12:00:00,2,"87.85751",0,11,2003-06-13 12:00:00,2
40,11,2003-06-13 14:00:00,2,"87.85751",0,11,2003-06-13 14:00:00,2
41,11,2003-06-13 16:00:00,2,"87.85751",0,11,2003-06-13 16:00:00,2
42,11,2003-06-13 18:00:00,2,"87.85751",0,11,2003-06-13 18:00:00,2
43,11,2003-06-13 20:00:00,2,"87.85751",0,11,2003-06-13 20:00:00,2
44,11,2003-06-13 22:00:00,2,"87.85751",0,11,2003-06-13 22:00:00,2
45,11,2003-06-14 00:00:00,2,"87.85751",0,11,2003-06-14 00:00:00,2
46,11,2003-06-14 02:00:00,2,"87.85751",0,11,2003-06-14 02:00:00,2
47,11,2003-06-14 04:00:00,2,"2.24",0,11,2003-06-14 04:00:00,2
48,11,2003-06-14 06:00:00,2,"3.5",0,11,2003-06-14 06:00:00,2
49,11,2003-06-14 08:00:00,2,"6.9",0,11,2003-06-14 08:00:00,2
50,11,2003-06-14 10:00:00,2,"22.5",0,11,2003-06-14 10:00:00,2
51,11,2003-06-14 12:00:00,2,"35.7",0,11,2003-06-14 12:00:00,2
52,11,2003-06-14 14:00:00,2,"45.98",0,11,2003-06-14 14:00:00,2
53,11,2003-06-14 16:00:00,2,"65.3",0,11,2003-06-14 16:00:00,2
54,11,2003-06-14 18:00:00,2,"87.85751",0,11,2003-06-14 18:00:00,2
55,11,2003-06-14 20:00:00,2,"87.85751",0,11,2003-06-14 20:00:00,2
56,11,2003-06-14 22:00:00,2,"87.85751",0,11,2003-06-14 22:00:00,2
-
Also here is the script to make the table. Hope this works.
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
-
Well, this caught my intrest, so I thought I'd give it a quick try. Sorry Remi. );
This appears to work, if I've understood your requirements properly.
CREATE PROCEDURE SumOfMaximum (@Tid INT, @MaximumValue FLOAT OUTPUT)
AS
DECLARE @SumOfMaximums FLOAT,
@DataValue FLOAT,
@PreviousDataValue FLOAT,
@SeqNoINT
-- TID_,DATE_STAMP,DATA_VALUE
SET @SumOfMaximums = 0
SET @PreviousDataValue = -1
DECLARE curTrend CURSOR FOR
SELECT DATA_VALUE_
FROM TrendData
WHERE TID_=@Tid-- Do one at a time. Easier code, faster too.
ORDER BY DATE_STAMP_ ASC
OPEN curTrend
FETCH NEXT FROM curTrend INTO @DataValue
WHILE @@FETCH_STATUS=0
BEGIN
IF (@DataValue<@PreviousDataValue) -- we've just gone over a reset
SET @SumOfMaximums = @SumOfMaximums + @PreviousDataValue
SET @PreviousDataValue = @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 + @PreviousDataValue
CLOSE curTrend
DEALLOCATE curTrend
SET @MaximumValue = @SumOfMaximums
GO
-- AND to run it
DECLARE @Value FLOAT
EXEC SumOfMaximum 11, @Value OUTPUT
PRINT @Value
GO
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 -
If anyone can do this without a cursor, I'd be very interested in seeing it.
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 -
Output parameters are also input parameters. If you do not want to supply a value simply define the output parameters as such.
Your old code:
-- declare the output parameters
@final_peak decimal(19,4) OUTPUT,
@final_date datetime OUTPUT
New (add default value):
-- declare the output parameters
@final_peak decimal(19,4) = null OUTPUT,
@final_date datetime = null OUTPUT
You will no longer receiver the "expects parameter x which was not supplied"
-
If you hit : edit post, then you can delete the duplicate post.
-
thanks, i removed the dups
-
Still trying to figure a way to generate a table script with 30 rows of data for one point.
Viewing 15 posts - 16 through 30 (of 39 total)
You must be logged in to reply to this topic. Login to reply