[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 509
  • Last Modified:

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
0
britpopfan74
Asked:
britpopfan74
  • 5
  • 2
1 Solution
 
exodusterCommented:
Hi.
I think You can just use subquery which return MAXLINE for each CLAIM.
If you need more help from me, i need to know what columns are in table #CLAIMS and what data in those columns.
0
 
britpopfan74Author Commented:
Thank you exoduster. Please see the attachment which has several claims from #CLAIMS; I look forward to your suggestion.
de-ided-claim-data.xlsx
0
 
britpopfan74Author Commented:
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
exodusterCommented:
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?
0
 
britpopfan74Author Commented:
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.
0
 
britpopfan74Author Commented:
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...
0
 
britpopfan74Author Commented:
I ended up just using MAX(line#) and it works; thanks.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now