Solved

SQL Aggregate Field List

Posted on 2014-07-21
11
247 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 49

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 49

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 49

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
 
LVL 49

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 49

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 49

Expert Comment

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

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

691 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