Max Min Agg func blues ...
-
The problem is definetly the converts I added.
Don't know what I did wrong.
-
This works for me :
If you need to have one proc for the min, and one for the max, I'll resplit them for you.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TrendMax_AbsoluteMinMax]') and XType = 'P')
drop PROCEDURE [dbo].[TrendMax_AbsoluteMinMax]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TESTDATA]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TESTDATA]
GO
CREATE TABLE [dbo].[TESTDATA] (
[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].[TESTDATA] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[TID_],
[SEQUENCENUMBER_]
) ON [PRIMARY]
GO
INSERT INTO TESTDATA VALUES (1,11,'2003-06-10 14:00:00',2,'20.00',0);
INSERT INTO TESTDATA VALUES (2,11,'2003-06-10 16:00:00',2,'35.484997',0);
INSERT INTO TESTDATA VALUES (3,11,'2003-06-10 18:00:00',2,'65.895',0);
INSERT INTO TESTDATA VALUES (4,11,'2003-06-10 20:00:00',2,'65.895',0);
INSERT INTO TESTDATA VALUES (5,11,'2003-06-10 22:00:00',2,'65.895',0);
INSERT INTO TESTDATA VALUES (6,11,'2003-06-11 00:00:00',2,'65.895',0);
INSERT INTO TESTDATA VALUES (7,11,'2003-06-11 02:00:00',2,'67.234',0);
INSERT INTO TESTDATA VALUES (8,11,'2003-06-11 04:00:00',2,'67.235',0);
INSERT INTO TESTDATA VALUES (9,11,'2003-06-11 06:00:00',2,'67.236',0);
INSERT INTO TESTDATA VALUES (10,11,'2003-06-11 08:00:00',2,'68.234',0);
INSERT INTO TESTDATA VALUES (11,11,'2003-06-11 10:00:00',2,'68.234',0);
INSERT INTO TESTDATA VALUES (12,11,'2003-06-11 12:00:00',2,'68.234',0);
INSERT INTO TESTDATA VALUES (13,11,'2003-06-11 14:00:00',2,'68.234',0);
INSERT INTO TESTDATA VALUES (15,11,'2003-06-11 16:00:00',2,'1.7',0);
INSERT INTO TESTDATA VALUES (16,11,'2003-06-11 18:00:00',2,'1.8',0);
INSERT INTO TESTDATA VALUES (17,11,'2003-06-11 20:00:00',2,'1.9',0);
INSERT INTO TESTDATA VALUES (18,11,'2003-06-11 22:00:00',2,'2.0',0);
INSERT INTO TESTDATA VALUES (19,11,'2003-06-12 00:00:00',2,'3',0);
INSERT INTO TESTDATA VALUES (20,11,'2003-06-12 02:00:00',2,'5.57',0);
INSERT INTO TESTDATA VALUES (21,11,'2003-06-12 04:00:00',2,'8',0);
INSERT INTO TESTDATA VALUES (22,11,'2003-06-12 06:00:00',2,'22.45',0);
INSERT INTO TESTDATA VALUES (23,11,'2003-06-12 08:00:00',2,'65.895',0);
INSERT INTO TESTDATA VALUES (24,11,'2003-06-12 10:00:00',2,'65.895',0);
INSERT INTO TESTDATA VALUES (25,11,'2003-06-12 12:00:00',2,'65.895',0);
INSERT INTO TESTDATA VALUES (26,11,'2003-06-12 14:00:00',2,'75.41665',0);
INSERT INTO TESTDATA VALUES (27,11,'2003-06-12 16:00:00',2,'75.41665',0);
INSERT INTO TESTDATA VALUES (28,11,'2003-06-12 18:00:00',2,'75.41665',0);
INSERT INTO TESTDATA VALUES (29,11,'2003-06-12 20:00:00',2,'75.41665',0);
INSERT INTO TESTDATA VALUES (30,11,'2003-06-12 22:00:00',2,'3.2',0);
INSERT INTO TESTDATA VALUES (31,11,'2003-06-13 00:00:00',2,'7.8',0);
INSERT INTO TESTDATA VALUES (33,11,'2003-06-13 02:00:00',2,'22.6',0);
INSERT INTO TESTDATA VALUES (35,11,'2003-06-13 04:00:00',2,'35.89',0);
INSERT INTO TESTDATA VALUES (36,11,'2003-06-13 06:00:00',2,'42.99',0);
INSERT INTO TESTDATA VALUES (37,11,'2003-06-13 08:00:00',2,'75.41665',0);
INSERT INTO TESTDATA VALUES (38,11,'2003-06-13 10:00:00',2,'75.41665',0);
INSERT INTO TESTDATA VALUES (39,11,'2003-06-13 12:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (40,11,'2003-06-13 14:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (41,11,'2003-06-13 16:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (42,11,'2003-06-13 18:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (43,11,'2003-06-13 20:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (44,11,'2003-06-13 22:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (45,11,'2003-06-14 00:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (46,11,'2003-06-14 02:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (47,11,'2003-06-14 04:00:00',2,'2.24',0);
INSERT INTO TESTDATA VALUES (48,11,'2003-06-14 06:00:00',2,'3.5',0);
INSERT INTO TESTDATA VALUES (49,11,'2003-06-14 08:00:00',2,'6.9',0);
INSERT INTO TESTDATA VALUES (50,11,'2003-06-14 10:00:00',2,'22.5',0);
INSERT INTO TESTDATA VALUES (51,11,'2003-06-14 12:00:00',2,'35.7',0);
INSERT INTO TESTDATA VALUES (52,11,'2003-06-14 14:00:00',2,'45.98',0);
INSERT INTO TESTDATA VALUES (53,11,'2003-06-14 16:00:00',2,'65.3',0);
INSERT INTO TESTDATA VALUES (54,11,'2003-06-14 18:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (55,11,'2003-06-14 20:00:00',2,'87.85751',0);
INSERT INTO TESTDATA VALUES (56,11,'2003-06-14 22:00:00',2,'87.85751',0);
GO
CREATE PROCEDURE dbo.TrendMax_AbsoluteMinMax
@TID int,
@StartDate datetime,
@EndDate datetime
AS
SELECT max(CONVERT(decimal(19,4), DATA_VALUE_ )) as Maximum, min(CONVERT(decimal(19,4), DATA_VALUE_ )) as Minimum
FROM dbo.TESTDATA
WHERE TID_ = @TID AND @StartDate = DATE_STAMP_ AND RECORD_TYPE_ = 2
GO
EXEC dbo.TrendMax_AbsoluteMinMax 11, '2003-06-11', '2003-06-13'
/*
Maximum Minimum
--------------------- ---------------------
75.4167 1.7000
*/
GO
DROP PROCEDURE TrendMax_AbsoluteMinMax
GO
DROP TABLE TESTDATA
-
DATA_VALUE_ is a column of type VARCHAR(50)
and I want to convert it to decimal(19,4)
CONVERT(decimal(19,4), DATA_VALUE_ )
should do it right?
-
You'd be better off with (18,6) because you got up to 6 decimals in that data sample.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply