Select the record pertaining to the latest end_dt for each member

[Crystal via AccessMonster.com]

Hello experts,

I have a table that contains multiple plan records for members. What is the
best way to query and select ONLY the record pertaining to the latest end_dt
for each member (In this case, it would the 1st and last record for each of
the id)? Thank you in advance!

Mem_ID PLAN START_DT END_DT

1 CH 2005-04-01 2005-09-30
1 HN 2004-05-01 2004-11-30
1 SH 2004-12-01 2005-03-31
2 CH 2004-01-01 2005-08-31
2 HN 2005-09-01 9999-12-31

I tried this and it did not work--)—I missed out mem_id 1’s record
select * from tbl
where end_dt in (select max(end_dt) from tbl.

This gives me multiple records for each id:
Select mem_id, plan, last(end_dt)
From tbl
Group by…

 

[Albert D.Kallal]

Build the query form the members table..not the member "plan" table.

The members table will supple the list of mem_id you need..

You query can thus be


select FirstName, Lastname, WorkPhone, PLAN, Start_DT, END_DT
from tblMembers
inner join tblPlan on tblMembers.id = tblPlan.mem_id
Where mem_id in
(select top1 mem_id from tblPlan
where mem_id = tblMembers.id order by DSC StartDate)

Also, if you want to include members in the repot...even if they do NOT have
a plan, then change the "inner join" to a "left join". Often, you will want
a print out of all members....even if they do not have a last plan.....

 

[James A. Fortune]

Hello experts,

I have a table that contains multiple plan records for members. What is the
best way to query and select ONLY the record pertaining to the latest end_dt
for each member (In this case, it would the 1st and last record for each of
the id)? Thank you in advance!

Mem_ID PLAN START_DT END_DT

1 CH 2005-04-01 2005-09-30
1 HN 2004-05-01 2004-11-30
1 SH 2004-12-01 2005-03-31
2 CH 2004-01-01 2005-08-31
2 HN 2005-09-01 9999-12-31

I tried this and it did not work--)—I missed out mem_id 1’s record
select * from tbl
where end_dt in (select max(end_dt) from tbl.

This gives me multiple records for each id:
Select mem_id, plan, last(end_dt)
From tbl
Group by…

Try:

SELECT * FROM tblPlan WHERE END_DT = (SELECT MAX(A.END_DT) FROM tblPlan
AS A WHERE A.Mem_ID = tblPlan.Mem_ID);

James A. Fortune
(e-mail address removed)

 

[Crystal via AccessMonster.com]

Thank to both of you for helping me here. I tried James sql and it worked
perfect.

James, would you be kind enough as to explain why your subquery will pick up
all the latest record for each emember, not just the members with maximum
end_dt (12/31/9999)?

Thanks!

Hello experts,

[quoted text clipped - 19 lines]

From tbl
Group by…

Try:

SELECT * FROM tblPlan WHERE END_DT = (SELECT MAX(A.END_DT) FROM tblPlan
AS A WHERE A.Mem_ID = tblPlan.Mem_ID);

James A. Fortune
(e-mail address removed)

 

[James A. Fortune]

Thank to both of you for helping me here. I tried James sql and it worked
perfect.

James, would you be kind enough as to explain why your subquery will pick up
all the latest record for each emember, not just the members with maximum
end_dt (12/31/9999)?

Thanks!

Hello experts,

[quoted text clipped - 19 lines]

From tbl
Group by…

Try:

SELECT * FROM tblPlan WHERE END_DT = (SELECT MAX(A.END_DT) FROM tblPlan
AS A WHERE A.Mem_ID = tblPlan.Mem_ID);

James A. Fortune
(e-mail address removed)

Crystal,

Albert's answer was very good. He was thinking farther ahead than I was.

I'll try to explain how the SQL I posted works and why you shouldn't use
it .

the data:

Mem_ID PLAN START_DT END_DT

1 CH 2005-04-01 2005-09-30
1 HN 2004-05-01 2004-11-30
1 SH 2004-12-01 2005-03-31
2 CH 2004-01-01 2005-08-31
2 HN 2005-09-01 9999-12-31


the SQL I posted:
SELECT * FROM tblPlan WHERE END_DT = (SELECT MAX(A.END_DT) FROM tblPlan
AS A WHERE A.Mem_ID = tblPlan.Mem_ID);

This not as efficient as joining to a table of unique Mem_ID values as
Albert suggested. Using the INNER or OUTER join from tblMember makes it
so that each member's max END_DT is found only once. Another way to go
about this (for illustration only) would be to use nested queries where
the first one grabs all the unique Mem_ID's in tblPlan first:

qryMem_ID:
SELECT DISTINCT Mem_ID FROM tblPlan;

then,

qryLatestPlan:
SELECT tblPlan.* FROM qryMem_ID INNER JOIN tblPlan ON qryMem_ID.Mem_ID =
tblPlan.Mem_ID WHERE tblPlan.END_DT = (SELECT Max(A.END_DT) FROM tblPlan
AS A WHERE A.Mem_ID = tblPlan.Mem_ID);

If Mem_ID is the primary key of tblMember then qryMem_ID can be replaced
by just the tblMember.Mem_ID values.

The first three lines of the SQL I posted will do something like

SELECT line1 FROM tblPlan if #9/30/05# = #9/30/05#
SELECT line2 FROM tblPlan if #11/30/04# = #9/30/05#
SELECT line3 FROM tblPlan if #3/31/05# = #9/30/05#

because the subquery will return #9/30/05# for Mem_ID = 1

The next two lines will do something like

SELECT line4 FROM tblPlan if #8/31/05# = #12/31/9999#
SELECT line5 FROM tblPlan if #12/31/9999# = #12/31/9999#

because the subquery returns #12/31/9999# when Mem_ID = 2

Therefore the query will return lines 1 and 5 from tblPlan.

The weakness of this SQL is that it has to recalculate the max END_DT
for each group of records with the same Mem_ID even if the Mem_ID hasn't
changed since the last record.

So I am recommending that you try Albert's suggestion after deciding
whether you want the Mem_ID's in tblPlan only (INNER JOIN) or all the
Mem_ID's in tblMember (LEFT JOIN) whether or not there is a value in
tblPlan. Again, the reason for the better efficiency of Albert's
suggestion is that a set of unique Mem_ID values is already sitting in
tblMember so a query doesn't have to calculate that set. OTOH, when the
max date is found once per Mem_ID using Albert's join, the comparison
still has to be made with the dates in tblPlan matching Mem_ID. So it
comes down to finding a max date versus matching a value. I'm confident
that determining the max date from a group of size N is much slower than
checking for a value from a group N times after finding the max date
once for the group.

I'll try to plan further ahead in the future. Let us know if you need
help implementing Albert's idea.

I hope this helps,

James A. Fortune
(e-mail address removed)

 

[Crystal via AccessMonster.com]

James,

You helped me far beyond what I had asked for. Thank you for helping me
understand a better way of querying and appreciate Albert's sql too. I really
appreciate all of you guys' generous efforts.

Thank to both of you for helping me here. I tried James sql and it worked
perfect.

[quoted text clipped - 19 lines]
Crystal,

Albert's answer was very good. He was thinking farther ahead than I was.

I'll try to explain how the SQL I posted works and why you shouldn't use
it .

the data:

Mem_ID PLAN START_DT END_DT

1 CH 2005-04-01 2005-09-30
1 HN 2004-05-01 2004-11-30
1 SH 2004-12-01 2005-03-31
2 CH 2004-01-01 2005-08-31
2 HN 2005-09-01 9999-12-31

the SQL I posted:
SELECT * FROM tblPlan WHERE END_DT = (SELECT MAX(A.END_DT) FROM tblPlan
AS A WHERE A.Mem_ID = tblPlan.Mem_ID);

This not as efficient as joining to a table of unique Mem_ID values as
Albert suggested. Using the INNER or OUTER join from tblMember makes it
so that each member's max END_DT is found only once. Another way to go
about this (for illustration only) would be to use nested queries where
the first one grabs all the unique Mem_ID's in tblPlan first:

qryMem_ID:
SELECT DISTINCT Mem_ID FROM tblPlan;

then,

qryLatestPlan:
SELECT tblPlan.* FROM qryMem_ID INNER JOIN tblPlan ON qryMem_ID.Mem_ID =
tblPlan.Mem_ID WHERE tblPlan.END_DT = (SELECT Max(A.END_DT) FROM tblPlan
AS A WHERE A.Mem_ID = tblPlan.Mem_ID);

If Mem_ID is the primary key of tblMember then qryMem_ID can be replaced
by just the tblMember.Mem_ID values.

The first three lines of the SQL I posted will do something like

SELECT line1 FROM tblPlan if #9/30/05# = #9/30/05#
SELECT line2 FROM tblPlan if #11/30/04# = #9/30/05#
SELECT line3 FROM tblPlan if #3/31/05# = #9/30/05#

because the subquery will return #9/30/05# for Mem_ID = 1

The next two lines will do something like

SELECT line4 FROM tblPlan if #8/31/05# = #12/31/9999#
SELECT line5 FROM tblPlan if #12/31/9999# = #12/31/9999#

because the subquery returns #12/31/9999# when Mem_ID = 2

Therefore the query will return lines 1 and 5 from tblPlan.

The weakness of this SQL is that it has to recalculate the max END_DT
for each group of records with the same Mem_ID even if the Mem_ID hasn't
changed since the last record.

So I am recommending that you try Albert's suggestion after deciding
whether you want the Mem_ID's in tblPlan only (INNER JOIN) or all the
Mem_ID's in tblMember (LEFT JOIN) whether or not there is a value in
tblPlan. Again, the reason for the better efficiency of Albert's
suggestion is that a set of unique Mem_ID values is already sitting in
tblMember so a query doesn't have to calculate that set. OTOH, when the
max date is found once per Mem_ID using Albert's join, the comparison
still has to be made with the dates in tblPlan matching Mem_ID. So it
comes down to finding a max date versus matching a value. I'm confident
that determining the max date from a group of size N is much slower than
checking for a value from a group N times after finding the max date
once for the group.

I'll try to plan further ahead in the future. Let us know if you need
help implementing Albert's idea.

I hope this helps,

James A. Fortune
(e-mail address removed)