Help on selecting current record from one table with additional fields

[ghampton]

I am new to SQL Server (2008 R2) and I am trying to create a view for use with a web app built with Dreamweaver.

Just to provide an example of what I am looking for, I want a listing of all the Clients who visited on 7/14/2009, and their most current address. I have seen some examples of using max, but they were incomplete and when I try to put them together using only two tables I can make it work, but when I try to do multiple tables like below, I keep getting aggregate errors. Here is an example of how I would like to see the list:

Case_ID EnrollDate  Name   Address      VisitDate	
3       7/14/2009   Tom	   412 Seville  7/14/2009
3       7/14/2009   Susan  412 Seville  7/14/2009
1       6/1/2009    Mary   325 Sandy    7/14/2009
1       6/1/2009    Bob	   325 Sandy    7/14/2009
2       6/5/2009    Bill   704 Mallard  7/14/2009

Currently, using a straightforward view, I am getting a complete line for each name for each address, and my inexperience with SQL Server is a bit frustrating. The list that I am creating is a bit larger and has more fields but I am showing these few for simplicity. If I can get it figured out for these, then I should be able to apply it to the remainder.

BTW the reason for the separate addresses table is for a current change to have an address history for each case and for each client.

Table Case
Case_ID EnrollDate
1       6/1/2009
2       6/5/2009
3       7/14/2009
4       7/29/2009

Table Clients
ID  Case_ID Name  
3   1       Mary
4   1       Bob
11  2       Bill
15  3       Tom
16  3       Susan
27  4       Harry

Table Addresses
Addr_ID Case_ID Address
1       1       123 Main
2       2       215 Hillary
3       3       885 Montrose
4       2       704 Mallard
5       1       325 Sandy
6       4       207 Avendia
7       3       412 Seville

Table Visits
V_ID  Case_ID VisitDate	
1     1       6/1/2009
2     2       6/5/2009
3     3       7/14/2009
4     1       7/14/2009
5     4       7/29/2009
6     2       7/14/2009
7     3       7/26/2009

Anticipating your responses. Thanks in advance for your help.

gh

[Rabbit]

I can only assume that the largest address id is equivalent to the most current address as there's nothing to indicate otherwise.

To get the most current address, you would do something like this"

SELECT * 
FROM Table1 AS T1 
INNER JOIN ( 
   SELECT caseID, MAX(addressID) AS addressID
   FROM Table1 
   GROUP BY caseID
) T2 
ON T1.caseID = T2.caseID
   AND T1.addressID= T2.addressID

[ghampton]

Yes, the first field in each of the tables in the example is the record id. And forgive me for blundering, but attached is the SQL file to drop, create and populate the tables for this problem.

Rabbit, I am combining the fields from all four tables. When I make the name corrections and try to add your example, I keep getting errors of one type or another, mostly syntax.

Here is the SQL statement (from a view) I am using and which needs modification to only use the current address, which is the largest Addr_ID (key) for the Case_ID.

SELECT TOP (100) PERCENT dbo.TClients.Case_ID, dbo.TClients.ClientName, dbo.TCase.EnrollDate, dbo.TAddress.Addr_ID, dbo.TAddress.Addr, dbo.TVisits.VisitDate
FROM dbo.TClients INNER JOIN
    dbo.TCase ON dbo.TClients.Case_ID = dbo.TCase.Case_ID INNER JOIN
    dbo.TAddress ON dbo.TClients.Case_ID = dbo.TAddress.Case_ID INNER JOIN
    dbo.TVisits ON dbo.TClients.Case_ID = dbo.TVisits.Case_ID
WHERE (dbo.TVisits.VisitDate = CONVERT(DATETIME, '2009-07-14', 102))
ORDER BY dbo.TClients.ClientName

which produces the following output (Copied from SQL Server output):

Case_ID	ClientName	EnrollDate	Addr_ID	Addr	VisitDate
2	Bill	2009-06-05 00:00:00.000	2	215 Hillary	2009-07-14 00:00:00.000
2	Bill	2009-06-05 00:00:00.000	4	704 Mallard	2009-07-14 00:00:00.000
1	Bob	2009-06-01 00:00:00.000	1	123 Main	2009-07-14 00:00:00.000
1	Bob	2009-06-01 00:00:00.000	5	325 Sandy	2009-07-14 00:00:00.000
1	Mary	2009-06-01 00:00:00.000	1	123 Main	2009-07-14 00:00:00.000
1	Mary	2009-06-01 00:00:00.000	5	325 Sandy	2009-07-14 00:00:00.000
3	Susan	2009-07-14 00:00:00.000	3	885 Montrose	2009-07-14 00:00:00.000
3	Susan	2009-07-14 00:00:00.000	7	412 Seville	2009-07-14 00:00:00.000
3	Tom	2009-07-14 00:00:00.000	3	885 Montrose	2009-07-14 00:00:00.000
3	Tom	2009-07-14 00:00:00.000	7	412 Seville	2009-07-14 00:00:00.000

And with only the current address it should look like this:

Case_ID	ClientName	EnrollDate	Addr_ID	Addr	VisitDate
2	Bill	2009-06-05 00:00:00.000	4	704 Mallard	2009-07-14 00:00:00.000
1	Bob	2009-06-01 00:00:00.000	5	325 Sandy	2009-07-14 00:00:00.000
1	Mary	2009-06-01 00:00:00.000	5	325 Sandy	2009-07-14 00:00:00.000
3	Susan	2009-07-14 00:00:00.000	7	412 Seville	2009-07-14 00:00:00.000
3	Tom	2009-07-14 00:00:00.000	7	412 Seville	2009-07-14 00:00:00.000

I hope I did not ramble too much.

gh

[Rabbit]

You haven't said what your error is. And you haven't posted what your SQL looks like after implementing my suggestion.

[ghampton]

Hey Rabbit, Sorry for being so long replying, multiple projects, fires, pleases, etc.

And sorry for not posting the SQL. Your suggestion worked to a point. But I'm sure that you will probably spot what I'm doing wrong and it's something simple.

Here is the SQL:

SELECT dbo.TClients.Case_ID, dbo.TClients.ClientName, dbo.TCase.EnrollDate, CaseID, Addr_ID, dbo.TVisits.VisitDate
FROM dbo.TClients 
INNER JOIN dbo.TCase ON dbo.TClients.Case_ID = dbo.TCase.Case_ID 
INNER JOIN (SELECT Case_ID as CaseID, MAX(Addr_ID) AS Addr_ID FROM TAddress GROUP BY Case_ID ) TAddress  ON CaseID = dbo.TCase.Case_ID
INNER JOIN dbo.TVisits ON dbo.TVisits.Case_ID = dbo.TCase.Case_ID
WHERE dbo.TVisits.VisitDate = CONVERT(DATETIME, '2009-07-14', 102)
ORDER BY dbo.TClients.ClientName

The above SQL works and Returns:

Case_ID	ClientName	EnrollDate	CaseID	Addr_ID	VisitDate
2	Bill	2009-06-05 00:00:00.000	2	4	2009-07-14 00:00:00.000
1	Bob	2009-06-01 00:00:00.000	1	5	2009-07-14 00:00:00.000
1	Mary	2009-06-01 00:00:00.000	1	5	2009-07-14 00:00:00.000
3	Susan	2009-07-14 00:00:00.000	3	7	2009-07-14 00:00:00.000
3	Tom	2009-07-14 00:00:00.000	3	7	2009-07-14 00:00:00.000

...but it won't let me add the Address field dbo.TAddress.Ad dr . When I try like this (Change underlined):

SELECT dbo.TClients.Case_ID, dbo.TClients.ClientName, dbo.TCase.EnrollDate,[U] TAddr,[/U] CaseID, Addr_ID, dbo.TVisits.VisitDate
FROM dbo.TClients 
INNER JOIN dbo.TCase ON dbo.TClients.Case_ID = dbo.TCase.Case_ID 
INNER JOIN (SELECT Case_ID as CaseID, MAX(Addr_ID) AS Addr_ID[U], Addr AS TAddr[/U] FROM TAddress GROUP BY Case_ID ) TAddress  ON CaseID = dbo.TCase.Case_ID
INNER JOIN dbo.TVisits ON dbo.TVisits.Case_ID = dbo.TCase.Case_ID
WHERE dbo.TVisits.VisitDate = CONVERT(DATETIME, '2009-07-14', 102)
ORDER BY dbo.TClients.ClientName

I get this error:

Msg 8120, Level 16, State 1, Line 1
Column 'TAddress.Addr' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. And just to be sure, this statement is on the same tables created and populated with the above attached file.

Thanks again for your assistance.

gh