Max Min Agg func blues ...
-
Can anyone tell me why this works fine and gives me the max but when I copy it in a new sp and change max to min it does not give the minimum but still gives the maximum?
--K. Mike Bradley
CREATE PROCEDURE dbo.TrendMax
@TID int,
@StartDate datetime,
@EndDate datetime
AS
SELECT top 1 DATE_STAMP_ "Date_Time", max(CONVERT(decimal(19,4), DATA_VALUE_ )) "Value"
FROM TRENDDATA
WHERE TID_ = @TID AND @StartDate <= DATE_STAMP_ AND @EndDate >= DATE_STAMP_ AND RECORD_TYPE_ = 2
group by DATE_STAMP_
order by max(CONVERT(decimal(19,4), DATA_VALUE_ )) desc
GO
-
This doesn't work??
CREATE PROCEDURE dbo.TrendMax
@TID int,
@StartDate datetime,
@EndDate datetime
AS
SELECT top 1 DATE_STAMP_ "Date_Time", min(CONVERT(decimal(19,4), DATA_VALUE_ )) "Value"
FROM TRENDDATA
WHERE TID_ = @TID AND @StartDate = DATE_STAMP_ AND RECORD_TYPE_ = 2
group by DATE_STAMP_
order by min(CONVERT(decimal(19,4), DATA_VALUE_ ))
GO
-
There can be
Only one row for the criteria or
somtimes it happens to me. Make the changes but forget to excute the changes in the data base.
Query looks fine and should give max or min as per the changes made to the query.
Regards,
gova -
What is this
order by max(CONVERT(decimal(19,4), DATA_VALUE_ )) desc
supposed to do along with a TOP 1 ?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Edit: Extremely late.. Remi already posted the same.
-
Hehe... too many guys with too much time on their hands.
Hey Mike, what's this query supposed to return?? The min amount per date, or just the min amount?
-
Remi,
This is the one you wrote for me (btw I added your name in comment on the server just not on my local copy which is old).
with max it returns the max value but I also need a min and if I change the max to min it still returns the max.
101.7 degress outside air temp on point 209 at a certian date of course.
-
I don't remember writting it that way, bot oh well :
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.TRENDDATA
WHERE TID_ = @TID AND @StartDate = DATE_STAMP_ AND RECORD_TYPE_ = 2
CREATE PROCEDURE dbo.TrendMaxMin_PerDate
@TID int,
@StartDate datetime,
@EndDate datetime
AS
SELECT DATE_STAMP_ as Date_Time, max(CONVERT(decimal(19,4), DATA_VALUE_ )) as Maximum, min(CONVERT(decimal(19,4), DATA_VALUE_ )) as Minimum
FROM dbo.TRENDDATA
WHERE TID_ = @TID AND @StartDate = DATE_STAMP_ AND RECORD_TYPE_ = 2
group by DATE_STAMP_
GO
-
Thanks but I need two separate sp's
and I dont see why when I do this is still returns the max?
--K. Mike Bradley
--with help from:
--Remi Gregoire @ http://www.SQLServerCentral.com
CREATE PROCEDURE dbo.TrendMin
@TID int,
@StartDate datetime,
@EndDate datetime
AS
SELECT top 1 DATE_STAMP_ "Date_Time", min(CONVERT(decimal(19,4), DATA_VALUE_ )) "Value"
FROM TRENDDATA
WHERE TID_ = @TID AND @StartDate <= DATE_STAMP_ AND @EndDate >= DATE_STAMP_ AND RECORD_TYPE_ = 2
group by DATE_STAMP_
order by min(CONVERT(decimal(19,4), DATA_VALUE_ )) desc
GO
-
I posted 2 different sps because I didn't know which one you really needed.
Have you tried running them?
Have you considered hiring a real programmer to do this task? I'm not trying to hurt you but you're way over your head in this project. As far as I understood you task, that report should have been done in less than 20 minutes by a professional and you already seem to have lost a few days on it... Just my 2 cents.
-
LOL!
well if you remember Remi I offered you money to your pay pall account to make it work.
You said you where to busy (I can relate to that).
I need four stored procedures to use in crystal reports.
1st that gives a range of trend data for a plot printout (which I got)
2nd that gives the max
3rd that gives the min
and 4th that gives the sum of all peaks which you started to help me on Remi and Michael Du Bois took up after you.
It seems as if the convert is not working after all. The max does not really return the max. I just tested it with another point (#210) which had 10.0 on a certain date but the max returned 6.2
Keep in mind the data is VARCHAR(50) ...
ascii 10.0 which needs to be returned as decimal.
-
Still don't have time.
I'm gonna repeat again that the min and max portions of the report can be calculeted by crystal report on the report itself so you don't have to code that part yourself (not 100% sure of this, but it's possible in Access 2000 so I assume that Crystal report has the same capabilities).
Can you post some sample data again with the results you are getting from the proc along with the expected results?
-
You have all the these functions in crystal Reports. It is a way better than access I would say. Max, Min, COunt, Avg allare there you name it it is there.
One feature I miss in crystal reports is select multiple values for a single parameter.
Regards,
gova -
I'll have to believe you because I never used Crystal report... but I would assume that it's more complete than Access.
-
Here is a working test table script for anyone who wants to try and help a poor newbie man.
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
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply