Home Forums SQL Server 2008 T-SQL (SS2K8) Sql Query - get most recent data and put record numbers into columns RE: Sql Query - get most recent data and put record numbers into columns

  • November 26, 2012 at 8:54 pm

    #1562479

    Guy N (11/26/2012)

    I am not sure exactly how to phrase what I am asking so thanks for looking and I will try to clarify the title if possible.

    Basically, I need to match a patient on:

    FirstName

    LastName

    DateOfBirth

    If there is a match return those fields, last discharge date, and all MR#'s (External Id's) related to the patient. There could be as many as twelve records so I would need to return all 12 ExternalId's.

    Below is some sample data with the desired outcome. Please let me know if more information is needed.

    CREATE TABLE #Readmits(

    ExternalId varchar (30) ,

    DateOfBirth varchar(Max) ,

    Firstname varchar (30) ,

    LastName varchar (30) ,

    DischargeTime datetime

    )

    INSERT INTO #Readmits

    --(ExternalId,DateOfBirth,FirstName,LastName,DischargeTime)

    SELECT 'SHL295834','1955-09-12','JK','Aaron','2011-05-03 20:56:00.000'

    Union all SELECT 'SMC101149','1955-09-12','JK','Aaron','2011-11-07 20:50:00.000'

    Union all SELECT 'SSC101149','1955-09-12','JK','Aaron','2011-12-22 18:00:00.000'

    Select * from #Readmits

    Drop table #Readmits

    Outcome:

    FirstName, LastName, DateOfBirth,DischargeTime(most recent date), ExternalId1, ExternalId2, ExternalId3 (externalId can be in any order)

    OR

    JK,Aaron,1955-09-12,2011-12-22 18:00:00.000,SHL295834,SMC101149,SSC101149

    Thanks in advance for your assistance!!!

    Although it may be well beyond your control, I have to agree with Joe on a potential serious problem. While it may be unlikely that two people could have the same name and birth date, especially within a single establishment, it's not impossible so you could end up with two (or more) people's information in the same output row. Further, it's much more likely that a person could provide their name with more than one spelling. For example, Sam Henderson, Samual Henderson, S. Henderson, Sammy Henderson, etc, etc. Of course, that would cause multiple output rows for the same person.

    So I also have to ask, how is it that the ExternalID changes for a given individual? Is it because the ID's come from different establishments? If so, the name problem is going to get even worse because the chance of two different people with the same name increases dramatically. How many people might have the names of John Smith or Manny Costa?

    I also have to ask... are you sure that you really want duplicated ExternalIDs if they exist? I can't see the need for them but I don't know your situation ore what the output would be used for. It just seems to be an unnecessary complication of a denormalized result set.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)