getting multiple codes for each encounter

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 2000 Forums

Transact-SQL (2000)

getting multiple codes for each encounter

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-18 : 10:45:29

basavaraja goud writes "Hi,
I have problem in getting codes for each encounter;
main table: encounter_main
code tables: encounter_dx, encounter_px, encounter_hcpcs.

The encounter_main has account_num field.
Each code tables have code fields and seq_num.

I want to get all codes for each account_num order by seq_num.

I have written following code.

select
M.facility_id AS AUDITWORKOP_DX_FACILITY_ID,
M.account_num AS AUDITWORKOP_DX_ACCOUNT_NUM,
M.encounter_sid AS AUDITWORKOP_DX_ENCOUNTER_SID,
isnull(isnull(D.seq_num, P.seq_num), H.hcpcs_seq_num) AS AUDITWORKOP_DX_seq_num,
ISNULL(D.code,'') AS AUDITWORKOP_DX_dx_code,
ISNULL(P.code,'') AS AUDITWORKOP_DX_px_code,
ISNULL(H.code,'') AS AUDITWORKOP_DX_hcpcs
from encounter_main M
inner join encounter E on M.encounter_sid = E.encounter_sid
full outer join encounter_dx D
on M.encounter_sid=D.encounter_sid and M.revision = D.revision
full outer join encounter_px P
on M.encounter_sid=P.encounter_sid and M.revision = P.revision and D.seq_num = P.seq_num
full outer join encounter_hcpcs H
on M.encounter_sid=H.encounter_sid and M.revision = H.revision
and (D.seq_num = H.hcpcs_seq_num or P.seq_num = H.hcpcs_seq_num)
WHERE
E.prebill_selected=1
and M.REVISION=0
AND M.VISIT_TYPE_CLASS = 'O'
order by 1,2

Problem with this code is i will get only codes corresponds to encounter_dx. suppose encouter_px, encounter_hcpcs has more codes than encounter_dx i will not get those codes.
I want get all codes irrespective of the no of codes in each code table for each account_num in encounter_main.

Thanks in advance."

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-18 : 13:29:14

quote:
I want get all codes irrespective of the no of codes in each code table for each account_num in encounter_main.

If I'm reading this correctly (and I don't have a lot of confidence that I am) then it sounds like you are talking about a CROSS JOIN instead of FULL OUTER JOINs. However beware that this would generate HUGE volumes of data. Perhaps you could post some sample data and desired results to help us understand what you're looking for.

--------------------------------
There's a new General in town...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources