?
Solved

SQL statement eo only select latest records

Posted on 2007-08-11
6
Medium Priority
?
552 Views
Last Modified: 2013-11-30
Microsoft Sequel Server 2000
Please help I am very new to sql I only need it for this project.
I have a database with two tables that I need to innerjoin.
Table A has 10 columns with the first column ChartNumber which represents each patient medical record number as the primary key.
Table B has several columns with CaseNumber as the primary key, where a case number represents insurance company bills, so that each patient may have several insurance bills, thus multiple rows for chartnumber, other columns are date, etc
How do I write a sequel statement, so that I can create a table that has the same columns as table B which only selects one row for each  chartNumber per record based on the latest date, and discard other rows in table B.
If I join the tables now I get an extremely large table with > 4,000,000 rows. If I am able to create this table it will cut down to about 10,000 records. Below is an illustration which may better explain my question:


Table A
ChartNumber          LastName          First Name
JD0          Doe            John
SC         Clause            Santa


Table B
CaseNumber            ChartNumber       Date
1             JD0                            01/01/07
2             JD0                            02/02/07
3            JDO                            03/02/07
4             SC                            01/09/07
5            SC                            02/09/07
6             SC                            03/15/07


                            Sequel statement

Table C
CaseNumber          ChartNumber      Date
3           LD0                            03/02/07
6            Sc                           03/15/07



Thank You
0
Comment
Question by:adkawley
  • 2
4 Comments
 
LVL 39

Accepted Solution

by:
appari earned 2000 total points
ID: 19675839
try like this

Select B.* from
tableA A join tableB B on
A.ChartNumber = B.ChartNumber
join ( Select ChartNumber, Max(Date) MAXDATE from tableB Group By ChartNumber) MaxChart
on MaxChart.ChartNumber = B.ChartNumber
and B.Date = MAXDATE
0
 
LVL 28

Expert Comment

by:imran_fast
ID: 19675842

select a.lastname, a.firstname, b.casenumber, b.chartnumber , b.date from
tablea as a
inner join (select chartnumber, max(date) date from tableb group by chartnumber) bb
on a.chartnumber = bb.chartnumber
inner join tableb b
on b.chartnumber =a.chartnumber
and b.date = bb.date
0
 

Author Comment

by:adkawley
ID: 19676455
APPARI
Your method worked. Could you please show me how i can insert the data from the join into a table in my data base which I may schedule as a dts package that updates regularly
0
 
LVL 39

Expert Comment

by:appari
ID: 19677834
try like this

insert into tableC(CaseNumber         , ChartNumber      , Date
)
Select B.CaseNumber         , B.ChartNumber      , B.Date from
tableA A join tableB B on
A.ChartNumber = B.ChartNumber
join ( Select ChartNumber, Max(Date) MAXDATE from tableB Group By ChartNumber) MaxChart
on MaxChart.ChartNumber = B.ChartNumber
and B.Date = MAXDATE

0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever visit a website where you spotted a really cool looking Font, yet couldn't figure out which font family it belonged to, or how to get a copy of it for your own use? This article explains the process of doing exactly that, as well as showing how…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question