Re: Getting all items from result set where any item has matching crit

From: Aaron Weiker (aaron_at_sqlprogrammer.org)
Date: 01/25/05 

Date: Mon, 24 Jan 2005 18:51:42 -0800

Hello Patrick,
Based on your query I'm not sure how you identify your charges. I'm guessing
that it is based upon the [cpt4_code_id] field so I am going to go with that.
If it isn't this field then you can either adjsut the WHERE clause accordingly
or repost with the correct information.

The only line that I add/modified was the last line in the WHERE statement.

SELECT
   NP.last_name + ', ' + NP.first_name AS Patient,
   C.cpt4_code_id,
   C.closing_date,
   V.visit_id,
   PM.last_name,
   PYM.payer_name
FROM charges C
   INNER JOIN visits V
      ON V.visit_id=C.source_id
   INNER JOIN provider_mstr PM
      ON C.rendering_id = PM.provider_id
   INNER JOIN payer_mstr PYM
      ON V.cob1_payer_id = PYM.payer_id
   INNER JOIN network_people NP
      ON NP.hin=V.hin
WHERE
   C.closing_date BETWEEN 20041216 AND 20050115
   AND V.location_id ='LM0UPA3FMION'
   AND PM.last_name <> 'Dietrich PA-C'
   AND PM.last_name <> 'Stewart PA-C'
   AND PM.last_name <> 'Knott PA-C'
   AND PM.last_name <> 'Evangelista MD'
   AND PM.last_name <> 'King MD'
   AND c.cpt4_code_id LIKE 'Er%'
ORDER BY
   NP.last_name ASC,
   C.cpt4_code_id DESC,
   PM.last_name ASC,
   V.onset_date ASC 

--
Aaron Weiker
http://aaronweiker.com/
> I'm trying to determine all charges (cpt4_code_id) for patients
> (NP.last_name) seen in the Emergency Room (WHERE cpt4_code_id LIKE
> 'Er%') .  I have the following query which gives me all patients and
> associated charges for people seen in the hospital ('location_id').
> From this list I want only those those patients (and all associated
> charges) where there exists a charge LIKE 'Er%'
> 
> SELECT NP.last_name+', '+NP.first_name AS Patient, C.cpt4_code_id,
> C.closing_date, V.visit_id, PM.last_name,PYM.payer_name
> FROM charges C
> INNER JOIN visits V
> ON V.visit_id=C.source_id
> INNER JOIN provider_mstr PM
> ON C.rendering_id = PM.provider_id
> INNER JOIN payer_mstr PYM
> ON V.cob1_payer_id = PYM.payer_id
> INNER JOIN network_people NP ON
> NP.hin=V.hin
> WHERE
> C.closing_date BETWEEN 20041216 AND 20050115
> AND V.location_id ='LM0UPA3FMION'
> AND PM.last_name <> 'Dietrich PA-C'
> AND PM.last_name <> 'Stewart PA-C'
> AND PM.last_name <> 'Knott PA-C'
> AND PM.last_name <> 'Evangelista MD'
> AND PM.last_name <> 'King MD'
> ORDER BY NP.last_name ASC, C.cpt4_code_id DESC,PM.last_name ASC,
> V.onset_date ASC

Relevant Pages
Loading