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)
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)
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window