Display one record for group of records

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

SQL Server 2000 Forums

SQL Server Development (2000)

Display one record for group of records

Author

Topic

nic
Posting Yak Master

209 Posts

Posted - 2003-10-17 : 14:17:40

Hi,
In the past I have posted a few questions about an auto insurance program I have been developing. Anyway it is going well, and we want to expand to home insurance. Essentially we want to have the ability to tie muliple insurances quotes together.

The database is set up so there is a main insurance quote record which has various people associated with it. If an applicant returns at a later date, we have the ability to retrieve the old quote and clone/copy it, which provide a starting point for the new quote (home/auto/renewal). In this case, the Quotes.GroupNumber is the same for the two Quotes. The process copies the old people records (along with all other records) into new ones. Then the agent can make any changes if necessary. The changes will only be for the new quote record (This is important since once the old quote is completed, nothing about it can change) So in time, a person will build up a history of quotes (where the groupNumber is the same for all the quotes). Each quote record will have a set of independant people records (in most instances the data will be the same, but it could change).

We want to have the ability to search for applicants. In the first step, the agent would provide some applicant info (i.e. part of a last name, city etc), which will return a list of all applicants fitting the criteria. The agent picks the desired applicant record and a new page (step 2) showing the quote history for the applicant (all the quotes with a common groupNumber) is displayed.

My question is how can I return the list of applicants in step one where only one applicant record is returned for each group of quotes with the same groupNumber. LastName etc can change so I can't "Group By" all the fields since some can change. I just want to return all the people data for the most recent record for each groupNumber (the data associated the most recent QuoteDate)

Here is my feable attempt. This breaks because I don't have an aggregate expression for Last and First Name.


SELECT
	LastName
	,FirstName
FROM 
	Quotes a INNER JOIN People b ON a.QuoteID = b.QUoteID
	INNER JOIN Addresses c ON b.PeopleID = c.PeopleID
WHERE
	b.LastName Like @LastName
	AND b.FirstName Like @FirstName
	AND c.City Like @City
GROUP BY
	a.GroupNumber


CREATE TABLE Quotes (
	QuotetID int,
	GroupNumber int,
	QuoteType varchar (10),
	QuoteDate datetime
)
GO

CREATE TABLE People (
	PeopleID int
	QuoteID int,
	PeopleType varchar(10),
	LastName varchar (50),
	FirstName varchar(50),
)
GO

CREATE TABLE Addresses (
	AddressID int
	PeopleID int,
	AddressType varchar(10),
	City varchar (50),
	State varchar(2),
)
GO

Once again thanks for all your help.

Nic

nic
Posting Yak Master

209 Posts

Posted - 2003-10-17 : 16:29:59

I think I got it. This returns the max(quoteID) as the unique record. I was hoping to get the max(QuoteDate) but this is pretty close (it will need refinement but the basics are here):

Thanks for anyone who read this. Sometimes the act of posting a question gives me ideas.


SELECT 
	b.QuoteID
	,b.GroupNumber
	,c.LastName
	,c.FirstName
	,c.MiddleName
	,c.Suffix
	,d.AddressType
	,d.Address1
	,d.Address2
	,d.City
	,d.State
	,d.Zip
FROM 
(
	SELECT 
		MAX(a.QuoteID) AS QuoteID
		,a.GroupNumber
	FROM
		Quotes a INNER JOIN People b ON a.QuoteID = b.QuoteID
                INNER JOIN Addresses c ON b.PeopleID = c.PeopleID
	WHERE
	        b.LastName Like @LastName
	        AND b.FirstName Like @FirstName
	        AND c.City Like @City
	GROUP BY
		a.GroupNumber	

) AS Dt INNER JOIN Quotes b ON DT.QuoteID = b.QuoteID
INNER JOIN People c ON b.QuoteID = c.QuoteID  
INNER JOIN Addresses d ON c.PeopleID = d.PeopleID

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-10-17 : 16:51:44

Hope it will work too:

select LastName, FirstName
from t where
QuoteDate =
(select max(QuoteDate) from t tt where tt.GroupNumber=t.GroupNumber)

Here t is your base query:


SELECT
       GroupNumber
        ,QuoteDate
	,LastName
	  ,FirstName
FROM
	Quotes a INNER JOIN People b ON a.QuoteID = b.QUoteID
	    INNER JOIN Addresses c ON b.PeopleID = c.PeopleID
WHERE
	b.LastName Like @LastName
	  AND b.FirstName Like @FirstName
	    AND c.City Like @City

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources