Returning the Max Date

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to return the row with the maximum [effective date] and
[expiration date] where the member number exists in a joined table and the
[expiration date] is prior to now(). Rows that have the same [effective
date] and [expiration date] are void, so I have to be sure and not return
those (you'll notice it in the where clause). Here's an example of 3 rows
that are the same except for the 2 date fields. When I run my query it
returns all 3 and I want it to only return the one where the most recent
dates are. I've converted the dates into the format below, so feel free to
change the way they look.

Row Values:
MemberID | FirstName | LastName | EffectiveDate |
ExpirationDate
011111 | Jane | Doe | 20020101 |
20020630
011111 | Jane | Doe | 20040401 |
20050531
011111 | Jane | Doe | 20050601 |
20050630

Query:
SELECT DISTINCT l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] = l.[MemberID]
WHERE (a.[EffectiveDate] < a.[ExpirationDate]) AND (a.[ExpirationDate] <
Now())
GROUP BY l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate

Thanks,
Matt
 
Rows with the same EffectiveDate and ExpirationDate are to be eliminated.
So the criteria there should be
a.[EffectiveDate] <> a.[ExpirationDate] versus a.[EffectiveDate] <
a.[ExpirationDate]

Rows that have an expirationdate prior to now (this seems to be correct)
A.[ExpirationDate] < Now()

Maximum EffectiveDate and Maximum ExpirationDate - this could be tricky as
those two values may not be in the same record. However if that doesn't
matter then the following should work

SELECT l.MemberID, l.FirstName, l.LastName,
Max(a.EffectiveDate) as LatestEffective,
Max(a.ExpirationDate) as LatestExpiration
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] = l.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
GROUP BY l.MemberID, l.FirstName, l.LastName

If you need to get the row with the Maximum EffectiveDate and its
corresponding ExpirationDate OR the row with Maximum ExpirationDate and its
corresponding EffectiveDate, then post back asking that question.
 
That seems to have worked.

Thanks!

John Spencer said:
Rows with the same EffectiveDate and ExpirationDate are to be eliminated.
So the criteria there should be
a.[EffectiveDate] <> a.[ExpirationDate] versus a.[EffectiveDate] <
a.[ExpirationDate]

Rows that have an expirationdate prior to now (this seems to be correct)
A.[ExpirationDate] < Now()

Maximum EffectiveDate and Maximum ExpirationDate - this could be tricky as
those two values may not be in the same record. However if that doesn't
matter then the following should work

SELECT l.MemberID, l.FirstName, l.LastName,
Max(a.EffectiveDate) as LatestEffective,
Max(a.ExpirationDate) as LatestExpiration
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] = l.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
GROUP BY l.MemberID, l.FirstName, l.LastName

If you need to get the row with the Maximum EffectiveDate and its
corresponding ExpirationDate OR the row with Maximum ExpirationDate and its
corresponding EffectiveDate, then post back asking that question.

Matt said:
I'm trying to return the row with the maximum [effective date] and
[expiration date] where the member number exists in a joined table and the
[expiration date] is prior to now(). Rows that have the same [effective
date] and [expiration date] are void, so I have to be sure and not return
those (you'll notice it in the where clause). Here's an example of 3 rows
that are the same except for the 2 date fields. When I run my query it
returns all 3 and I want it to only return the one where the most recent
dates are. I've converted the dates into the format below, so feel free
to
change the way they look.

Row Values:
MemberID | FirstName | LastName | EffectiveDate |
ExpirationDate
011111 | Jane | Doe | 20020101 |
20020630
011111 | Jane | Doe | 20040401 |
20050531
011111 | Jane | Doe | 20050601 |
20050630

Query:
SELECT DISTINCT l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] =
l.[MemberID]
WHERE (a.[EffectiveDate] < a.[ExpirationDate]) AND (a.[ExpirationDate] <
Now())
GROUP BY l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate

Thanks,
Matt
Click to expand...
Click to expand...
 
I've been asked to add another column from a different table and am having
the same problem with it returning multiple rows. The Code column is the new
one and I want the value that corresponds to the most recent b.EffectiveDate
column from that table. The Code column contains either a 4 digit number or
"TEMP". Oh, I do need the a.EffectiveDate that corresponds to the row with
the Max(a.ExpirationDate). Here's the query:

SELECT l.MemberID, l.FirstName, l.LastName,
Max(a.EffectiveDate) as LatestEffective,
Max(a.ExpirationDate) as LatestExpiration
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] = l.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
GROUP BY l.MemberID, l.FirstName, l.LastName


Thanks again,
Matt

John Spencer said:
Rows with the same EffectiveDate and ExpirationDate are to be eliminated.
So the criteria there should be
a.[EffectiveDate] <> a.[ExpirationDate] versus a.[EffectiveDate] <
a.[ExpirationDate]

Rows that have an expirationdate prior to now (this seems to be correct)
A.[ExpirationDate] < Now()

Maximum EffectiveDate and Maximum ExpirationDate - this could be tricky as
those two values may not be in the same record. However if that doesn't
matter then the following should work

SELECT l.MemberID, l.FirstName, l.LastName,
Max(a.EffectiveDate) as LatestEffective,
Max(a.ExpirationDate) as LatestExpiration
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] = l.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
GROUP BY l.MemberID, l.FirstName, l.LastName

If you need to get the row with the Maximum EffectiveDate and its
corresponding ExpirationDate OR the row with Maximum ExpirationDate and its
corresponding EffectiveDate, then post back asking that question.

Matt said:
I'm trying to return the row with the maximum [effective date] and
[expiration date] where the member number exists in a joined table and the
[expiration date] is prior to now(). Rows that have the same [effective
date] and [expiration date] are void, so I have to be sure and not return
those (you'll notice it in the where clause). Here's an example of 3 rows
that are the same except for the 2 date fields. When I run my query it
returns all 3 and I want it to only return the one where the most recent
dates are. I've converted the dates into the format below, so feel free
to
change the way they look.

Row Values:
MemberID | FirstName | LastName | EffectiveDate |
ExpirationDate
011111 | Jane | Doe | 20020101 |
20020630
011111 | Jane | Doe | 20040401 |
20050531
011111 | Jane | Doe | 20050601 |
20050630

Query:
SELECT DISTINCT l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] =
l.[MemberID]
WHERE (a.[EffectiveDate] < a.[ExpirationDate]) AND (a.[ExpirationDate] <
Now())
GROUP BY l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate

Thanks,
Matt
Click to expand...
Click to expand...
 
SELECT L.MemberID, L.FirstName, L.LastName, a.EffectiveDate,
a.ExpirationDate
FROM LostMembers AS L INNER JOIN Active AS a ON a.[MemberNo] = L.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
AND A.EffectiveDate =
(SELECT Max(EffectiveDate)
FROM Active
WHERE Active.MemberID=L.MemberID)

I don't have a clue on how to solve your new problem. Not enough
information. I am going home shortly, so perhaps I will see you on Monday.

Matt said:
I've been asked to add another column from a different table and am having
the same problem with it returning multiple rows. The Code column is the
new
one and I want the value that corresponds to the most recent
b.EffectiveDate
column from that table. The Code column contains either a 4 digit number
or
"TEMP". Oh, I do need the a.EffectiveDate that corresponds to the row
with
the Max(a.ExpirationDate). Here's the query:

SELECT l.MemberID, l.FirstName, l.LastName,
Max(a.EffectiveDate) as LatestEffective,
Max(a.ExpirationDate) as LatestExpiration
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] =
l.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
GROUP BY l.MemberID, l.FirstName, l.LastName


Thanks again,
Matt

John Spencer said:
Rows with the same EffectiveDate and ExpirationDate are to be eliminated.
So the criteria there should be
a.[EffectiveDate] <> a.[ExpirationDate] versus a.[EffectiveDate] <
a.[ExpirationDate]

Rows that have an expirationdate prior to now (this seems to be correct)
A.[ExpirationDate] < Now()

Maximum EffectiveDate and Maximum ExpirationDate - this could be tricky
as
those two values may not be in the same record. However if that doesn't
matter then the following should work

SELECT l.MemberID, l.FirstName, l.LastName,
Max(a.EffectiveDate) as LatestEffective,
Max(a.ExpirationDate) as LatestExpiration
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] =
l.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
GROUP BY l.MemberID, l.FirstName, l.LastName

If you need to get the row with the Maximum EffectiveDate and its
corresponding ExpirationDate OR the row with Maximum ExpirationDate and
its
corresponding EffectiveDate, then post back asking that question.

Matt said:
I'm trying to return the row with the maximum [effective date] and
[expiration date] where the member number exists in a joined table and
the
[expiration date] is prior to now(). Rows that have the same
[effective
date] and [expiration date] are void, so I have to be sure and not
return
those (you'll notice it in the where clause). Here's an example of 3
rows
that are the same except for the 2 date fields. When I run my query it
returns all 3 and I want it to only return the one where the most
recent
dates are. I've converted the dates into the format below, so feel
free
to
change the way they look.

Row Values:
MemberID | FirstName | LastName | EffectiveDate |
ExpirationDate
011111 | Jane | Doe | 20020101
|
20020630
011111 | Jane | Doe | 20040401
|
20050531
011111 | Jane | Doe | 20050601
|
20050630

Query:
SELECT DISTINCT l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] =
l.[MemberID]
WHERE (a.[EffectiveDate] < a.[ExpirationDate]) AND (a.[ExpirationDate]
<
Now())
GROUP BY l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate

Thanks,
Matt
Click to expand...
Click to expand...
Click to expand...
 
Not a problem. Thanks for all of your help!

John Spencer said:
SELECT L.MemberID, L.FirstName, L.LastName, a.EffectiveDate,
a.ExpirationDate
FROM LostMembers AS L INNER JOIN Active AS a ON a.[MemberNo] = L.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
AND A.EffectiveDate =
(SELECT Max(EffectiveDate)
FROM Active
WHERE Active.MemberID=L.MemberID)

I don't have a clue on how to solve your new problem. Not enough
information. I am going home shortly, so perhaps I will see you on Monday.

Matt said:
I've been asked to add another column from a different table and am having
the same problem with it returning multiple rows. The Code column is the
new
one and I want the value that corresponds to the most recent
b.EffectiveDate
column from that table. The Code column contains either a 4 digit number
or
"TEMP". Oh, I do need the a.EffectiveDate that corresponds to the row
with
the Max(a.ExpirationDate). Here's the query:

SELECT l.MemberID, l.FirstName, l.LastName,
Max(a.EffectiveDate) as LatestEffective,
Max(a.ExpirationDate) as LatestExpiration
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] =
l.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
GROUP BY l.MemberID, l.FirstName, l.LastName


Thanks again,
Matt

John Spencer said:
Rows with the same EffectiveDate and ExpirationDate are to be eliminated.
So the criteria there should be
a.[EffectiveDate] <> a.[ExpirationDate] versus a.[EffectiveDate] <
a.[ExpirationDate]

Rows that have an expirationdate prior to now (this seems to be correct)
A.[ExpirationDate] < Now()

Maximum EffectiveDate and Maximum ExpirationDate - this could be tricky
as
those two values may not be in the same record. However if that doesn't
matter then the following should work

SELECT l.MemberID, l.FirstName, l.LastName,
Max(a.EffectiveDate) as LatestEffective,
Max(a.ExpirationDate) as LatestExpiration
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] =
l.[MemberID]
WHERE (a.[EffectiveDate] <> a.[ExpirationDate])
AND (a.[ExpirationDate] < Now())
GROUP BY l.MemberID, l.FirstName, l.LastName

If you need to get the row with the Maximum EffectiveDate and its
corresponding ExpirationDate OR the row with Maximum ExpirationDate and
its
corresponding EffectiveDate, then post back asking that question.

I'm trying to return the row with the maximum [effective date] and
[expiration date] where the member number exists in a joined table and
the
[expiration date] is prior to now(). Rows that have the same
[effective
date] and [expiration date] are void, so I have to be sure and not
return
those (you'll notice it in the where clause). Here's an example of 3
rows
that are the same except for the 2 date fields. When I run my query it
returns all 3 and I want it to only return the one where the most
recent
dates are. I've converted the dates into the format below, so feel
free
to
change the way they look.

Row Values:
MemberID | FirstName | LastName | EffectiveDate |
ExpirationDate
011111 | Jane | Doe | 20020101
|
20020630
011111 | Jane | Doe | 20040401
|
20050531
011111 | Jane | Doe | 20050601
|
20050630

Query:
SELECT DISTINCT l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate
FROM LostMembers AS l INNER JOIN Active AS a ON a.[MemberNo] =
l.[MemberID]
WHERE (a.[EffectiveDate] < a.[ExpirationDate]) AND (a.[ExpirationDate]
<
Now())
GROUP BY l.MemberID, l.FirstNAme, l.LastName, a.EffectiveDate,
a.ExpirationDate

Thanks,
Matt
Click to expand...
Click to expand...
Click to expand...
 
Back
Top