469,929 Members | 1,597 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,929 developers. It's quick & easy.

Max Date Query

I have a problem I'm trying to solve, but for the life of me, cannot figure out how to get it to work.

I have two tables, each with a "Last Update Date" field. The query I want to create would match each "Last Update Date" field for table one, with the most recent date prior to that from table two.

For example. Table one would have records for 5/1/07, I would want the most recent date prior to that from table two (which aren't necessarily the day before).

I've used the "max" function, but can't get more than one date to display. I would need the prior date for every record in table one.

Thanks.
May 1 '07 #1
7 3015
Rabbit
12,516 Expert Mod 8TB
Just set the criteria for the date in Table 2 to < Table1's Date.
May 1 '07 #2
Just set the criteria for the date in Table 2 to < Table1's Date.
That pulls in ALL the dates prior to the table 1 date. I only want the one date that's immediately prior to it.

Sorry if I didn't make that clear.
May 1 '07 #3
Rabbit
12,516 Expert Mod 8TB
Then SELECT TOP 1 and order it by descending date.
May 1 '07 #4
Then SELECT TOP 1 and order it by descending date.
That only gives me the date for one record. I needs the immediately prior date for EACH record from table one. Let me give you an example of the dataset:

table1 table2
5/1/07 5/1/07
5/1/07 4/30/07
5/1/07 4/15/07
5/1/07 4/14/07
4/30/07 4/30/07
4/30/07 4/26/07
4/30/07 4/24/07
4/26/07 4/26/07
4/26/07 4/19/07
4/26/07 4/16/07

So the records I would want returned would be
5/1/07 4/30/07
4/30/07 4/26/07
4/26/07 4/19/07

Does that help?
May 1 '07 #5
Rabbit
12,516 Expert Mod 8TB
Now you're getting into complicated territory. You're going to have to SELECT DISTINCT and use a subquery or DMax to achieve what you want.
May 1 '07 #6
Now you're getting into complicated territory. You're going to have to SELECT DISTINCT and use a subquery or DMax to achieve what you want.
Yep. Already figured out how complicated this is turning out to be. :)
May 1 '07 #7
Rabbit
12,516 Expert Mod 8TB
Yep. Already figured out how complicated this is turning out to be. :)
Let us know if you get stuck on any part of that. First you have to decide if you want to go the DMax route or the subquery route.

DMax is easier but not as efficient.
May 1 '07 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

4 posts views Thread by Russell | last post: by
10 posts views Thread by Kenneth | last post: by
7 posts views Thread by Nicolae Fieraru | last post: by
4 posts views Thread by Peter Bailey | last post: by
12 posts views Thread by Steve Elliott | last post: by
10 posts views Thread by Daniel | last post: by
2 posts views Thread by sixdeuce62 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.