Assistance with SQL MAX function

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
PHS_ITAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
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
PHS_ITAuthor Commented:
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
appariCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.