Solved

Assistance with SQL MAX function

Posted on 2007-04-03
3
296 Views
Last Modified: 2012-05-05
I have posted below the result of the code (MYCODE) I have also posted below.  I am not having success at accomplishing what I really wish to do...pull the MAX EnteredForDate of the grp_code TUBE104 and the MAX Entered_For_Date of the grp_code TUBE102 for each patient.  Since the information I want, comment, is stored in the same field, no matter what the grp_code, the best I have been able to do is pull the last EnteredForDate for either TUBE104 or TUBE102, not both.  I would like to have a result that would look like RESULT1.

RESULT1
Location  VisitId       PtName                        EnteredForDate         Comment
Room1     7505        MOUSE, MICKEY          04/03/2007 8:00        100 ml/hour     --this is the last TUBE104 for MMouse
Room1     7505        MOUSE, MICKEY          04/03/2007 8:00        glucerna         --this is the last TUBE102 for MMouse
Room2     8090        DUCK, DONALD         04/03/2007 12:30        Ensure Plus     --this is the last TUBE102 for Donald Duck
Room2     8090       DUCK, DONALD          04/02/2007 17:00        bolus feed       --this is the last TUBE104 for DDuck

TABLE1
Location      VisitID            PtName                            EnteredForDate                      Comment      grp_code
Room1      7505      MOUSE, MICKEY      04/03/2007 8:00      100 ml/ hour      TUBE104   
Room1      7505      MOUSE, MICKEY      04/03/2007 8:00      glucerna      TUBE102   
Room1      7505      MOUSE, MICKEY      04/03/2007 6:15      100 ml/hr      TUBE104   
Room1      7505      MOUSE, MICKEY      04/03/2007 6:15      1000 ml      TUBE102   
Room1      7505      MOUSE, MICKEY      04/02/2007 14:15      100      TUBE104   
Room1      7505      MOUSE, MICKEY      04/02/2007 14:15      Glucerna      TUBE102   
Room2      8090      DUCK, DONALD      04/03/2007 12:30      Ensure Plus      TUBE102   
Room2      8090      DUCK, DONALD      04/03/2007 9:00      Encure Plus       TUBE102   
Room2      8090      DUCK, DONALD      04/02/2007 17:00      Ensure Plus       TUBE102   
Room2      8090      DUCK, DONALD      04/02/2007 17:00      bolus feed      TUBE104   
Room2      8090      DUCK, DONALD      04/02/2007 12:30      Ensure Plus       TUBE102   
Room2      8090      DUCK, DONALD      04/02/2007 8:30      Ensure Plus       TUBE102   
Room2      8090      DUCK, DONALD      04/01/2007 21:00      300ml bolus      TUBE104   

MY CODE

SELECT     RTRIM(TSM950_LOCATION_REF.loc_ext_id) + '-' + RTRIM(TSM950_LOCATION_REF_1.loc_ext_id) + '-' + RTRIM(TSM950_LOCATION_REF_2.loc_ext_id)
                      AS Location, TPM300_PAT_VISIT.vst_ext_id AS VisitID, RTRIM(TSM040_PERSON_HDR.lst_nm) + ', ' + RTRIM(TSM040_PERSON_HDR.fst_nm)
                      + ' ' + ISNULL(SUBSTRING(TSM040_PERSON_HDR.mid_nm, 1, 1), '') AS PtName, MAX(t_ptdata.entered_for_date) AS EnteredForDate,
                      MAX(t_ptdata.detail_note) AS Comment, t_grpmst.grp_code
FROM         t_ptdata INNER JOIN
                      TPM300_PAT_VISIT ON t_ptdata.vst_int_id = TPM300_PAT_VISIT.vst_int_id INNER JOIN
                      t_grpmst ON t_ptdata.grp_code = t_grpmst.grp_code INNER JOIN
                      t_fndmst ON t_ptdata.find_code = t_fndmst.find_code INNER JOIN
                      t_catmst ON t_ptdata.category_code = t_catmst.cat_code INNER JOIN
                      TSM040_PERSON_HDR ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id INNER JOIN
                      TSM950_LOCATION_REF ON TPM300_PAT_VISIT.loc_lvl_3_id = TSM950_LOCATION_REF.loc_int_id INNER JOIN
                      TSM950_LOCATION_REF AS TSM950_LOCATION_REF_1 ON TPM300_PAT_VISIT.loc_lvl_4_id = TSM950_LOCATION_REF_1.loc_int_id INNER JOIN
                      TSM950_LOCATION_REF AS TSM950_LOCATION_REF_2 ON TPM300_PAT_VISIT.loc_lvl_5_id = TSM950_LOCATION_REF_2.loc_int_id
WHERE     (t_catmst.cat_code = 'TUB101') AND (t_grpmst.grp_code IN ('TUBE102', 'TUBE104')) AND (TPM300_PAT_VISIT.pat_ty IN ('27360', '28483')) AND
                      (TPM300_PAT_VISIT.dschrg_ts IS NULL) AND (t_ptdata.strike_out_flag = '0')
GROUP BY RTRIM(TSM950_LOCATION_REF.loc_ext_id) + '-' + RTRIM(TSM950_LOCATION_REF_1.loc_ext_id)
                      + '-' + RTRIM(TSM950_LOCATION_REF_2.loc_ext_id), RTRIM(TSM040_PERSON_HDR.lst_nm) + ', ' + RTRIM(TSM040_PERSON_HDR.fst_nm)
                      + ' ' + ISNULL(SUBSTRING(TSM040_PERSON_HDR.mid_nm, 1, 1), ''), TPM300_PAT_VISIT.vst_ext_id, t_ptdata.detail_note, t_grpmst.grp_code
ORDER BY TPM300_PAT_VISIT.vst_ext_id, MAX(t_ptdata.entered_for_date) DESC
0
Comment
Question by:PHS_IT
[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
3 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 18846865
I don't have time to detail it out, but here is the form you need:


SELECT T1.Location, T1.Visit, T1.PTName, T1.EnteredForDate, T1.Comment, T1.grp_code
FROM  Table1 AS T1
WHERE T1.EnteredForDate=
   (SELECT Max(T1.EnteredForDate) FROM Table1 T2
      WHERE T2.PatientID = T1.PatientID    --however you Identify a patient
           AND T2.grp_code = T1.grp_code
  )
GROUP BY T1.Grp_code, T1.EnteredForDate, T1.Comment
0
 

Author Comment

by:PHS_IT
ID: 18849911
My problem is that all the data I need isn't stored in the same table.  For instance, Visit, PtName are in one table, grp_code is in another table and enteredfordate and comment are in a third table.  The SQL code is more complex than I can figure out with my limited SQL knowledge.  Do I alias everything I want in T1 as T1 no matter where it originates from?  
0
 
LVL 39

Accepted Solution

by:
appari earned 500 total points
ID: 18862481
try this

with grpData as
(
SELECT     RTRIM(TSM950_LOCATION_REF.loc_ext_id) + '-' + RTRIM(TSM950_LOCATION_REF_1.loc_ext_id) + '-' + RTRIM(TSM950_LOCATION_REF_2.loc_ext_id)
                      AS Location, TPM300_PAT_VISIT.vst_ext_id AS VisitID, RTRIM(TSM040_PERSON_HDR.lst_nm) + ', ' + RTRIM(TSM040_PERSON_HDR.fst_nm)
                      + ' ' + ISNULL(SUBSTRING(TSM040_PERSON_HDR.mid_nm, 1, 1), '') AS PtName, MAX(t_ptdata.entered_for_date) AS EnteredForDate,
                      MAX(t_ptdata.detail_note) AS Comment, t_grpmst.grp_code
FROM         t_ptdata INNER JOIN
                      TPM300_PAT_VISIT ON t_ptdata.vst_int_id = TPM300_PAT_VISIT.vst_int_id INNER JOIN
                      t_grpmst ON t_ptdata.grp_code = t_grpmst.grp_code INNER JOIN
                      t_fndmst ON t_ptdata.find_code = t_fndmst.find_code INNER JOIN
                      t_catmst ON t_ptdata.category_code = t_catmst.cat_code INNER JOIN
                      TSM040_PERSON_HDR ON TPM300_PAT_VISIT.psn_int_id = TSM040_PERSON_HDR.psn_int_id INNER JOIN
                      TSM950_LOCATION_REF ON TPM300_PAT_VISIT.loc_lvl_3_id = TSM950_LOCATION_REF.loc_int_id INNER JOIN
                      TSM950_LOCATION_REF AS TSM950_LOCATION_REF_1 ON TPM300_PAT_VISIT.loc_lvl_4_id = TSM950_LOCATION_REF_1.loc_int_id INNER JOIN
                      TSM950_LOCATION_REF AS TSM950_LOCATION_REF_2 ON TPM300_PAT_VISIT.loc_lvl_5_id = TSM950_LOCATION_REF_2.loc_int_id
WHERE     (t_catmst.cat_code = 'TUB101') AND (t_grpmst.grp_code IN ('TUBE102', 'TUBE104')) AND (TPM300_PAT_VISIT.pat_ty IN ('27360', '28483')) AND
                      (TPM300_PAT_VISIT.dschrg_ts IS NULL) AND (t_ptdata.strike_out_flag = '0')
GROUP BY RTRIM(TSM950_LOCATION_REF.loc_ext_id) + '-' + RTRIM(TSM950_LOCATION_REF_1.loc_ext_id)
                      + '-' + RTRIM(TSM950_LOCATION_REF_2.loc_ext_id), RTRIM(TSM040_PERSON_HDR.lst_nm) + ', ' + RTRIM(TSM040_PERSON_HDR.fst_nm)
                      + ' ' + ISNULL(SUBSTRING(TSM040_PERSON_HDR.mid_nm, 1, 1), ''), TPM300_PAT_VISIT.vst_ext_id, t_ptdata.detail_note, t_grpmst.grp_code
ORDER BY TPM300_PAT_VISIT.vst_ext_id, MAX(t_ptdata.entered_for_date) DESC
 )
select
from grpData gd1 , ( Select
Location, VisitID, PtName, MAX(EnteredForDate) EnteredForDate, grp_code From
grpData
group by Location, VisitID, PtName, grp_code ) gd2
where
gd1.Location = gd2.Location
and gd1.VisitID = gd2.VisitID
and gd1.PtName = gd2.PtName
and gd1.grp_code = gd2.grp_code
and gd1.EnteredForDate = gd2.EnteredForDate
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 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