Solved

SQL Aggregate Field List

Posted on 2014-07-21
11
231 Views
Last Modified: 2014-07-25
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.
0
Comment
Question by:rwheeler23
  • 6
  • 5
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40210372
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40210380
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
 

Author Comment

by:rwheeler23
ID: 40210576
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
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40210582
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
 

Author Comment

by:rwheeler23
ID: 40210606
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 40210625
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
 

Author Comment

by:rwheeler23
ID: 40210684
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
 

Author Comment

by:rwheeler23
ID: 40211335
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 40211419
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
 

Author Closing Comment

by:rwheeler23
ID: 40220809
Excellent reply! Thank you.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40220892
pleasure, good 'get' on needing ApptCode , cheers, Paul
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now