Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2012-08-13
3
Medium Priority
?
388 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)
0
Comment
Question by:britpopfan74
  • 2
3 Comments
 
LVL 38

Expert Comment

by:Jim P.
ID: 38289551
You may want to go with a union query instead. Assuming that the main or the detail can only occur once:

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) 

union 

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

Open in new window

0
 

Author Comment

by:britpopfan74
ID: 38289701
I needed to make some modifications to get the correct number of variables for the union statement.

Unfortunately, I'm not pulling any rows.

"the main or the detail can only occur once" -- you could have one master record to one or many detail records, depending on how many line codes there are


DECLARE @FDOS DATETIME
DECLARE @EFF_THRU DATETIME
DECLARE @TIN VARCHAR(9)
SET @FDOS = '01/01/2011' --
SET @EFF_THRU = '08/13/2012' -- THROUGH NOW
SET @TIN = ''

SELECT MCP.MEMBER
, MCP.[DOCUMENT]
, MCP.REGION
, MCP.FIRST_DOS
, MCP.CLM_TYPE
, MCD.LINE_CODE
, MCP.VENDOR AS VENDOR#
, MCP.PROVIDER AS PROVIDER#
, CLAIM_STATE =     CASE WHEN MCP.CL_STATUS ='B' THEN 'Batched'...
FROM MASTER_CLAIM_PEND MCP
 JOIN MASTER_CLAIM#DETAIL MCD ON (MCP.[DOCUMENT] = mcd.[DOCUMENT])
AND (MCP.MEMBER = MCD.MEMBER)
WHERE CLAIM_LINE_NUMBER > 0
AND
(MCP.FIRST_DOS BETWEEN @FDOS AND @EFF_THRU)
AND (MCP.CL_STATUS IN('E','P','A')) -- LIKE PAID
AND ((MCP.PROVIDER
IN(SELECT PROVIDER_ID FROM dbo.PHYSICIAN WHERE FED# = @TIN)) OR
(MCP.VENDOR
IN(SELECT VENDOR_NUM FROM dbo.VENDOR WHERE FED_ID = @TIN)))

union

SELECT MC.MEMBER
, MC.[DOCUMENT]
, MC.REGION
, MC.FIRST_DOS
, MC.CLM_TYPE
, MCD.LINE_CODE
, MC.VENDOR AS VENDOR#
, MC.PROVIDER AS PROVIDER#
, CLAIM_STATE =  --TAKEN FROM DST DD
    CASE WHEN MC.CL_STATUS ='B' THEN 'Batched'...
FROM MASTER_CLAIM#DETAIL MCD
 JOIN MASTER_CLAIM MC ON (MC.[DOCUMENT] = mcd.[DOCUMENT])
AND (MC.MEMBER = MCD.MEMBER)
WHERE CLAIM_LINE_NUMBER > 0
AND
(MC.FIRST_DOS BETWEEN @FDOS AND @EFF_THRU)
AND (MC.CL_STATUS = 'P') -- ONLY PAID
AND
(MC.PROVIDER_NPI = @TIN) OR
(MC.VENDOR_NPI = @TIN)
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 2000 total points
ID: 38289867
Build the two queries independently -- make sure they work, then do the union.

Or the other way is to build two select queries and then use them as subqueries where they intersect:

Select SubQ1.*, SubQ2.*
from (SELECT MCP.MEMBER
, MCP.[DOCUMENT]
, MCP.REGION
, MCP.FIRST_DOS
, MCP.CLM_TYPE
, MCD.LINE_CODE
, MCP.VENDOR AS VENDOR#
, MCP.PROVIDER AS PROVIDER#
, CLAIM_STATE =     CASE WHEN MCP.CL_STATUS ='B' THEN 'Batched'...
FROM MASTER_CLAIM_PEND MCP
 JOIN MASTER_CLAIM#DETAIL MCD ON (MCP.[DOCUMENT] = mcd.[DOCUMENT])
AND (MCP.MEMBER = MCD.MEMBER)
WHERE CLAIM_LINE_NUMBER > 0
AND
(MCP.FIRST_DOS BETWEEN @FDOS AND @EFF_THRU)
AND (MCP.CL_STATUS IN('E','P','A')) -- LIKE PAID
AND ((MCP.PROVIDER
IN(SELECT PROVIDER_ID FROM dbo.PHYSICIAN WHERE FED# = @TIN)) OR
(MCP.VENDOR
IN(SELECT VENDOR_NUM FROM dbo.VENDOR WHERE FED_ID = @TIN))) ) SubQ1
LEFT JOIN 
(SELECT MC.MEMBER
, MC.[DOCUMENT]
, MC.REGION
, MC.FIRST_DOS
, MC.CLM_TYPE
, MCD.LINE_CODE
, MC.VENDOR AS VENDOR#
, MC.PROVIDER AS PROVIDER#
, CLAIM_STATE =  --TAKEN FROM DST DD
    CASE WHEN MC.CL_STATUS ='B' THEN 'Batched'...
FROM MASTER_CLAIM#DETAIL MCD
 JOIN MASTER_CLAIM MC ON (MC.[DOCUMENT] = mcd.[DOCUMENT])
AND (MC.MEMBER = MCD.MEMBER)
WHERE CLAIM_LINE_NUMBER > 0
AND
(MC.FIRST_DOS BETWEEN @FDOS AND @EFF_THRU)
AND (MC.CL_STATUS = 'P') -- ONLY PAID
AND
(MC.PROVIDER_NPI = @TIN) OR
(MC.VENDOR_NPI = @TIN) ) SubQ2

On SubQ1.MEMBER = SubQ2.MEMBER

Open in new window


Then you can use coalesce and case statements to present the data you want.
0

Featured Post

Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question