How to query lowest 3 consecutive combined values **

[svankley]

I'm inserting into a table, a value every five (5) minutes. How can I query for the minimum sustained 15 minute value for the day? This would be the three lowest contiguous combined values for the requested 24 hour period (i.e. 10:10, 10:15 & 10:20), or whatever the case may be.
This one has really got me stumped - any takers?? 8^)

Thanks in advance!!

Steve

 

[monksnake]

Is this what you mean:

Select top 3 timeCol from table 1 order by valueCol

<.

 

[svankley]

Actually the values have to be contiguous. So any three values in a row that have the lowest sum will be returned. So I guess it would have to calculate v1+v2+v3 and then v2+v3+v4 and then v3+v4+v5 and whatever the value is for the sum of the three in a row that are the lowest, thats the one to return. I hope I'm not being redundent, but I don't know how else to describe it. It's very hard to describe in a post. 8^)

 

[svankley]

Maybe this will help

The data is:
10:00 10
10:05 12
10:10 32
10:15 34
10:20 12
10:25 23
10:30 17
10:35 31
10:40 12
10:45 12
10:50 0
10:55 3
11:00 7
11:05 23
11:10 54
11:15 34
11:20 65
11:25 23
11:30 21
11:35 9
11:40 65
11:45 13
11:50 17
11:55 23
12:00 62
12:05 12
12:10 19
12:15 23

So in this example the minimum running 15-minute total would the 10:50-11:00 15-minute period with a total of 10.
The maximum running 15-minute total would the 11:10-11:20 15-minute period with a total of 153.

So I would want returned MIN = 10 and MAX=153

Hope this helps.

 

[gmmastros]

The code I show will ONLY work if the times are exactly minutes apart with no gaps. Also, the code I show puts your sample data in to a table variable so that I could test the functionality. This means you can copy/paste the query to query anaylyzer and run it. If it appears to work properly, then you need to change the query to suit your table and fields.

[COLOR=green]-- Sample data
[/color][COLOR=blue]Declare[/color] @Temp [COLOR=blue]Table[/color](TheTime [COLOR=#FF00FF]DateTime[/color], Val [COLOR=blue]Int[/color])
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:00'[/color],    10)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:05'[/color],    12)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:10'[/color],    32)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:15'[/color],    34)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:20'[/color],    12)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:25'[/color],    23)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:30'[/color],    17)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:35'[/color],    31)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:40'[/color],    12)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:45'[/color],    12)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:50'[/color],    0)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'10:55'[/color],    3)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:00'[/color],    7)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:05'[/color],    23)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:10'[/color],    54)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:15'[/color],    34)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:20'[/color],    65)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:25'[/color],    23)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:30'[/color],    21)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:35'[/color],    9)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:40'[/color],    65)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:45'[/color],    13)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:50'[/color],    17)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'11:55'[/color],    23)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'12:00'[/color],    62)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'12:05'[/color],    12)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'12:10'[/color],    19)
[COLOR=blue]Insert[/color] [COLOR=blue]Into[/color] @Temp [COLOR=blue]Values[/color]([COLOR=red]'12:15'[/color],    23)

[COLOR=green]-- Query starts here
[/color][COLOR=blue]Select[/color] [COLOR=#FF00FF]Min[/color](NewVal) [COLOR=blue]As[/color] [COLOR=blue]MinValue[/color],
       [COLOR=#FF00FF]Max[/color](NewVal) [COLOR=blue]As[/color] [COLOR=blue]MaxValue[/color]
[COLOR=blue]From[/color]   (
       [COLOR=blue]Select[/color] A.Val + B.Val + C.Val [COLOR=blue]As[/color] NewVal
       [COLOR=blue]From[/color]   @Temp A
              [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] @Temp B
                 [COLOR=blue]On[/color] A.TheTime = [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Minute[/color], -5, B.TheTime)
              [COLOR=blue]Inner[/color] [COLOR=blue]Join[/color] @Temp C
                 [COLOR=blue]On[/color] B.TheTime = [COLOR=#FF00FF]DateAdd[/color]([COLOR=#FF00FF]Minute[/color], -5, C.TheTime)
       ) [COLOR=blue]As[/color] A

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[svankley]

You absolutely rock gmmastros!! That's perfect.

Now I just have to figure out how to covert it to Access - I was just told about 10 minutes ago that the end user now wants to use MS Access for storage portability.

THANKS AGAIN!!!