query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Other SQL Server 2008 Topics

query help

Author

Topic

markofdiego
Starting Member

13 Posts

Posted - 2008-06-27 : 18:01:26

I need to retrieve multiple maximums from one tag between 2 dates. Is this possible?

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-27 : 23:03:24

Have table schema and sample data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-28 : 03:14:38

quote:
Originally posted by markofdiego

I need to retrieve multiple maximums from one tag between 2 dates. Is this possible?

multiple maximums of what? Please elaborate what you want clearly with some sample data

markofdiego
Starting Member

13 Posts

Posted - 2008-06-30 : 10:50:09

Part of the table would be with data:

DateTime TagName Value
6:01 PM Analog .1111
6:02 PM Analog .1122
6:03 PM Analog .1133
6:04 PM Analog .1122
6:05 PM Analog .1111
6:06 PM Analog .1122
6:07 PM Analog .1111
6:08 PM Analog .1122
6:09 PM Analog .1133
6:010 PM Analog .1122

So I would want to return with one query:

6:03 PM Analog .1133
6:06 PM Analog .1122
6:09 PM Analog .1133

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 10:55:24

quote:
Originally posted by markofdiego

Part of the table would be with data:

DateTime TagName Value
6:01 PM Analog .1111
6:02 PM Analog .1122
6:03 PM Analog .1133
6:04 PM Analog .1122
6:05 PM Analog .1111
6:06 PM Analog .1122
6:07 PM Analog .1111
6:08 PM Analog .1122
6:09 PM Analog .1133
6:010 PM Analog .1122

So I would want to return with one query:

6:03 PM Analog .1133
6:06 PM Analog .1122
6:09 PM Analog .1133

Whats your criteria for selecting those three rows?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 11:06:10

is it that you want every third record? then use

SELECT t.DateTime, t.TagName, t.Value
FROM
(SELECT ROW_NUMBER() OVER(ORDER BY DateTime) AS RowNo,
DateTime, TagName, Value
FROM YourTable
)t
WHERE t.RowNo%3 = 0

markofdiego
Starting Member

13 Posts

Posted - 2008-06-30 : 11:09:06

Select DateTime, TageName, Max(Value) -----> But I need mulitple max's
From Table A
Where DateTime >= 6:00 PM
AND DateTime <= 6:10 PM

markofdiego
Starting Member

13 Posts

Posted - 2008-06-30 : 11:12:29

The data that will be coming in will be like a SINE wave. So I need to return the peak of each cycle. There will not be any pattern.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 11:13:40

quote:
Originally posted by markofdiego

Select DateTime, TageName, Max(Value) -----> But I need mulitple max's
From Table A
Where DateTime >= 6:00 PM
AND DateTime <= 6:10 PM

ok i agree. but then why didnt you chose other rows though some had one of your interested max value .1122? please make clear you rules for finding the max rows.

markofdiego
Starting Member

13 Posts

Posted - 2008-06-30 : 11:19:58

DateTime TagName Value
6:01 PM Analog .11
6:02 PM Analog .12
6:03 PM Analog .13
6:04 PM Analog .12
6:05 PM Analog .11
6:06 PM Analog .14
6:07 PM Analog .11
6:08 PM Analog .12
6:09 PM Analog .15
6:10 PM Analog .12

So I would want to return with one query:

6:03 PM Analog .13
6:06 PM Analog .14
6:09 PM Analog .15

Just because otehrs match a result does not mean I need them. If the result was not the peak between the valley then I don't want that result.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 13:07:56

quote:
Originally posted by markofdiego

DateTime TagName Value
6:01 PM Analog .11
6:02 PM Analog .12
6:03 PM Analog .13
6:04 PM Analog .12
6:05 PM Analog .11
6:06 PM Analog .14
6:07 PM Analog .11
6:08 PM Analog .12
6:09 PM Analog .15
6:10 PM Analog .12

So I would want to return with one query:

6:03 PM Analog .13
6:06 PM Analog .14
6:09 PM Analog .15

Just because otehrs match a result does not mean I need them. If the result was not the peak between the valley then I don't want that result.

ok.Try this:-

;With Peak_Value_CTE (Seq,DateTime, TagName, Value) AS
(SELECT ROW_NUMBER() OVER (ORDER BY DateTime),
DateTime, TagName, Value
FROM YourTable)

SELECT c.DateTime, c.TagName, c.Value
FROM Peak_Value_CTE c
CROSS APPLY(SELECT Value
            FROM Peak_Value_CTE
            WHERE Seq=c.Seq-1)p
CROSS APPLY(SELECT Value
            FROM Peak_Value_CTE
            WHERE Seq=c.Seq+1)n
WHERE c.Value>p.Value
AND c.Value>n.Value

markofdiego
Starting Member

13 Posts

Posted - 2008-06-30 : 13:40:05

It sort of works.
If I narrow the search for one peak it works.
Two peaks the first result is right the second is off by going to the next one after the right one.
Three peaks the are all off by one going to the next.
Four peaks they are all off but going different directions.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-30 : 13:55:19

quote:
Originally posted by markofdiego

It sort of works.
If I narrow the search for one peak it works.
Two peaks the first result is right the second is off by going to the next one after the right one.
Three peaks the are all off by one going to the next.
Four peaks they are all off but going different directions.

didnt get that. what do you mean by two peaks,three peaks..?

markofdiego
Starting Member

13 Posts

Posted - 2008-06-30 : 15:04:43

the peak of the data. Like in the example data set the results are the peaks. Do you know what a sine wave looks like on math. There are peaks and valleys. The peak is the maximum. My data will follow this style of format as it is a collection of data on a power system.

markofdiego
Starting Member

13 Posts

Posted - 2008-06-30 : 15:26:39

OK, I figured it out. The datadbase I am using have diffent retrieval modes and I needed to change the mode.

Thank you for your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources