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
4 2564
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
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
You haven't said what your error is. And you haven't posted what your SQL looks like after implementing my suggestion.
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.Addr . When I try like this (Change underlined): -
SELECT dbo.TClients.Case_ID, dbo.TClients.ClientName, dbo.TCase.EnrollDate, TAddr, 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, Addr AS TAddr 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
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Tony |
last post by:
Hello,
I am having difficulty in getting the current record of the current
form to show after pressing a command button that takes me to another
form. The command button takes me to another...
|
by: Anderson |
last post by:
Can you please help?
I have an appointment table and employees have multiple records in this
table simple because their contracts change. The table has fields change
date and reason for change...
|
by: Rosy |
last post by:
I am attempting to use the following code to print a report based on
the current record in the form. Users bring up the record with a
parameter box and then can make changes to the sub-form on the...
|
by: Alien Clone |
last post by:
I am using the following code within an Access database to retrieve data from Oracle and build a local table within the Access database.
DoCmd.RunSQL "Select Field1, Field2, " _
& "'' as...
|
by: anansi |
last post by:
Hi
I have a form called 'shiftviewer' and it contains a subform called 'all shifts for current month subform'. the subform is in datasheet view (access 2007) and the main form is a column...
|
by: Kosmos |
last post by:
Hey ya'll...I can't seem to figure out why I'm getting this error message, but it all started when I added the new line of code with the recSet5.AddNew --- when I ran the first line, the logic worked...
|
by: KevinC |
last post by:
Hi All,
I have two tables: tblLicensedPrem and tblLicensedPremHistory (these tables are identical).
tblLicensedPrem contains records for licensed premises. Over time details of a licensed...
|
by: markla |
last post by:
Hi,
I have an EDM model, which I'm rendering using DynamicData pages.
I need to add a derived/calculated field.
For example, in a "Person" entity, I have LastName and FirstName. I need to...
|
by: JonHuff |
last post by:
I have a form in which users can create new records or modify existing records. All records are stored in one table. I want to be able to keep track of all changes made to existing records as well...
|
by: Jeff Karli |
last post by:
I think I now understand how to use this forum. I hope I am following the rules now. I am a college student working on a database project that requires some VBA coding for the Event Procedures and...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |