locked
Table Update Problem RRS feed

  • Question

  • User1122355199 posted

    Hello everyone and thanks for your help in advance.  I need to build an update query based on two tables.  One is a master table tblPTMstr holding a master list of patients.  The second table, tblPatientVisits, holds a list of patient visits, so the relationship between tables is one to none, one to one, or one to many.  Here are some snippets of the table structures:

    Select PatientID, DateLastSvc from tblPTMstr where PatientID = '5732'
    
    MRNumber   DateLastSvc
    ---------- -----------------------
    5732       NULL
    

    and

    ID          MRNumber    FirstName                                          LastName                                           CheckedIn               CheckedOut
    ----------- ----------- -------------------------------------------------- -------------------------------------------------- ----------------------- -----------------------
    143429      5732        TOM                                               TESTCASE                                         2011-11-10 16:37:58.890 2011-11-10 16:58:12.203
    126965      5732        TOM                                               TESTCASE                                         2010-05-02 10:16:06.560 2010-05-02 10:21:53.377
    170312      5732        TOM                                               TESTCASE                                         2014-08-19 09:11:17.657 NULL
    127074      5732        TOM                                               TESTCASE                                         2010-05-04 13:26:54.607 NULL

    I need to update DateLastSvc for each row of tblPTMstr with the last visit date returned from tblPatientVisits.  The query for tblPatientVisits for each particular patient would be:

    select TOP 1 CheckedOut from tblPatientVisits where MRNumber = '5732' Order By CheckedOut Desc

    This value would then be used to update the corresponding MRNumber DateLastSvc. I've played around with several ideas including using cursors without anything close to working.  Any help would be appreciated.

    Saturday, April 16, 2016 3:18 PM

Answers

  • User77042963 posted
    create table tblPTMstr(MRNumber int, PatientID int, DateLastSvc datetime)
     Insert into tblPTMstr values(5732, 5732, null)
    
     create table tblPatientVisits(ID int identity(1,1), MRNumber int, Firstname varchar(50),LastName varchar(50), CheckedOut  datetime)
    Insert into tblPatientVisits values(5732, 'Tom','Test','2016-04-18 09:04:39.813' )
    Insert into tblPatientVisits values(5732, 'Tom','Test','2016-03-18 09:04:39.813' )
    Insert into tblPatientVisits values(5732, 'Tom','Test','2016-02-18 09:04:39.813' )
    Insert into tblPatientVisits values(5732, 'Tom','Test','2016-01-18 09:04:39.813' )
    
    
    --select *  from tblPTMstr
    Merge   tblPTMstr tgt
    Using (select MRNumber, Max(CheckedOut) CheckedOut from tblPatientVisits Group by MRNumber) src 
    on src.MRNumber=tgt.MRNumber
    
    When matched then 
    Update
    Set DateLastSvc=src.CheckedOut;
    
    
    --Select * from tblPatientVisits
    --select * from tblPTMstr
    
     drop table tblPTMstr, tblPatientVisits
    
    
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 18, 2016 2:10 PM

All replies

  • User958441766 posted

    The easiest way to get this done, probably could be creating a trigger on table tblPatientVisits.

    The update statement can be something like-

    UPDATE P SET DateLastSvc=MAX(CheckedOut)
    
    FROM tblPtMst P
    
    LEFT JOIN tblPatirntVisits PV ON PV.MRNumber=P.MRNumber
    
    WHERE P.MRNumber='5732'

    Or, using subquery like

    UPDATE tblPtMst SET DateLastSvc=(SELECT MAX(CheckedOut) FROM tblPatirntVisits WHERE MRNumber='5732')

    Hope, these help.

    In case your requirement is something else, please let me know.

    Sunday, April 17, 2016 7:52 AM
  • User1122355199 posted

    Thanks for the response.  Yes, that will take care of queries going forward, but I need to execute a one time update to load the initial data.  I'm almost think of rolling a quick vb.net help function, but this should be able to get done in SQL.

    Sunday, April 17, 2016 2:38 PM
  • User77042963 posted
    create table tblPTMstr(MRNumber int, PatientID int, DateLastSvc datetime)
     Insert into tblPTMstr values(5732, 5732, null)
    
     create table tblPatientVisits(ID int identity(1,1), MRNumber int, Firstname varchar(50),LastName varchar(50), CheckedOut  datetime)
    Insert into tblPatientVisits values(5732, 'Tom','Test','2016-04-18 09:04:39.813' )
    Insert into tblPatientVisits values(5732, 'Tom','Test','2016-03-18 09:04:39.813' )
    Insert into tblPatientVisits values(5732, 'Tom','Test','2016-02-18 09:04:39.813' )
    Insert into tblPatientVisits values(5732, 'Tom','Test','2016-01-18 09:04:39.813' )
    
    
    --select *  from tblPTMstr
    Merge   tblPTMstr tgt
    Using (select MRNumber, Max(CheckedOut) CheckedOut from tblPatientVisits Group by MRNumber) src 
    on src.MRNumber=tgt.MRNumber
    
    When matched then 
    Update
    Set DateLastSvc=src.CheckedOut;
    
    
    --Select * from tblPatientVisits
    --select * from tblPTMstr
    
     drop table tblPTMstr, tblPatientVisits
    
    
     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 18, 2016 2:10 PM
  • User1122355199 posted

    Sorry for the delay in responding as I wanted to read up on the Merge statement and understand how it worked.  One other question.  Why the query:

    select MRNumber, Max(CheckedOut) CheckedOut from tblPatientVisits Group by MRNumber

    Instead of :

    select  Top 1 CheckedOut from tblPatientVisits Group by CheckedOut desc

    More efficient and why?

    Thanks for the response.  It works perfectly.

    Saturday, April 23, 2016 12:01 AM
  • User77042963 posted

    When you have multiple MRNumbers, you will have max checkedout value for each MRNumber. But the SELECT top 1 returns only one value for all MRNumbers.

    Monday, April 25, 2016 1:46 PM