Reading backwards thru Table to get nearest date

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

Reading backwards thru Table to get nearest date

Author

Topic

Siopold
Starting Member

9 Posts

Posted - 2010-01-13 : 13:01:09

Hi,

The problem I am attempting to solve is as follows;
I asked a similar question on this forum before and thought that I had resolved it in a previous SQL but does not seem to work here.

I am doing a subselect on a table where I wish to retrieve one date based upon a date I pass.
However I can get a direct match or crucially if not a direct match I want to retrieve the nearest previous date to that match

E.g. Table contains

20090601
20090701
20090901

I have the date 20090801

This date does not match exactly so I want to pick up the nearest (previous) date to it 20090701 (in this example)

-- Looking to retrieve specific record or previous

So something like this should work
But for some reason it brings back 20090901

Am I missing something obvious? Any help appreciated

where
(a.date in (Select Top 1 a.date
from tableb
where a.date <= b.date2
order by a.date desc))

Regards
D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 13:04:38

its should work. b/w which version are you using?

SQL Server MVP

Siopold
Starting Member

9 Posts

Posted - 2010-01-13 : 14:06:59

Hi Visakh16,

If I am not mistaken you may have helped me with this or a similar issue before.
And I am almost sure I had this working before. I am using SQL Server 2005.
I should mention that I am not an SQL person so not sure if MVP. I am doing this for someone else. If you say that there is nothing obviously wrong with that statement I will try a few more variations and see if I am soing something silly somewhere, very possible.

Regards

D

Siopold
Starting Member

9 Posts

Posted - 2010-01-13 : 15:53:18

An update. I reorgnised the SQL joins there were three and used this code and it seems to be working now. Thanks for the help and for reviewing the code. Until next time.

Ta

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-01-13 : 23:58:23

No problem you're welcome

SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources