|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
 |
|
|
|
|
|||||||||
|
|||||||||
|
|||||||||
| |
|||
|
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Display Modes |
Dev Articles Community Forums Sponsor:
|
|
Stay one step ahead of the competition. Evaluate and give feedback
on some of the hottest web development tools on the market today.
Make your opinion heard! Click
Here
|
|
#1
October 2nd, 2007, 10:01 AM
|
|||
|
|||
|
Return value in effect on/before certain date?
my table data (other columns removed for simplicity)
ID WorkCodeID WorkCodeVal WorkCodeEffctvDt 6545 21 0.000 2004-01-01 7009 21 38.000 2006-06-05 7196 21 40.000 2006-05-01 7642 21 45.000 2005-10-18 i want to return the value that was in effect on 2007-06-18 that should return 38.0000 i need this as a subqeury i have tried various incantations, but always get 0.000 mark
|
|
#2
October 3rd, 2007, 02:22 AM
|
|||
|
|||
Are you trying to return more than one row? In your implementation, it appears to be that there could be more than one WorkCodeID that is in effect on 2007-06-18.
In your example. All rows will be returned if you run the following query Code:
SELECT WorkCodeID FROM `table` WHERE WorkCodeEffctvDt <= "2007-06-18" AND WorkCodeEffctvDt > "1999-01-01"; Will return, Code:
0.000 38.000 40.000 45.000 If you run the following query Code:
SELECT WorkCodeID FROM `table` WHERE WorkCodeEffctvDt <= "2004-01-01" AND WorkCodeEffctvDt > "1999-01-01"; This will return "21" Unless you set a pre-defined range, there will always be possibility that you return more than one row.. The queries above relies on Ranges so you can probably use BETWEEN here. hope that helps
|
|
#3
October 3rd, 2007, 09:29 AM
|
|||
|
|||
|
that is the trick - i only want 1 value returned
in my example, i want 38.000 there may be many values as they change over time i want the 1 that was in effect on or before the date i specify Quote:
|
|
#4
October 3rd, 2007, 06:23 PM
|
|||
|
|||
|
Oh ok, in this case the only way to go is to return the closest one to the date that is on or before.
So instead of "Ranges" you need the "Closest", try this one: Code:
SELECT WorkCodeID FROM `table` WHERE WorkCodeEffctvDt <= "2006-11-18" ORDER BY WorkCodeEffctvDt DESC LIMIT 1; This should *theoretically return the nearest one.. hope that helps
|
|
| Viewing: Dev Articles Community Forums > Databases > MySQL Development > Return value in effect on/before certain date? |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
