narrowing down data - SQL Server Forums

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)

narrowing down data

Author

Topic

sross81
Posting Yak Master

228 Posts

Posted - 2008-09-16 : 14:02:32

I have 3 main tables. Person which contains all person demographic data, patient_encounter which containts information about each patient encounter with a provider, and then charges which links to the patient_encounter table and lists all charges associated with an encounter.

I am trying to create a report that gives me a list of patients that had 6 or more encounters. When I include the charge table it seems to give me alot mroe records and I think that this has to do with it counting the charges on the encounter instead of the encounter as one.

Person
column: person id

one to many relationship to
patient_encounter
column enc_id is unique indentifier

many to many relationship to
charges
column joined on patient_encounter.enc_id = charges.source_id

The reason I need to include the charge table is because I need to limit it so that it does not count OB patients as part of the 6+ encounters list. I have a list of diagnosis codes that I want to filter out. I just am not sure where to put this into my code cause I think eitehr way its still counting the number of charges versus just encounters.

Any suggestions? Let me know if you need more information.

select * from
(
select distinct a.enc_id, c.description,d.location_name,b.person_id,b.first_name,b.last_name,
b.date_of_birth
from patient_encounter a
join person b on a.person_id = b.person_id
join provider_mstr c on a.rendering_provider_id = c.provider_id
join location_mstr d on a.location_id = d.location_id
join charges e on a.enc_id = e.source_id
where a.create_timestamp >= '20080101' and a.create_timestamp <= '20080916' and
b.first_name not like '%lady%' and b.first_name not like '%test%'
group by a.enc_id,c.description,d.location_name,b.person_id,b.first_name,b.last_name,
b.date_of_birth
having count(a.enc_id) >= 6
)t
where t.location_name = 'CHC Eastside Clinic'

Thanks in Advance!
Sherri

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 03:13:07

Show some sample data from your tables and explain output you want

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources