Sql Query - get most recent data and put record numbers into columns

Question

Sql Query - get most recent data and put record numbers into columns

Guy N

SSC Enthusiast

Points: 184
More actions
November 26, 2012 at 4:15 pm

#265247

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!!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

CapnHector SSCoach Points: 16143 More actions · Answer 1

Great sample data. i have a question, how do you know there will only be 12 ExternalID's? is that a business rule. how will you deal with patients who have more than 12 ExternalID's if there are more?

as far as the actual query goes take a look at jeff moden's cross tabs and pivot article http://www.sqlservercentral.com/articles/T-SQL/63681/

EDIT: Grabbed the wrong link, fixed.

For faster help in answering any problems Please read How to post data/code on a forum to get the best help - Jeff Moden[/url] for the best way to ask your question.

For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]

Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

CELKO (11/26/2012)
You might also want to learn the ANSI row constructor syntax for INSERT INTO:
INSERT INTO Readmits
(mr_nbr, birth_date, first_name, last_name, discharge_timestamp)
VALUES
('SHL295834', '1955-09-12', 'JK', 'Aaron', '2011-05-03 20:56:00'),
('SMC101149', '1955-09-12', 'JK', 'Aaron', '2011-11-07 20:50:00'),
('SSC101149', '1955-09-12', 'JK', 'Aaron', '2011-12-22 18:00:00');

Or not. It's not portable to versions/engines that don't follow that particular ANSI standard which would also discourage people using an older version of SQL Server from helping. 😉

--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)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 3

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)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

CELKO (11/27/2012)
Or not. It's not portable to versions/engines that don't follow that particular ANSI standard which would also discourage people using an older version of SQL Server from helping. 😉
Or is might encourage them to upgrade to a version of SQL Server 2005 or later. Do you want to keep using *= and a compatibility setting for that same reason? :crazy:

One problem. this:

INSERT INTO Readmits

(mr_nbr, birth_date, first_name, last_name, discharge_timestamp)

VALUES

('SHL295834', '1955-09-12', 'JK', 'Aaron', '2011-05-03 20:56:00'),

('SMC101149', '1955-09-12', 'JK', 'Aaron', '2011-11-07 20:50:00'),

('SSC101149', '1955-09-12', 'JK', 'Aaron', '2011-12-22 18:00:00');

Doesn't work in SQL Server 2005. Also, upgrading for some may not be an option. Sometimes you are held hostage to what vendors support.

Guy N SSC Enthusiast Points: 184 More actions · Answer 5

But there is no excuse

CELKO, Sorry the method in which I supplied the data didn't meet your high standards. I will tell you what I think there is no excuse for...your high and mighty attitude and your need to belittle someone who is asking for help.

If you are unhappy with how someone request assitance, how about just not responding rather than attempting to show your superiority.

Thanks for not replying any further to my request.