S
sherriross81
Hello,
I have a query that returns duplicate rows because of a one to many
relationship. Basically here is a simplified version of the data.
person,date,problem
1, 10/29/08, sick
2, 10/29/08, pain
I want to return it as
person, date, problem
1,10/29/08, sick and pain
BUT I can't get it to return a list of problems instead of returning
separate rows. Can someone let me know what is wrong with my code? I looked
through the two rows that come back and all data for each column is identical
except for the problem so it seems that the 2 problems are the only reason
for the duplicate rows. Any ideas would be greatly appreciated
.
select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr,
a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,max(c.create_timestamp) as DOS,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
outer apply (select description + ','
from diagnosis_code_mstr
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by
dl.desclist,c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name
order by c.enc_id, fullname
I have a query that returns duplicate rows because of a one to many
relationship. Basically here is a simplified version of the data.
person,date,problem
1, 10/29/08, sick
2, 10/29/08, pain
I want to return it as
person, date, problem
1,10/29/08, sick and pain
BUT I can't get it to return a list of problems instead of returning
separate rows. Can someone let me know what is wrong with my code? I looked
through the two rows that come back and all data for each column is identical
except for the problem so it seems that the 2 problems are the only reason
for the duplicate rows. Any ideas would be greatly appreciated
.select distinct c.enc_id,a.person_id,LTRIM(a.person_nbr) as AcctNbr,
a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth, max(b.rx_refills) as refills,max(c.create_timestamp) as DOS,
e.first_name + ' ' + e.last_name as provname,
e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,f.phone,f.fax,
f.location_name,
coalesce(left(dl.desclist,len(dl.desclist)-1),'') as problem
from person a
join patient_medication b on a.person_id = b.person_id
join patient_encounter c on b.enc_id = c.enc_id
join fdb_medication d on b.ndc_id = d.ndc_id
join provider_mstr e on b.provider_id = e.provider_id
join location_mstr f on b.location_id = f.location_id
outer apply (select description + ','
from diagnosis_code_mstr
where diagnosis_code_id=b.diagnosis_code_id
for xml path(''))dl(desclist)
where d.gcn = '94200' and b.date_stopped = ' ' and a.person_nbr = 77622
group by
dl.desclist,c.enc_id,a.person_id,a.person_nbr,a.first_name,a.last_name,a.date_of_birth,
e.first_name,e.last_name,e.other_lic_id,f.address_line_1,f.city,f.state,f.zip,
f.phone,f.fax,f.location_name
order by c.enc_id, fullname