SQL Aggregate Field List

I have this query that works  however I have been told I need to add a field called Location. What they want to see is a list of all patients who have not been seen for a certain ApptCode since 07/21/2013. The problem I have with the location field is that it cannot be part of the aggregate group for the MAX function but they still want to see the location as part of the query. Using DM1 ApptCode as an example a patient could have the same code but be seen in different locations. Is there any way in a single pass to adjust this query to filter and group on the dates but still be able list the Location field as part of the query. The location field is part of Appointments.Location.

select t1.PatID,t1.PatName,Max(t1.LastApptDate) as LastApptDate,t1.ApptCode,t1.PatDOB ,t1.Location
from Appointments t1
join Patients t2 on t1.PatID=t2.PatID
where t1.ApptCode in ('RETEX','DM1','RET1','MD1')
group by t1.Account,t1.PatName,t2.HomePhone,t2.MobilePhone,t2.WorkPhone,t1.ApptCode,t1.PatDOB
WHERE MAX(t1.LastApptDate) <= '20130721'
order by t1.PatID

Example data
00001,'Elmer Fudd','20100101','DIM1','02/01/50','Chicago'
00001,'Elmer Fudd','20131231','DIM1','02/01/50','Dallas'
00002,'Bugs Bunny','20100101','DIM1','07/23/56','Chicago'
00002,'Bugs Bunny','20130101','DIM1','07/23/56','Dallas'

In this case, Elmer should not be included in the list because his Dallas appointment occurred after 07/21/2013 but Bugs Bunny would be included in the list because both of his appointments are before 07/21/2013.  If I were to add Location to the Group By then the Elmer visit in Chicago would be included since the date is prior to 07/21/2013.
LVL 1
rwheeler23Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Perhaps the most common method for solving this type of issue is use of row_number()

What I believe you want is "the most recent record" not just the most recent date. With the most recent record you will get both the last seen date as well as "last seen at" location.

| PATID |    PATNAME |     LASTAPPTDATE | APPTCODE |        PATDOB | LASTLOCATION |
|-------|------------|------------------|----------|---------------|--------------|
|     2 | Bugs Bunny | January, 01 2013 |     DIM1 | July, 23 1956 |       Dallas |
		


--who have not been seen for a certain ApptCode since 07/21/2013

SELECT
      PatID
    , PatName
    , LastApptDate
    , ApptCode
    , PatDOB
    , Location AS LastLocation
FROM (
            SELECT
                  t1.PatID
                , t2.PatName
                , t1.LastApptDate
                , t1.ApptCode
                , t2.PatDOB
                , t1.Location
                , ROW_NUMBER() OVER (PARTITION BY t1.PatID ORDER BY t1.LastApptDate DESC) AS rn
            FROM Appointments t1
                  JOIN Patients t2
                              ON t1.PatID = t2.PatID
            WHERE t1.ApptCode IN ('DIM1', 'RETEX', 'DM1', 'RET1', 'MD1')
      ) AS derived
WHERE rn = 1
      AND LastApptDate <= '20130721'
;

Open in new window

+edit, for row_number() see:
http://msdn.microsoft.com/en-us/library/ms186734(v=sql.100).aspx
0
PortletPaulfreelancerCommented:
workings
**MS SQL Server 2008 Schema Setup**:
    CREATE TABLE Appointments 
    	([PatID] int,  [LastApptDate] datetime, [ApptCode] varchar(8), [Location] varchar(11))
    ;
    	
    INSERT INTO Appointments 
    	([PatID], [LastApptDate], [ApptCode], [Location])
    VALUES
    	(00001,  '2010-01-01 00:00:00', 'DIM1', 'Chicago'),
    	(00001,  '2013-12-31 00:00:00', 'DIM1', 'Dallas'),
    	(00002,  '2010-01-01 00:00:00', 'DIM1', 'Chicago'),
    	(00002,  '2013-01-01 00:00:00', 'DIM1', 'Dallas')
    ;
    
    CREATE TABLE Patients 
    	([PatID] int, [PatName] varchar(10), [PatDOB] datetime)
    ;
    	
    INSERT INTO Patients 
    	([PatID], [PatName], [PatDOB])
    VALUES
    	(00001, 'Elmer Fudd', '1950-02-01 00:00:00'),
    	(00002, 'Bugs Bunny', '1956-07-23 00:00:00')
    ;

**Query 1**:

    --who have not been seen for a certain ApptCode since 07/21/2013
    
    SELECT
          PatID
        , PatName
        , LastApptDate
        , ApptCode
        , PatDOB
        , Location AS LastLocation
    FROM (
                SELECT
                      t1.PatID
                    , t2.PatName
                    , t1.LastApptDate
                    , t1.ApptCode
                    , t2.PatDOB
                    , t1.Location
                    , ROW_NUMBER() OVER (PARTITION BY t1.PatID ORDER BY t1.LastApptDate DESC) AS rn
                FROM Appointments t1
                      JOIN Patients t2
                                  ON t1.PatID = t2.PatID
                WHERE t1.ApptCode IN ('DIM1', 'RETEX', 'DM1', 'RET1', 'MD1')
          ) AS derived
    WHERE rn = 1
          AND LastApptDate <= '20130721'
    

**[Results][2]**:
    
    | PATID |    PATNAME |                   LASTAPPTDATE | APPTCODE |                      PATDOB | LASTLOCATION |
    |-------|------------|--------------------------------|----------|-----------------------------|--------------|
    |     2 | Bugs Bunny | January, 01 2013 00:00:00+0000 |     DIM1 | July, 23 1956 00:00:00+0000 |       Dallas |


**Query 2**:

                SELECT
                      t1.PatID
                    , t2.PatName
                    , t1.LastApptDate
                    , t1.ApptCode
                    , t2.PatDOB
                    , t1.Location
                    , ROW_NUMBER() OVER (PARTITION BY t1.PatID ORDER BY t1.LastApptDate DESC) AS rn
                FROM Appointments t1
                      JOIN Patients t2
                                  ON t1.PatID = t2.PatID
                WHERE t1.ApptCode IN ('DIM1', 'RETEX', 'DM1', 'RET1', 'MD1')
    

**[Results][3]**:
    
    | PATID |    PATNAME |                    LASTAPPTDATE | APPTCODE |                          PATDOB | LOCATION | RN |
    |-------|------------|---------------------------------|----------|---------------------------------|----------|----|
    |     1 | Elmer Fudd | December, 31 2013 00:00:00+0000 |     DIM1 | February, 01 1950 00:00:00+0000 |   Dallas |  1 |
    |     1 | Elmer Fudd |  January, 01 2010 00:00:00+0000 |     DIM1 | February, 01 1950 00:00:00+0000 |  Chicago |  2 |
    |     2 | Bugs Bunny |  January, 01 2013 00:00:00+0000 |     DIM1 |     July, 23 1956 00:00:00+0000 |   Dallas |  1 |
    |     2 | Bugs Bunny |  January, 01 2010 00:00:00+0000 |     DIM1 |     July, 23 1956 00:00:00+0000 |  Chicago |  2 |



  [1]: http://sqlfiddle.com/#!3/ada49f/8

Open in new window

0
rwheeler23Author Commented:
I will play around with these. What I am trying to provide is a patient recall list.  There are these four types of appointments: DIM1, RETEX, DM1,RET1, MD1 each with their last date of appointment. They want a list of all patients who last date of visit for each of these appointments is more than one year prior to the current date.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

PortletPaulfreelancerCommented:
use of row_number() will meet this requirement and all you need to add in the logic for "one year prior to the current date"

assuming you need to ignore time of day then:

dateadd(year,-1,dateadd(day, datediff(day,0, getDate()), 0)) -- returns datetime

or

dateadd(year,-1,cast(getdate() as date)) -- returns date
0
rwheeler23Author Commented:
You are correct. However, I just discovered I cannot use row_number. My query runs against a mumps database via an openquery command. When I run this I get a message about User defined SQL function 'SQLUSER.ROW_NUMBER' does not exist. I am going to check to see if I import these two tables into MS SQL and then run this query.
0
PortletPaulfreelancerCommented:
mmm. ok.

there is an alternative, it's not quite as exact as row_number() but for this style of data it should be fine.
SELECT
      t1.PatID
    , t2.PatName
    , t1.LastApptDate
    , t1.ApptCode
    , t2.PatDOB
    , t1.Location AS LastLocation
FROM Appointments t1
      JOIN Patients t2
                  ON t1.PatID = t2.PatID
      JOIN (
                  SELECT
                        PatID
                      , MAX(LastApptDate) AS MaxApptDate
                  FROM Appointments
                  GROUP BY
                        PatID
            ) AS t3
                  ON t1.PatId = t3.PatID
                        AND t1.LastApptDate = t3.MaxApptDate
WHERE t1.ApptCode IN ('DIM1', 'RETEX', 'DM1', 'RET1', 'MD1')
and t3.MaxApptDate <= '20130721'
;

http://sqlfiddle.com/#!3/ada49f/11

Open in new window

0
rwheeler23Author Commented:
Thank you for your help on this. In the morning I will revisit this as it is returning way too few records. I think it has something to do with the fact that the LastApptDate field in the database is actually a string. I will try converting it to a date and then look for a difference that is greater than 365 days when compared to today's date.
0
rwheeler23Author Commented:
I discovered I needed to add the ApptCode to the Group By otherwise for patients that only had multiple codes, I would only see one. I need to see all appointment codes for all patients where for each apt code the last date of visit was over a year ago.

SELECT
      t1.PatID
    , t2.PatName
    , t1.LastApptDate
    , t1.ApptCode
    , t2.PatDOB
    , t1.Location AS LastLocation
FROM Appointments t1
      JOIN Patients t2
                  ON t1.PatID = t2.PatID
      JOIN (
                  SELECT
                        PatID
                      , MAX(LastApptDate) AS MaxApptDate
                  FROM Appointments
                  GROUP BY
                        PatID,ApptCode --  I needed to add this
            ) AS t3
                  ON t1.PatId = t3.PatID
                        AND t1.LastApptDate = t3.MaxApptDate
WHERE t1.ApptCode IN ('DIM1', 'RETEX', 'DM1', 'RET1', 'MD1')
and t3.MaxApptDate <= '20130721'

Open in new window

0
PortletPaulfreelancerCommented:
then you must 'finish' this
SELECT
      t1.PatID
    , t2.PatName
    , t1.LastApptDate
    , t1.ApptCode
    , t2.PatDOB
    , t1.Location AS LastLocation
FROM Appointments t1
      JOIN Patients t2
                  ON t1.PatID = t2.PatID
      JOIN (
                  SELECT
                        PatID, ApptCode                           -- add here also
                      , MAX(LastApptDate) AS MaxApptDate
                  FROM Appointments
                  GROUP BY
                        PatID,ApptCode --  I needed to add this
            ) AS t3
                  ON t1.PatId = t3.PatID
                        AND t1.ApptCode = t3.ApptCode            -- and add this as well
                        AND t1.LastApptDate = t3.MaxApptDate
WHERE t1.ApptCode IN ('DIM1', 'RETEX', 'DM1', 'RET1', 'MD1')
and t3.MaxApptDate <= '20130721'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rwheeler23Author Commented:
Excellent reply! Thank you.
0
PortletPaulfreelancerCommented:
pleasure, good 'get' on needing ApptCode , cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.