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

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 609

Help with when and where to insert this logic in the SQL

Hi Experts,

In our database, our claims table has the #master and the #detail.

Only from the #detail is where I'm able to pull any line items (e.g. procedures, their individual costs and dates).

My issue is I need a way to accurately count the days of service when there is one master claim that has multiple line items.

So for example, say I have the following data:

mem_no            claim#            DOS            LOS      LINE_CODE..
001            2001            2012-03-28      1            421
001            2001            2012-03-28      1            421
001            2001            2012-03-28      1            421

This member had 3 of the same procedures on the same day -- the way it would appear for my counting is currently is 3 days as LOS, which obviously is incorrect.

I know that I need to have logic in my code to check to make sure that if the same member's claim and DOS are the same, count only 1x otherwise calculate with the LOS equation.

If someone could suggest how to incorporate this, much appreciation!

Current code:

SELECT DISTINCT D.mem_no
, MCD.[DOCUMENT] AS CLAIM
, MCD.SERV_DT AS DOS
, MCD.LINE_CODE
, MCD.PAR_NONPAR AS NP
, LOS = ABS(DATEDIFF(DAY, SERV_DT, THRU_DT) + 1) -- always counts at least one dos
, MCD.MAX_AWP AS BED_ALLOWABLE
, MCD.LINE_DISC AS BED_DISCOUNT
, BED_DAY = ((SUM(MCD.MAX_AWP) - (SUM(MCD.LINE_DISC)))/SUM(ABS(DATEDIFF(DAY, SERV_DT, THRU_DT)+1)))
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]
WHERE
(MCD.SERV_DT BETWEEN '2012/01/01' AND '2012/08/01') --SET TIME PERIOD
AND (mc.[DOCUMENT] IS NOT NULL)
AND (mc.VALID_CLAIM IS NULL)
AND (MCD.LINE_CODE IN('420', '421', '422', '423', '424', '429', '430'
, '431', '432', '433', '434', '439',
'440', '441', '442', '443', '444', '449'))
AND (PAR_NONPAR = @PAR)
--AND (PL_CODE = @POS)
AND (
)
GROUP BY D.mem_no
, MCD.[DOCUMENT]
, MCD.SERV_DT
, MCD.THRU_DT
, MCD.LINE_CODE
, MCD.PAR_NONPAR
, MCD.MAX_AWP
, MCD.LINE_DISC
ORDER BY MCD.SERV_DT
0
britpopfan74
• 4
• 2
2 Solutions

Author Commented:
will try this self-join somehow in a subquery but still need ideas; thanks

SELECT COUNT(DISTINCT mcd1.SERV_DT)
FROM MASTER_CLAIM#DETAIL mcd1, MASTER_CLAIM#DETAIL mcd2
WHERE (mcd1.[DOCUMENT] = mcd2.[DOCUMENT])
AND (mcd1.SERV_DT = mcd2.SERV_DT)
GROUP BY mcd1.SERV_DT, mcd2.SERV_DT
0

Commented:
First thing that comes to mind - Combine claim# and DOS and do a distinct count on the result.  I'd probably convert them to strings (eg. claim 2001 and DOS 2012-03-28 becomes something like '2001 2012-03-28').  If DOS is actually a datetime, you'd have to decide whether or not the time should be included.

Depending on how/where you're doing the count, you might need to include mem_no too.

OTOH, now I'm thinking that I may have misunderstood the problem and it's not about only counting each combination of claim# and DOS one time, and is actually about only adding LOS once for each of those combinations, in which case this may not help.

James
0

Author Commented:
Hi James,

Thanks so much for the feedback...I took a step back myself.

My trying to calculate the LOS too early in the process is problematic...below is a sample of de-identified data with claim line codes added.

There are 2 members, each has 2 unique claims. They also happen to have 2 distinct DOS (1/3 and 1/5).

If I was to concatenate the mem_no, claim# and DOS, I don't think this would be detailed enough -- maybe adding in the claim line gets to that detail.

The way I've gotten to this is with a self-join though I don't think it's giving me the aggregate count I need -- perhaps doing the above concatentation and retrying will work - will give it a whirl.

mem_no      claim#      CLAIM_LINE#      DOS      LINE_CODE..

001      2001      1      2012-01-03 00:00:00.000      421      P
001      2001      2      2012-01-03 00:00:00.000      424      P
001      2001      3      2012-01-05 00:00:00.000      421      P

002      2002      1      2012-01-03 00:00:00.000      421      P
002      2002      2      2012-01-03 00:00:00.000      421      P
002      2002      3      2012-01-05 00:00:00.000      421      P
002      2002      4      2012-01-05 00:00:00.000      421      P
002      2002      5      2012-01-05 00:00:00.000      424      P
0

Commented:
Adding claim line would count each line as a separate instance.
Assuming claim number is unique to the system and not to the member all you should need is claim# and date of service to count the days.

I don't see in the data you have how each has 2 claims.  To me it seems there are 2 members 001 and 002 with claims 2001 and 2002.

mlmcc
0

Author Commented:
You are correct, mlmcc, I had to think on it...

Went back and made a unique # out of the mem_no, claim and DOS:

UNIQUE_CODE = REPLACE(D.mem_no,'*','') + '-' + MCD.[DOCUMENT]
+ '-' + CONVERT(VARCHAR(8), SERV_DT, 10)

This is great because it allows me:

SELECT MEM_NO
, CLAIM#
, DOS
, LINE_CODE
, NP
, UNIQUE_CODE
, COUNT(UNIQUE_CODE) AS UNIQUE_COMBO
, CASE WHEN COUNT(UNIQUE_CODE) > 1 THEN 1 ELSE 1 END AS DOSCOUNT
INTO #UPDATED_CLAIMS
FROM #CLAIMS C
GROUP BY MEM_NO
, CLAIM#
, DOS
, LINE_CODE
, NP
, UNIQUE_CODE

So now I can get to where I'm getting 1 DOS even if the member has multiple claims/day.

UNIQUE_CODE      UNIQUE_COMBO      DOSCOUNT
001-2001-01-05-12        2      1

Please bear with me...so now I need to aggregate the "DOSCOUNT" to get the correct DOS per mem_no and claim#...I tried the following self-join but I realize it's not correct as I'm getting claim# repeating and the counts are not aggregate:

SELECT distinct mcd1.MEM_NO
, MCD1.CLAIM#
, mcd1.UNIQUE_CODE
,COUNT(mcd1.UNIQUE_CODE) AS COUNT_DOS
FROM #UPDATED_CLAIMS mcd1
INNER JOIN
#UPDATED_CLAIMS mcd2 ON
(mcd1.UNIQUE_CODE = mcd2.UNIQUE_CODE)
GROUP BY mcd1.MEM_NO, mcd1.CLAIM#, mcd1.UNIQUE_CODE
0

Author Commented:
Thanks -- went a long way around but finally got where I needed!
0

Commented:
So, you were able to get the results that you needed?  In your previous post, you said that the self-join wasn't giving you the correct results.

And you're welcome.  Glad I could help.

James
0

Featured Post

• 4
• 2
Tackle projects and never again get stuck behind a technical roadblock.