Removing duplicates that are not all duplicates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Removing duplicates that are not all duplicates

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-21 : 12:55:29

Ok so what I mean by my subject is I have records that are bascially all the same data but one field in different so it keeps duplicating. For example

date,Person id, Enc id, Supply, Diagnosis
7/1/08,1, 1, crutch, 202
7/1/08,1,1,crutch,205
7/1/08,1,1,crutch,207
7/1/08,1,1,crutch,208

My problem is that it lists it 4 times because there are 4 different diagnosis codes used but its the same date, person, encounter and supply so I would like if it was only listed one time with like the first diagnosis or maybe a list of the diagnosis but all in one row. Is this something I can do with my current code? Or how could I structure it to give me that result? I am still really new to this...

alter procedure [dbo].[GetDMEList] (@startdate datetime, @enddate datetime,@location_name as varchar(40)) as

select distinct c.enc_id,a.person_id, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth,b.rx_quanity, b.rx_refills,b.sig_desc,c.create_timestamp,
d.brand_name,d.generic_name,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,
h.diagnosis_code_id,
h.description,f.location_name
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
left join patient_procedure g on c.enc_id = g.enc_id
left join diagnosis_code_mstr h on g.diagnosis_code_id_1 = h.diagnosis_code_id
where d.dose_form_desc IN
(
(select distinct dose_form_desc
from fdb_medication
where gcn = '94200')
) and d.route_desc Not IN ('Oral','Topical') and
c.create_timestamp >=@startdate and c.create_timestamp <= @enddate
and f.location_name = @location_name and
a.first_name <> 'Lady' and a.first_name <> 'Test'
order by c.create_timestamp

Any help would be very appreciated. Thanks! :)

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 13:43:13

You can group on date,Person id, Enc id, Supply and take a MIN() or MAX() of Diagnosis to return single id per combination.

sross81
Posting Yak Master

228 Posts

Posted - 2008-07-21 : 14:15:02

I tried that...

select distinct c.enc_id,a.person_id, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth,b.rx_quanity, b.rx_refills,d.dose_form_desc,d.route_desc,d.gcn,b.sig_desc,c.create_timestamp,
d.brand_name,d.generic_name,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,
h.diagnosis_code_id,max(h.diagnosis_code_id),max(h.description)
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
left join patient_procedure g on c.enc_id = g.enc_id
left join diagnosis_code_mstr h on g.diagnosis_code_id_1 = h.diagnosis_code_id
where d.dose_form_desc IN
(
(select distinct dose_form_desc
from fdb_medication
where gcn = '94200')
) and d.route_desc NOT IN ('Oral','Topical') and
c.create_timestamp >= '1/01/08' and c.create_timestamp <= '7/18/08'
and f.location_name = 'CHC Sumner Clinic' and
a.first_name <> 'Lady' and a.first_name <> 'Test'
group by c.enc_id,a.person_id,a.first_name,a.last_name,
a.date_of_birth,b.rx_quanity,b.rx_refills,d.dose_form_desc,
d.route_desc,
d.gcn,b.sig_desc,c.create_timestamp,d.brand_name,d.generic_name,
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,h.diagnosis_code_id,h.description
order by fullname

BUT it still keeps duplicating on the diagnosis

quote:
Originally posted by visakh16

You can group on date,Person id, Enc id, Supply and take a MIN() or MAX() of Diagnosis to return single id per combination.

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-21 : 14:21:08

do like this, you want distinct only if you've duplicates in some other table. run without distinct and see if you get duplicates. only if it persists,use distinct.

select distinct c.enc_id,a.person_id, a.first_name + ' ' + a.last_name as fullname,
a.date_of_birth,b.rx_quanity, b.rx_refills,d.dose_form_desc,d.route_desc,d.gcn,b.sig_desc,c.create_timestamp,
d.brand_name,d.generic_name,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,
h.diagnosis_code_id,h.description
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
left join (select enc_id,max(diagnosis_code_id_1) as maxdiagnosisid from patient_procedure group by enc_id)g on c.enc_id = g.enc_id
left join diagnosis_code_mstr h on g.maxdiagnosisid = h.diagnosis_code_id
where d.dose_form_desc IN 
(
(select distinct dose_form_desc 
from fdb_medication
where gcn = '94200')
) and d.route_desc NOT IN ('Oral','Topical') and
c.create_timestamp >= '1/01/08' and c.create_timestamp <= '7/18/08' 
and f.location_name = 'CHC Sumner Clinic' and
a.first_name <> 'Lady' and a.first_name <> 'Test'
order by fullname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources