I am running the following query and need to modify it to pull only 1 row, with the most recent Onset Date, when the pr.diagnosis_code_id and pr.description are not unique. I could actually use one or the other, since the code always maps to the same description.
Currently, my query outputs:
Acute cholecystitis, 575.0, 06/29/2010, --, --
Acute pancreatitis, 577.0, 06/08/2010, --, --
Acute posthemorrhagic anemia, 285.1, 06/29/2010, --, --
Bipolar Disoder, 296.80, --, --, 06/29/2010
Bipolar Disoder, 296.80, 06/08/2010, --, 06/29/2010
ETOH Dependence, 303.91, 06/08/2010, --, --
And I want it to output the following (removing the Bipolar Disoder where there the Onset date is the least current), but I don't know how to do a "distinct" on the pr.description or pr.diagnosis_code_id:
Acute cholecystitis, 575.0, 06/29/2010, --, --
Acute pancreatitis, 577.0, 06/08/2010, --, --
Acute posthemorrhagic anemia, 285.1, 06/29/2010, --, --
Bipolar Disoder, 296.80, 06/08/2010, --, 06/29/2010
ETOH Dependence, 303.91, 06/08/2010, --, --
Here is my query:
select
pr.diagnosis_code_id,
pr.description,
pr.date_onset_sympt as dt1,
pr.date_diagnosed as dt2,
pr.date_resolved as dt3,
pe.enc_timestamp as dt4
from patient p,
patient_diagnosis pr,
patient_encounter pe
where p.med_rec_nbr = $mrn
and pr.person_id = p.person_id
and pr.practice_id = $practice_id
and pr.enterprise_id = $enterprise_id
and pe.enc_id = pr.enc_id
order by pr.description, pe.enc_timestamp DESC
Thanks! I am new to this and really struggle once I get out of the simple select stuff!
3 2160
Hi Ginny28
Here is some example code for tackling your problem.
This is what I use as a starting point whenever I am working on the type of problem you are describing.
Hope it helps!
Kind regards
Brad
declare @TestData Table
(
RecordId INT IDENTITY(1,1),
STUDENT_ID VARCHAR(6),
TF Decimal(2,1)
)
insert into @TestData (student_id, tf)
select 'abc123', 0.2 UNION
select 'abc123', 0.7 UNION
select 'abc123', 0.4 UNION
select 'def123', 0.1 UNION
select 'def123', 0.5 UNION
select 'def123', 0.4
select * from @testdata
select * from
@TestData Data
where recordid in
(
select top 1 recordid
from @TestData MyData
where mydata.STUDENT_ID = data.STUDENT_ID
order by TF desc
)
Thanks for the info. I still struggled because it appears Top 1 only returns one row. I ended up with the following and I think it is what I need. Unless you can think of a better way:
select DISTINCT date_onset_sympt as dt1, description
from patient_diagnosis
where date_onset_sympt = (select max(pr.date_onset_sympt)
from patient p,
patient_diagnosis pr
where pr.description = patient_diagnosis.description
and p.med_rec_nbr = $mrn
and pr.person_id = p.person_id
and pr.practice_id = $practice_id
and pr.enterprise_id = $enterprise_id)
Modifying Brad's code... -
-
declare @TestData Table
-
(
-
STUDENT_ID VARCHAR(6),
-
TF Decimal(2,1)
-
)
-
-
insert into @TestData (student_id, tf)
-
select 'abc123', 0.2 UNION
-
select 'abc123', 0.7 UNION
-
select 'abc123', 0.4 UNION
-
select 'def123', 0.1 UNION
-
select 'def123', 0.5 UNION
-
select 'def123', 0.4
-
-
select * from @testdata
-
-
;with maxvalue
-
as
-
(
-
select student_id, max(tf) as tf
-
from @TestData MyData
-
group by student_id
-
)
-
select
-
t.*
-
from @TestData t
-
inner join maxvalue m on m.student_id = t.student_id and m.tf = t.tf
-
-
One catch: Watch out for duplicates...
Happy Coding!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: HandersonVA |
last post by:
There are several day_timestamp for each index_id.
Anyone can help me to write a sql to generate the most recent
day_timestamp of index_ids which has not accessed into the system in
90 days from...
|
by: |
last post by:
I have a collumn filled with dates. How would I go about selecting the record with the most recent date.
using access 2000.
thanks!
|
by: William Wisnieski |
last post by:
Hello Everyone,
I'm helping out a non-profit school with their database. They would like to
know the last gift made by each donor, the donor name, and the gift amount.
I built a query based...
|
by: Jeroen |
last post by:
Hello,
Is it possible to retrieve the date when a record is created in an Access
database table?
And if so, how can I do that (by code)?
Thanks in advance,
Jeroen Elias
|
by: Jeroen Elias |
last post by:
Hello,
Is it possible to retrieve the date when a record is created in an Access
database table?
And if so, how can I do that?
Thanks in advance,
Jeroen Elias
|
by: gafchic |
last post by:
I manage a training database where I work and I would like to run a make table query. The table I want to query has a list of trainings our employees have taken and the dates they have taken the...
|
by: Jason H |
last post by:
Hi,
I am sure I am just overlooking the obvious, but I am having a little trouble with this one...
I am setting up an inventory database that tracks company tools and their location. This...
|
by: Proaccesspro |
last post by:
I have 2 tables in a query. One of the tables contains a field titled Action Date. How can I query for the most recent date in the field Action Date?
Dmax?
|
by: giandeo |
last post by:
Hello Experts.
I am unable to retrieve records base on a date. Could you please help me.
Here is my code for user to insert a date :
<form name="hello" method="post"...
|
by: Steve |
last post by:
I have a databse tracking container movements. I am happily recording
each movement and the date of movement and can see where each
container is by sorting by date of movement. Is there a way I...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |