Nested Grouping Problem

[Lots_of_Queries]

Hi,
I'm still a relative SQL newbie so apologies if this is mundane.

I'm having trouble filtering records using results from aggregate functions.

Specifically I'm trying to find the current names for my customers.

My table is:
seq_n cust_id name effective_dt
1 1 Robert 1970
2 2 Elizabeth 2000
3 1 Bob 1990
4 1 Rob 1990
5 2 Beth 1972

Seq_n is the name sequence number/primary key
cust_id is the id for each customer
name is the possible names
effective_dt is the date from which a given name becomes effective.

For each cust_id I want to find the most recent effective date, then from
this group use the name with the highest seq_n.
i.e.
cust_id name
1 Rob
2 Elizabeth

I'm not sure the best way to acheive this with SQL & would appreciate your
suggestions.

I have been able to do this using a very messy function with several dmax
calls in a 'where' clause but suspect there is a better way using SQL. I'd
also rather not use VBA if possible.

Thanks for your help!

 

[John Spencer]

One method with a coordinated subquery.

SELECT Seq_n, [Cust_id], [Name], Effective_dt
FROM YourTableName as Y1
WHERE Effective_dt =
(SELECT Max(Y2.Effective_dt)
FROM YourTableName as Y2
WHERE Y2.Cust_Id = Y1.CustID)

Two query method
QueryOne: Save as qLastEffDate
SELECT CustID, Max(Effective_dt) as Latest
FROM YourTable

QueryTwo
Select YourTableName.*
FROM YourTableName INNER JOIN qLastEffDate
On YourTableName.CustID = qLastEffDate.CustID AND
YourTableName.Effective_dt = qLastEffDate.Latest

 

[Lots_of_Queries]

Fantastic, a coordinated subquery was exactly what I was looking for. Thanks!

btw do you know which way would run more efficiently - the subquery or the
JOIN??


To pick the highest seq_n within a cust_id > effective_dt grouping I used:

SELECT Y1.seq_n, Y1.cust_id, Y1.name, Y1.effective_dt
FROM YourTableName AS Y1
WHERE
(y1.effective_dt=(SELECT Max(Y2.Effective_dt)
FROM YourTableName as Y2
WHERE Y2.Cust_Id = Y1.Cust_ID))
AND
(y1.seq_n=(SELECT Max(seq_n)
from YourTableName as y3
where (y3.effective_dt=y1.effective_dt)));

as you suggested.

This has been a recurrent problem I've had, often with tables with many more
fields that need grouping. I think I can do even further filtering/grouping
by adding more AND... clauses to the WHERE in a similar way to above.

Many thanks!

One method with a coordinated subquery.

SELECT Seq_n, [Cust_id], [Name], Effective_dt
FROM YourTableName as Y1
WHERE Effective_dt =
(SELECT Max(Y2.Effective_dt)
FROM YourTableName as Y2
WHERE Y2.Cust_Id = Y1.CustID)

Two query method
QueryOne: Save as qLastEffDate
SELECT CustID, Max(Effective_dt) as Latest
FROM YourTable

QueryTwo
Select YourTableName.*
FROM YourTableName INNER JOIN qLastEffDate
On YourTableName.CustID = qLastEffDate.CustID AND
YourTableName.Effective_dt = qLastEffDate.Latest

Hi,
I'm still a relative SQL newbie so apologies if this is mundane.

I'm having trouble filtering records using results from aggregate
functions.

Specifically I'm trying to find the current names for my customers.

My table is:
seq_n cust_id name effective_dt
1 1 Robert 1970
2 2 Elizabeth 2000
3 1 Bob 1990
4 1 Rob 1990
5 2 Beth 1972

Seq_n is the name sequence number/primary key
cust_id is the id for each customer
name is the possible names
effective_dt is the date from which a given name becomes effective.

For each cust_id I want to find the most recent effective date, then from
this group use the name with the highest seq_n.
i.e.
cust_id name
1 Rob
2 Elizabeth

I'm not sure the best way to acheive this with SQL & would appreciate your
suggestions.

I have been able to do this using a very messy function with several dmax
calls in a 'where' clause but suspect there is a better way using SQL. I'd
also rather not use VBA if possible.

Thanks for your help!