Solved

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

Posted on 2012-08-22
598 Views
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 (
((ADJUSTMENT_CODE NOT LIKE 'D%') OR (ADJUSTMENT_CODE IS NULL))
OR ((ADJUSTMENT_CODE_2 NOT LIKE 'D%') OR (ADJUSTMENT_CODE_2 IS NULL))
)
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
Question by:britpopfan74

Author Comment

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

LVL 34

Accepted Solution

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 Comment

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

LVL 100

Assisted Solution

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 Comment

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 Closing Comment

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

LVL 34

Expert Comment

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

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

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

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

#### Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!