asked on

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.

PortletPaul

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

PortletPaul

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

rwheeler23

ASKER

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.

PortletPaul

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

rwheeler23

ASKER

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.

PortletPaul

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

rwheeler23

ASKER

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.

rwheeler23

ASKER

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rwheeler23

ASKER

Excellent reply! Thank you.

PortletPaul

pleasure, good 'get' on needing ApptCode , cheers, Paul