Help getting the MAX line item on a claim when dates of service "out of sequence"
Hello Experts,
I need help with the logic and coding a specific scenario occurring when I go to aggregate claims data.
An example of the scenario output is like below.
There is a subscriber having one claim that has 31 line items on it.
In the majority of cases, the claims come into the system ordered by date.
However, there will be these exceptions where you may note that like claim #11, the way I'm running my code, it's "segmenting" into different date periods.
I'm able to get the correct total inpatient days (13) but not the correct max line number of 31 -- instead, it picks up lines 1 & 2 and with that, 2 rows of total_pays that are erroneous.
SUB# START END CLAIM# MAXLINE# TOT_INPT TOT_AWP TOTAL_PAY
same# 1/10/2011-1/18/2011 11 1 13 8531.04 1988.69
same# 1/18/2011-1/23/2011 11 2 13 8800 1988.69
same# 1/10/2011-1/23/2011 11 31 13 195542.79 1988.69
I really appreciate your suggestions on how to get a max line number on a claim that has these "out of sequence" dates; thank you!
--DROP TABLE #UPDATED_CLAIMS
--SELECT DISTINCT * FROM #UPDATED_CLAIMS
SELECT DISTINCT CONVERT(INT, C1.SUB#) AS SUB#
, C1.CLAIM#
, MAXLINE# = CASE WHEN ((C1.CLAIM# = C2.CLAIM#) AND (C1.START <> C1.THRU))
THEN MAX(C1.LINE#) END -- GET GREATEST LINE#
, TOT_INPT = CASE WHEN (C1.INPT = @INPT) THEN C1.LOS -- CALC. BY DST -- 'Y'
WHEN (C1.INPT <> @INPT) THEN 0 -- SHOULDN'T BE ANY ELSE C1.LOS END
, CASE WHEN (C1.CLAIM# = C2.CLAIM#) THEN SUM(C1.LINE_ALLOW) END AS TOT_AWP
, C1.TOTAL_PAY
INTO #UPDATED_CLAIMS
FROM #CLAIMS C1
INNER JOIN #CLAIMS C2 ON (C1.SUB# = C2.SUB#)
AND (C1.CLAIM# = C2.CLAIM#) AND (C1.LINE# = C2.LINE#)
WHERE C1.START <= C1.THRU
GROUP BY C1.SUB#
, C1.CLAIM#
, C2.CLAIM#
, C1.START
, C1.THRU
, C1.TOTAL_PAY
, C1.INPT
, C1.LOS
I need help with the logic and coding a specific scenario occurring when I go to aggregate claims data.
An example of the scenario output is like below.
There is a subscriber having one claim that has 31 line items on it.
In the majority of cases, the claims come into the system ordered by date.
However, there will be these exceptions where you may note that like claim #11, the way I'm running my code, it's "segmenting" into different date periods.
I'm able to get the correct total inpatient days (13) but not the correct max line number of 31 -- instead, it picks up lines 1 & 2 and with that, 2 rows of total_pays that are erroneous.
SUB# START END CLAIM# MAXLINE# TOT_INPT TOT_AWP TOTAL_PAY
same# 1/10/2011-1/18/2011 11 1 13 8531.04 1988.69
same# 1/18/2011-1/23/2011 11 2 13 8800 1988.69
same# 1/10/2011-1/23/2011 11 31 13 195542.79 1988.69
I really appreciate your suggestions on how to get a max line number on a claim that has these "out of sequence" dates; thank you!
--DROP TABLE #UPDATED_CLAIMS
--SELECT DISTINCT * FROM #UPDATED_CLAIMS
SELECT DISTINCT CONVERT(INT, C1.SUB#) AS SUB#
, C1.CLAIM#
, MAXLINE# = CASE WHEN ((C1.CLAIM# = C2.CLAIM#) AND (C1.START <> C1.THRU))
THEN MAX(C1.LINE#) END -- GET GREATEST LINE#
, TOT_INPT = CASE WHEN (C1.INPT = @INPT) THEN C1.LOS -- CALC. BY DST -- 'Y'
WHEN (C1.INPT <> @INPT) THEN 0 -- SHOULDN'T BE ANY ELSE C1.LOS END
, CASE WHEN (C1.CLAIM# = C2.CLAIM#) THEN SUM(C1.LINE_ALLOW) END AS TOT_AWP
, C1.TOTAL_PAY
INTO #UPDATED_CLAIMS
FROM #CLAIMS C1
INNER JOIN #CLAIMS C2 ON (C1.SUB# = C2.SUB#)
AND (C1.CLAIM# = C2.CLAIM#) AND (C1.LINE# = C2.LINE#)
WHERE C1.START <= C1.THRU
GROUP BY C1.SUB#
, C1.CLAIM#
, C2.CLAIM#
, C1.START
, C1.THRU
, C1.TOTAL_PAY
, C1.INPT
, C1.LOS
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
And the code to get to #CLAIMS - sorry, forgot to attach:
--DROP TABLE #CLAIMS
--SELECT DISTINCT * FROM #CLAIMS
SELECT LEFT(D.MEM_NO, 9) AS SUB#
, MCD.[DOCUMENT] AS CLAIM#
, MCD.CLAIM_LINE_NUMBER AS LINE#
, MCD.SERV_DT AS START
, MCD.THRU_DT AS THRU
, INPT = CASE WHEN MC.VISITS > 0 THEN @INPT ELSE 'N' END -- DATE OF DISCH. NOT RELIABLE
, MC.VISITS AS LOS
, MCD.LINE_CODE
, C.DESCRIPTION
, MCD.CPT_IDS
, MCD.PL_CODE AS POS
, MC.EPSDT
, MC.CLM_TYPE AS CTYPE
, MCD.PAR_NONPAR AS NP
, MCD.MAX_AWP AS LINE_ALLOW
, MCD.PAY_AMT AS LINE_PAY
, MC.TOTAL_PAY_AMT AS TOTAL_PAY
INTO #CLAIMS
from #temp_eligibles D
INNER JOIN MASTER_CLAIM mc ON D.Mem_No = MC.MEMBER
INNER JOIN MASTER_CLAIM#DETAIL mcd ON mc.[DOCUMENT] = mcd.[DOCUMENT]
LEFT JOIN CODE C ON mcd.LINE_CODE = C.PROC_CD
WHERE
(MCD.SERV_DT BETWEEN @FDOS AND @EFF_THRU)
AND (mcD.[DOCUMENT] IS NOT NULL)
AND (mc.VALID_CLAIM IS NULL)
AND (
MC.EPSDT IN(@TYPE) -- UB CLAIMS
AND (MCD.PL_CODE = @POS)
AND (MC.VISITS > 0) -- INPT ONLY
AND (((ADJUSTMENT_CODE NOT LIKE 'D%') OR (ADJUSTMENT_CODE IS NULL))
OR ((ADJUSTMENT_CODE_2 NOT LIKE 'D%') OR (ADJUSTMENT_CODE_2 IS NULL))))
ORDER BY D.mem_no, MCD.[DOCUMENT], MCD.CLAIM_LINE_NUMBER, MCD.SERV_DT
--DROP TABLE #CLAIMS
--SELECT DISTINCT * FROM #CLAIMS
SELECT LEFT(D.MEM_NO, 9) AS SUB#
, MCD.[DOCUMENT] AS CLAIM#
, MCD.CLAIM_LINE_NUMBER AS LINE#
, MCD.SERV_DT AS START
, MCD.THRU_DT AS THRU
, INPT = CASE WHEN MC.VISITS > 0 THEN @INPT ELSE 'N' END -- DATE OF DISCH. NOT RELIABLE
, MC.VISITS AS LOS
, MCD.LINE_CODE
, C.DESCRIPTION
, MCD.CPT_IDS
, MCD.PL_CODE AS POS
, MC.EPSDT
, MC.CLM_TYPE AS CTYPE
, MCD.PAR_NONPAR AS NP
, MCD.MAX_AWP AS LINE_ALLOW
, MCD.PAY_AMT AS LINE_PAY
, MC.TOTAL_PAY_AMT AS TOTAL_PAY
INTO #CLAIMS
from #temp_eligibles D
INNER JOIN MASTER_CLAIM mc ON D.Mem_No = MC.MEMBER
INNER JOIN MASTER_CLAIM#DETAIL mcd ON mc.[DOCUMENT] = mcd.[DOCUMENT]
LEFT JOIN CODE C ON mcd.LINE_CODE = C.PROC_CD
WHERE
(MCD.SERV_DT BETWEEN @FDOS AND @EFF_THRU)
AND (mcD.[DOCUMENT] IS NOT NULL)
AND (mc.VALID_CLAIM IS NULL)
AND (
MC.EPSDT IN(@TYPE) -- UB CLAIMS
AND (MCD.PL_CODE = @POS)
AND (MC.VISITS > 0) -- INPT ONLY
AND (((ADJUSTMENT_CODE NOT LIKE 'D%') OR (ADJUSTMENT_CODE IS NULL))
OR ((ADJUSTMENT_CODE_2 NOT LIKE 'D%') OR (ADJUSTMENT_CODE_2 IS NULL))))
ORDER BY D.mem_no, MCD.[DOCUMENT], MCD.CLAIM_LINE_NUMBER, MCD.SERV_DT
It's still too hard for me. What about data in excel from wich you get that:
SUB# START END CLAIM# MAXLINE# TOT_INPT TOT_AWP TOTAL_PAY
same# 1/10/2011-1/18/2011 11 1 13 8531.04 1988.69
same# 1/18/2011-1/23/2011 11 2 13 8800 1988.69
same# 1/10/2011-1/23/2011 11 31 13 195542.79 1988.69
and result you want at the same form as above?
SUB# START END CLAIM# MAXLINE# TOT_INPT TOT_AWP TOTAL_PAY
same# 1/10/2011-1/18/2011 11 1 13 8531.04 1988.69
same# 1/18/2011-1/23/2011 11 2 13 8800 1988.69
same# 1/10/2011-1/23/2011 11 31 13 195542.79 1988.69
and result you want at the same form as above?
ASKER
Maybe I misunderstood, sorry.
I attached what is the output from the first temp table #CLAIMS.
In the second temp table, #UPDATED_CLAIMS, I get what is the original output without the dates e.g. (one line shown only)
SUB# CLAIM# MAXLINE# TOT_INPT TOT_AWP TOTAL_PAY
same# 11 1 13 8531.04 1988.69
and the problem is that for some of these claims, they are repeating due to the dates being "out of sequence" so I need some logic to order this I think in #UPDATED_CLAIMS so I'm not double counting claims.
I attached what is the output from the first temp table #CLAIMS.
In the second temp table, #UPDATED_CLAIMS, I get what is the original output without the dates e.g. (one line shown only)
SUB# CLAIM# MAXLINE# TOT_INPT TOT_AWP TOTAL_PAY
same# 11 1 13 8531.04 1988.69
and the problem is that for some of these claims, they are repeating due to the dates being "out of sequence" so I need some logic to order this I think in #UPDATED_CLAIMS so I'm not double counting claims.
ASKER
CASE WHEN ((C1.CLAIM# = C2.CLAIM#) AND (C1.START <> C1.THRU))
then (select max(C1.LINE#) from #CLAIMS) end as MAXLINE#
returns more than 1 value so fails but I think along these lines...
then (select max(C1.LINE#) from #CLAIMS) end as MAXLINE#
returns more than 1 value so fails but I think along these lines...
ASKER
I ended up just using MAX(line#) and it works; thanks.
ASKER
de-ided-claim-data.xlsx