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: 2 days ago @ 10:16 AM
Points: 16, Visits: 82
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: Thursday, March 27, 2014 5:29 AM
Points: 945, Visits: 1,760
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 5:34 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782

Basically, I need to match a patient on:
first_name
last_name
birth_date

If there is a match return those fields [sic: column are not fields], last discharge date, and all MR#'s (External Id's) related to the patient. There could be as many as twelve records [sic:rows are not records] so I would need to return all 12 external_id's.


What you posted is not a table; it has no key and not way to have a key. If the name of the data element is the very specific “MR #” (whatever that is), why do your re-name it to the vague “external_id” in this table? (do you change your name from room to room?) Why did violate 1NF by using strings for temporal data? Why did you fail to follow USPS standards for names? Here is my guess at a clean-up and corrections:

CREATE TABLE Readmits
(mr_nbr CHAR(9) NOT NULL PRIMARY KEY
CHECK (mr_nbr LIKE 'S[A-Z][A-Z][0-9][0-9][0-9][0-9][0-9][0-9]'),
birth_date DATE NOT NULL,
first_name VARCHAR (20) NOT NULL,
last_name VARCHAR (20) NOT NULL,
discharge_timestamp DATETIME2(0)
DEFAULT CURRENT_TIMESTAMP NOT NULL);

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

What you want to do it destroy First Normal Form (1NF). A table has a fixed number of columns by definition. Records can be variant, look at the OCCURS keyword in COBOL. This one of many reason that learning the right concepts is important.

We can easily do this:

SELECT last_name, first_name, birth_date,
MIN(mr_nbr) AS lowest_mr_nbr,
MAX(mr_nbr) AS highest_mr_nbr,
COUNT(*) AS mr_cnt,
MAX(discharge_timestamp) AS last_discharge_timestamp
FROM Readmits
GROUP BY last_name, first_name, birth_date


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1388903
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 @ 11:47 PM
Points: 35,959, Visits: 30,252
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." -- 04 August 2013
(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 @ 11:47 PM
Points: 35,959, Visits: 30,252
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." -- 04 August 2013
(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:04 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
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?


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1389215
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 @ 8:55 PM
Points: 22,492, Visits: 30,199
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 6:35 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, January 15, 2013 11:11 AM
Points: 1,945, Visits: 2,782
Sometimes you are held hostage to what vendors support.


All too true. But there is no excuse for the "SELECT <single row constructor> UNION ALL .." instead of "INSERT INTO ..VALUES (<single row constructor>)" in the code. I cannot remember why Sybase did that back in the 1970's.


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1389480
Posted Tuesday, November 27, 2012 8:07 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 10:16 AM
Points: 16, Visits: 82
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