Nearest value query - 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

General SQL Server Forums

New to SQL Server Programming

Nearest value query

Author

Topic

maarten.del
Starting Member

17 Posts

Posted - 2011-03-03 : 07:56:26

I have this

Timestamp
Tagname
MeasValue

I want to write a query who gives me the last 5 values like this.

nearest value now - 7 days
nearest value now - 14 days
nearest value now - 21 days
nearest value now - 28 days
nearest value now - 35 days

and make the average from those 5 values.

I just want to look up the average from that value 5 weeks back in time. But the average must be from the 5 values i wrote above. Nearest now - 7, 14, 21, 28, 35 days in time.

Thanks in advance.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-03-03 : 08:07:06

You're going to have to provide sample data and sample output.

maarten.del
Starting Member

17 Posts

Posted - 2011-03-03 : 08:51:28

this is my data

RtuTimestamp RtuName TagName MeasValue
2011-02-03 06:15:00.000 st102 ST102H070_AVG 5.844277
2011-02-03 06:30:00.000 st102 ST102H070_AVG 5.75778
2011-02-03 06:45:00.000 st102 ST102H070_AVG 5.661999
2011-02-03 07:00:00.000 st102 ST102H070_AVG 5.561556
2011-02-03 07:15:00.000 st102 ST102H070_AVG 5.458278
2011-02-03 07:30:00.000 st102 ST102H070_AVG 5.375668
2011-02-03 07:45:00.000 st102 ST102H070_AVG 5.311943
2011-02-03 08:00:00.000 st102 ST102H070_AVG 5.259721
2011-02-03 08:15:00.000 st102 ST102H070_AVG 5.219725
2011-02-03 08:30:00.000 st102 ST102H070_AVG 5.166945
2011-02-03 08:45:00.000 st102 ST102H070_AVG 5.114112
2011-02-03 09:00:00.000 st102 ST102H070_AVG 5.061278
2011-02-03 09:15:00.000 st102 ST102H070_AVG 4.971833
2011-02-03 09:30:00.000 st102 ST102H070_AVG 4.867888
2011-02-03 09:45:00.000 st102 ST102H070_AVG 4.752778
2011-02-03 10:00:00.000 st102 ST102H070_AVG 4.688722
2011-02-03 10:15:00.000 st102 ST102H070_AVG 4.645945
2011-02-03 10:30:00.000 st102 ST102H070_AVG 4.606833
2011-02-03 10:45:00.000 st102 ST102H070_AVG 4.575555
2011-02-03 11:00:00.000 st102 ST102H070_AVG 4.541611
2011-02-03 11:15:00.000 st102 ST102H070_AVG 4.513556
2011-02-03 11:30:00.000 st102 ST102H070_AVG 4.489277
2011-02-03 11:45:00.000 st102 ST102H070_AVG 4.466889
2011-02-03 12:00:00.000 st102 ST102H070_AVG 4.448
2011-02-03 12:15:00.000 st102 ST102H070_AVG 4.431222
2011-02-03 12:30:00.000 st102 ST102H070_AVG 4.402278
2011-02-03 12:45:00.000 st102 ST102H070_AVG 4.376444
2011-02-03 13:00:00.000 st102 ST102H070_AVG 4.352722
2011-02-03 13:15:00.000 st102 ST102H070_AVG 4.329165
2011-02-03 13:30:00.000 st102 ST102H070_AVG 4.321167
2011-02-03 13:45:00.000 st102 ST102H070_AVG 4.32
2011-02-03 14:00:00.000 st102 ST102H070_AVG 4.325167
2011-02-03 14:15:00.000 st102 ST102H070_AVG 4.323444
2011-02-03 14:30:00.000 st102 ST102H070_AVG 4.319889
2011-02-03 14:45:00.000 st102 ST102H070_AVG 4.338666
2011-02-03 15:00:00.000 st102 ST102H070_AVG 4.362889
2011-02-03 15:15:00.000 st102 ST102H070_AVG 4.382777
2011-02-03 15:30:00.000 st102 ST102H070_AVG 4.4045
2011-02-03 15:45:00.000 st102 ST102H070_AVG 4.417222
2011-02-03 16:00:00.000 st102 ST102H070_AVG 4.413888
2011-02-03 16:15:00.000 st102 ST102H070_AVG 4.412722
2011-02-03 16:30:00.000 st102 ST102H070_AVG 4.416332
2011-02-03 16:45:00.000 st102 ST102H070_AVG 4.409277
2011-02-03 17:00:00.000 st102 ST102H070_AVG 4.389222
2011-02-03 17:15:00.000 st102 ST102H070_AVG 4.368111
2011-02-03 17:30:00.000 st102 ST102H070_AVG 4.341777
2011-02-03 17:45:00.000 st102 ST102H070_AVG 4.308222
2011-02-03 18:00:00.000 st102 ST102H070_AVG 4.261777
2011-02-03 18:15:00.000 st102 ST102H070_AVG 4.208944

when i do this query

select top(1) * from hislog where Tagname like 'ST102H070_avg' and rtutimestamp > dateadd(day,-7,getdate()) order by rtutimestamp asc

i get this

RtuTimestamp RtuName TagName MeasValue
2011-02-24 15:00:00.000 st102 ST102H070_AVG 4.613389

This value is the value nearest to now minus one week.

I want to get a query who gives me 5 lines with last weeks value, 2 weeks ago, ...

Or even better, the average from those 5 values.

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources