Solved

Assistance with SQL MAX function

Posted on 2007-04-03
3
291 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
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

759 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

21 Experts available now in Live!

Get 1:1 Help Now