how to add most recent date (timestamp) to a query without causing aggregate errors

I have a query that works great up to this point, but from the patient_encounter table there is also an enc_timestamp and I need to only return the most recent per person_id and each way I try just gives me errors or doesn't return the actual most recent. I tried doing a row_number()  but it returned enc_timestamps that were not dates for each patient, they were random. That query is shown second.

select distinct p.person_id, p.last_name as "patient last_name", p.first_name as "patient first name", pm.last_name as "provider last name", pm.first_name
as "provider first name", pr.payer_name
from person p
inner join patient_encounter pe on p.person_id = pe.person_id
INNER JOIN appointments AS a with(nolock)  ON pe.enc_id	= a.enc_id
left join provider_mstr pm on p.primarycare_prov_id = pm.provider_id
left join payer_mstr pr on p.uds_primary_med_coverage_id = pr.payer_id
where (a.appt_date > '20151231' and a.appt_date < '20180101'
and a.appt_kept_ind = 'Y' and a.resched_ind = 'N' and a.delete_ind  = 'N' and clinical_ind = 'Y' and a.location_id <> 'EFAF5A81-18CF-426C-A985-863C7B5D16D0')
and p.last_name <> 'zzzztest'

Open in new window


With row_number () Over
select  * from (select distinct p.person_id, p.last_name as "patient last_name", p.first_name as "patient first name", pm.last_name as "provider last name", pm.first_name
as "provider first name", pr.payer_name, pe.enc_timestamp,
ROW_NUMBER() OVER (PARTITION BY p.person_id ORDER BY pe.enc_timestamp DESC)rowno
 from person p
inner join patient_encounter pe on p.person_id = pe.person_id
INNER JOIN appointments AS a	 with(nolock)  ON pe.enc_id	= a.enc_id
left join provider_mstr pm on p.primarycare_prov_id = pm.provider_id
left join payer_mstr pr on p.uds_primary_med_coverage_id = pr.payer_id
where (a.appt_date > '20151231' and a.appt_date < '20180101'
and a.appt_kept_ind = 'Y' and a.resched_ind = 'N' and a.delete_ind  = 'N' and clinical_ind = 'Y' and a.location_id <> 'EFAF5A81-18CF-426C-A985-863C7B5D16D0')
and p.last_name <> 'zzzztest')test
where rowno = 1

Open in new window

sample-pt-payer-and-pcp-report.xlsx
biotecAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Nitin SontakkeDeveloperCommented:
Much nicer had you mentioned which record you would expect to have returned.
0
Nitin SontakkeDeveloperCommented:
All the person ids is provided data are unique. I am not sure if you are expecting single record out of data provided or several (for which data is not provided).

Assuming you want single record, please have a look at the attached. If you want several records, please provide appropriate data.
29076035.sql
0
_agx_Commented:
I tried doing a row_number()  but it returned enc_timestamps that were not dates for each patient, they were random.
1. Please provide an example of the base data for a single person_id and the expected vs. actual results
2. What is the data type of "enc_timestamp"? It works fine with a datetime column.  
 
sample
create table #person
(
   person_id uniqueidentifier
  ,person_name varchar(200)
)
create table #patient_encounter (
enc_id int identity
, person_id uniqueidentifier
, enc_timestamp datetime
)

create table #appointments (
appt_id int identity
, enc_id int
)

insert into #person values ('A3D453A1-B473-400B-B7EE-3CD70D2E62E0', 'Joe')
insert into #person values ('C7BAD0CC-511E-4B15-BDFC-3DC7FCA8535C', 'Anne')
insert into #person values ('50CF8944-9EF9-41DB-B663-BF10B6719E8A', 'Bob')

insert into #patient_encounter values ('A3D453A1-B473-400B-B7EE-3CD70D2E62E0', '2016-01-12 14:15:00');
insert into #patient_encounter values ('A3D453A1-B473-400B-B7EE-3CD70D2E62E0', '2016-02-26 11:45:00');
insert into #patient_encounter values ('A3D453A1-B473-400B-B7EE-3CD70D2E62E0', '2016-08-06 14:45:00');

insert into #patient_encounter values ('50CF8944-9EF9-41DB-B663-BF10B6719E8A', '2016-09-06 14:45:00');
insert into #patient_encounter values ('50CF8944-9EF9-41DB-B663-BF10B6719E8A', '2016-08-11 14:10:00');

insert into #appointments (enc_id)
select enc_id
from   #patient_encounter

Open in new window


base data
person_id	enc_timestamp	rowno
A3D453A1-B473-400B-B7EE-3CD70D2E62E0	2016-08-06 14:45:00.000	1
A3D453A1-B473-400B-B7EE-3CD70D2E62E0	2016-02-26 11:45:00.000	2
A3D453A1-B473-400B-B7EE-3CD70D2E62E0	2016-01-12 14:15:00.000	3
50CF8944-9EF9-41DB-B663-BF10B6719E8A	2016-09-06 14:45:00.000	1
50CF8944-9EF9-41DB-B663-BF10B6719E8A	2016-08-11 14:10:00.000	2

Open in new window


query
; select  test.* 
from (
	select p.person_id
			, pe.enc_timestamp, 
			ROW_NUMBER() OVER (PARTITION BY p.person_id ORDER BY pe.enc_timestamp DESC) AS rowno
	from #person p
			INNER JOIN #patient_encounter pe on p.person_id = pe.person_id
			INNER JOIN #appointments AS a with(nolock)  ON pe.enc_id	= a.enc_id
) test
where test.rowno = 1

Open in new window


results:
person_id	enc_timestamp	rowno
A3D453A1-B473-400B-B7EE-3CD70D2E62E0	2016-08-06 14:45:00.000	1
50CF8944-9EF9-41DB-B663-BF10B6719E8A	2016-09-06 14:45:00.000	1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
biotecAuthor Commented:
Thanks for the comments. Based on these thoughts here I was able to figure out what the problem was. It was working in a sense but the enc_timestamp didn't seem accurate but that was due to the filters I placed on the appointment table which gets a bit complex but bottom line that filter also filters out some of the encounters and then gives a timestamp that I thought was wrong but really wasn't.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.