quote:

---

Originally posted by werhardt

I am not sure how to go about asking this. I have a database that imported multiple products under a tin. I was wondering how I can go about find all of them. What I am thinking is....Give me all taxid (pcsa_tax1)that have duplicate products "DGH" (pcsp_prd). Not sure if this is enough information for you.

TaxID 2459872548
BROOKLYN PEDIATRIC ASSOCIATES

DEVON DGH EFF: 06/01/2003 TRM: PHYFEE BCD
DEVON DGH EFF: 05/15/2003 TRM: PHYFEE BCD

---

SELECT taxid
FROM YourTable t
JOIN (SELECT pcsp_prd
FROM YourTable
GROUP BY pcsp_prd
HAVING COUNT(*) >1)tmp
ON tmp.pcsp_prd=t.pcsp_prd

quote:

---

Originally posted by werhardt

Ok this is working below, but it is also brining over ones that have mulitple addresses. is there a way that I can say something like PCSA_ADDR1<>PCSA_ADDR1?


select * from VW_Double_Pricing
where PCSA_tax1 in (

Select Distinct
PCSA_tax1

From VW_Double_Pricing
where pcsp_prd = 'DGH' --and
--PCSA_ADDR1 <> p.PCSA_ADDR1
group by PCSA_tax1

HAVING COUNT (pcsp_prd) > 1)
order by pcs_lname

---

SELECT taxid
FROM YourTable t
JOIN (SELECT pcsp_prd,address
FROM YourTable
GROUP BY pcsp_prd,address
HAVING COUNT(*) >1)tmp
ON tmp.pcsp_prd=t.pcsp_prd
AND tmp.address=t.address

quote:

---

Originally posted by werhardt

This is starting to look good, but it is still not pulling like i want it to. Maybe it can't be done.
It is pulling both of these for this doctor, maybe I shouldn't use the taxid. Maybe I should use the PCS number that goes to the doctor. I only want it to pull the one in in read because they have the product twice under that doctor.

ALFRED SWYER 1176 5TH AVENUE, BOX 1235
TAX-009010020 NEW YORK, NY 10029
* DAMTS DGH EFF: 11/05/2002 TRM: PHYFEE

MT. SINAI RADIOLOGY ASSOCIATES 1176 FIFTH AVE.
TAX-136171197 NEW YORK, NY 10029-6503
* DAMTS DGH EFF: 10/02/2006 TRM: PHYFEE
* GHINA PPO EFF: 07/01/1996 TRM: GHINAL
* GHINA PPO EFF: 11/06/2003 TRM: GHINAL

---