We help IT Professionals succeed at work.
Get Started

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

400 Views
Last Modified: 2012-08-14
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)
Comment
Watch Question
Most Valuable Expert 2014
Commented:
This problem has been solved!
Unlock 1 Answer and 3 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE