SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
Guy N
Guy N
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 95
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!!!
CapnHector
CapnHector
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3081 Visits: 1789
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 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

Need to Split some strings? Jeff Moden's DelimitedSplit8K
Jeff Moden's Cross tab and Pivots Part 1
Jeff Moden's Cross tab and Pivots Part 2
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205285 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)SSC Guru (205K reputation)

Group: General Forum Members
Points: 205285 Visits: 41952
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Lynn Pettis
Lynn Pettis
SSC Guru
SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)SSC Guru (90K reputation)

Group: General Forum Members
Points: 90694 Visits: 38945
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.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Guy N
Guy N
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 95
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search