Need advice: may these tables be joined somehow if the claims in each have different "status"

Hi Experts,

Need advice - I thought there may be a workaround even with the conflicting status issues of "paid" vs. "pre-paid" -- can anyone propose a way to join #Claim_pend to #Claim_detail so that I can find out which claims are "pre-paid" to find their CPT codes?

We currently have several claims tables:

1. #Claim_pend - where claims go at pre-'Paid' status; these have a member #, claim # and primary dx on it but no CPT code

2. #Claim_main - where "paid" claims go at the "highest level", that is, the totals are shown (total paid, allowed, billed, etc.); again, these have a member #, claim # and primary dx on it but no CPT code

3, #Claim_detail - where "paid" claims go and have detail listed by line item on the claim by CPT code; these have a member #, claim # (no primary dx)


Every way I've tried with SQL joining I've not been able to get to the line level, only the master level.

So for ex. there are 6 "approved" claims currently I want to report on and I'm getting 6 total/master records for claim payment, provider, member, claim#...but each claim may have multiple line items -- like a code for some diagnostic test plus one for visiting with the provider.

My attempts (partly below) aren't working so any help much appreciated as I'm having to pull everything manually now; thanks!

DECLARE @FDOS DATETIME
DECLARE @EFF_THRU DATETIME
SET @FDOS = '01/01/2011' --
SET @EFF_THRU = '08/13/2012' -- THROUGH NOW

SELECT MCP.*, MCD.*
FROM MASTER_CLAIM#DETAIL MCD
 JOIN MASTER_CLAIM_PEND MCP ON (MCP.[DOCUMENT] = mcd.[DOCUMENT])
AND (MCP.MEMBER = MCD.MEMBER)
WHERE CLAIM_LINE_NUMBER > 0
AND (MCP.FIRST_DOS BETWEEN @FDOS AND @EFF_THRU)