| View previous topic :: View next topic |
| Author |
Message |
larand54
l33t
Joined: 20 Feb 2004
Posts: 695
Location: Sweden
|
 Posted: Sat Sep 29, 2007 5:36 pm Post subject: An SQL-query question[SOLVED] |
 |
|
I got a table tha are keyed with dates.
This table is populated with a set of records entered at different dates during a few years.
I want to enter a date that can be between two records and get the result from the nearest previous date and the nearest succeding date.
I thought I could solve this using two SQL-querys but I failed.
An example of the table:
| Code: |
Date Value
2004-12-16 1000
2005-05-30 10000
2005-08-30 18560
2005-11-05 29930
....
....
|
Suppose I got the the date 2005-06-22 and want an interpolated value for that date. Then I need to get the dates 2005-05-30 and 2005-08-30.
How can I solve this with SQL ?
Last edited by larand54 on Sat Sep 29, 2007 7:42 pm; edited 1 time in total |
|
| Back to top |
|
 |
nixnut
Bodhisattva
Joined: 09 Apr 2004
Posts: 10974
Location: the dutch mountains
|
| Posted: Sat Sep 29, 2007 5:45 pm Post subject: |
|
|
Something like this ought to do it I think:
| Code: | select max(Value) AS LowerValue from Mytable where Date < 2005-06-22
union
select min (Value) AS UpperValue from Mytable where Date > 2005-06-22 |
_________________
Please add [solved] to the initial post's subject line if you feel your problem is resolved. Help answer the unanswered
talk is cheap. supply exceeds demand |
|
| Back to top |
|
|
larand54
l33t
Joined: 20 Feb 2004
Posts: 695
Location: Sweden
|
| Posted: Sat Sep 29, 2007 6:45 pm Post subject: |
|
|
| Hmm....It works but I also need the date for each value but that's not possible to include in the select as I see it...or is it possible? |
|
| Back to top |
|
|
nixnut
Bodhisattva
Joined: 09 Apr 2004
Posts: 10974
Location: the dutch mountains
|
| Posted: Sat Sep 29, 2007 7:08 pm Post subject: |
|
|
This maybe?
| Code: | select max(Date) AS LowerDate, max(Value) AS LowerValue from Mytable where Date < 2005-06-22
union
select min(Date) AS UpperDate, min (Value) AS UpperValue from Mytable where Date > 2005-06-22 |
Mind you, you don't want to run this on very large datasets.
_________________
Please add [solved] to the initial post's subject line if you feel your problem is resolved. Help answer the unanswered
talk is cheap. supply exceeds demand |
|
| Back to top |
|
|
larand54
l33t
Joined: 20 Feb 2004
Posts: 695
Location: Sweden
|
| Posted: Sat Sep 29, 2007 7:42 pm Post subject: |
|
|
Perfect! 
I have a lot to learn...Thanks! |
|
| Back to top |
|
|
|
Portage & Programming
