Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Sql Query - get most recent data and put record numbers into columns Expand / Collapse
Author
Message
Posted Monday, November 26, 2012 4:15 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:38 AM
Points: 16, 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!!!
Post #1388891
Posted Monday, November 26, 2012 5:05 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, April 17, 2015 2:35 PM
Points: 887, Visits: 1,781
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

Jeremy Oursler
Post #1388899
Posted Monday, November 26, 2012 8:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:22 PM
Points: 37,364, Visits: 34,245
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388915
Posted Monday, November 26, 2012 8:54 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:22 PM
Points: 37,364, Visits: 34,245
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1388918
Posted Tuesday, November 27, 2012 8:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 4:59 PM
Points: 21,492, Visits: 34,001
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?


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.



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)
Post #1389217
Posted Tuesday, November 27, 2012 8:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, September 29, 2014 11:38 AM
Points: 16, 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.
Post #1389486
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse