Link to home
Create AccountLog in
Avatar of britpopfan74
britpopfan74Flag for United States of America

asked on

Help getting a distinct length of stay

Hello experts,

I'm writing code that extracts for pre-defined conditions (not shown here) to gather detailed data to help me ultimately calculate summary utilization measures such as "member months" and "utilization per 1,000", etc.

Where I am stuck currently is that I have the below code where I need to have the detailed output (attached and de-identified for a few examples).

I need to have for each subscriber (sub#) the detail of the claim at the line level for the "allowed" and "paid" for each service so I can categorize it accordingly.

Detail not shown here but line 1 of a given claim may need to be allocated to "ER" whereas line 2 may need to be allocated to "Pharmacy" for the report.

But, repeating throughout is the length of service (LOS), which in the final calculations I'm trying to do are assuming whatever days reflected per line level are to be summed per sub# per claim.

So if it's a claim with a LOS of 1 day but has 2 line items, I'm calculating 2 days instead of 1 day.

I hope this makes sense; please follow-up with any questions - thank you in advance!

SELECT DISTINCT CONVERT(INT, C1.SUB#) AS SUB#
, MIN(C1.START) AS START
, MAX(C1.THRU) AS THRU
, LOS = CASE WHEN (((MAX(C1.THRU) = MIN(C1.START)))) THEN CAST((MAX(C1.THRU) - MIN(C1.START)+1) AS FLOAT)
                              ELSE CAST((MAX(C1.THRU) - MIN(C1.START)) AS FLOAT) END
, C1.CLAIM#
, C1.LINE#
, C1.LINE_ALLOW
, 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#)
GROUP BY C1.SUB#
, C2.SUB#
, C1.CLAIM#
, C1.LINE#
, C1.TOTAL_PAY
, C1.LINE_ALLOW
, C1.TOTAL_PAY
Avatar of britpopfan74
britpopfan74
Flag of United States of America image

ASKER

I'm wondering if a "top n" sub-select or row_number() over (partition... may work to get a unique LOS per sub# per claim#; trying some examples
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Hi Mark,

Thanks so much for responding...and for giving me food for thought!

You are so correct in pointing out that LOS doesn't have to be done at a particular level...

In the process, I found an issue that I had not realized before and need to incorporate into this logic...maybe you can give me some advice.

An example is this same subscriber having the same claim #:

START                                      THRU                                          LOS
2011-03-02 00:00:00.000      2011-03-31 00:00:00.000            29
2011-03-09 00:00:00.000      2011-03-23 00:00:00.000            14
2011-03-23 00:00:00.000      2011-04-01 00:00:00.000            9

So for the designated line item on the claim, let's say 'REHAB', the way the system is setup, I'm calculating 3 different LOS for this subscriber. But -- and this may be something I have to go back to the claims dept. to understand -- does this warrant billing for the aggregate of these LOS even though they are overlapping?

Luckily, most line items are more straight-forward than this one and then I was able to aggregate into some coding like below:

SELECT COUNT(DISTINCT SUB#) AS SUB_COUNT
, COUNT(DISTINCT CLAIM#) AS CLAIM_COUNT
, SUM(LOS) AS TOT_DAYS
, SUM(TOT_ENC) AS TOT_VISITS
, ALOS = CAST(AVG(TOT_ENC) AS DECIMAL (13, 2))
, SUM(TOT_AWP) AS TOT_AWP
, SUM(TOT_PAY) AS TOT_PD
INTO #SUMMED_CLAIMS
FROM #UPDATED_CLAIMS
Well, something like the "REHAB" example is a very good example of how and when you need to aggregate.

While it might end up being SUM(LOS) as TOT_DAYS

It could also be DATEDIFF(DAY, MIN(START), MAX(THRU)) to get the total duration of days regardless of how many different types of activities (or claims).

If the different types of claims are accumulative, then the SUM() aggregate would seem to be the correct choice as per your code above.

One reveal of "how to" is the aggregation of "pay" so it would indicate that SUM() is the more logical choice.

Interested to see "allow" being aggregated as well. What if there are a few items within the same allowance ? you probably dont want to aggregate several of the exact same allowances because that will artificially inflate the real allowance for a particular claim type. For example claim 2277 line items 3 and 4 similar for 5 and 6 (from your spreadsheet) seem to have the same "allow" value - which is fine if they are for different activities (or claim types), but not if they are for the same claim type. In the case of being the same claim type, then the SUM() would give 2 rather than the datediff calc giving 1 for days.

( I have made certain assumptions as to what your business is about, so, might be wrong in a few of the above synopsis )