Link to home
Start Free TrialLog in
Avatar of britpopfan74
britpopfan74Flag for United States of America

asked on

Total count of re-admission visits

Hi Experts,

I'm having trouble calculating the count of re-admissions of subscribers to the ER.

I've tried many variations on COUNT(*) and trying to find the MAX(thru) of the previous claim to the MAX(thru) of the follow-up claim but cannot get the logic.

Please advise; I'll provide sample data; thank you very much!

--DROP TABLE #UPDATED_CLAIMS
--SELECT DISTINCT * FROM #UPDATED_CLAIMS

SELECT DISTINCT CONVERT(INT, C1.SUB#) AS SUB#
, C1.START
, C1.THRU
, C1.CLAIM#
, MAX(C1.LINE#) AS MAXLINE#
, TOT_INPT_DAYS = CASE WHEN (C1.INPT = @INPT) THEN C1.LOS -- CALC. BY DST      
                 WHEN (C1.INPT <> @INPT) THEN 0  -- SHOULDN'T BE ANY
                   ELSE C1.LOS END --IF NO DISCH. DT. CONSIDERED NOT INPT. BUT ABOVE WILL CONSIDER 1 DAY
, BW_ADMITS = CASE WHEN ((C1.SUB# = C2.SUB#) AND (C1.START < C2.THRU)
                        AND (C1.THRU > C2.THRU)) THEN (DATEDIFF(DD, C1.THRU, C2.THRU)) ELSE 0 END -- not working
, 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#
, C2.SUB#
, C1.CLAIM#
, C2.CLAIM#
, C1.START
, C2.START
, C1.THRU
, C2.THRU
, C1.TOTAL_PAY
, C1.INPT
, C1.LOS
, C1.SVC_MOS
--HAVING C1.SUB# > 0
--ORDER BY C1.MEM_NO
Avatar of britpopfan74
britpopfan74
Flag of United States of America image

ASKER

Avatar of Scott Pletcher
>> I'm having trouble calculating the count of re-admissions of subscribers to the ER. <<

So how does one determine if it's a re-admission of a subscriber to the ER, in data terms?

That is, what value(s) in what column(s) identify it as a re-admission?
In this case, it's purely looking to see if it is the same SUB# and seeing if the previous THRU date is the same or not as the next THRU...

So if you have in the first instance in the dataset:

sub#           start                         thru                       claim#
817      1/10/2011      1/13/2011      785
817      1/13/2011      1/21/2011      161 -- the thru to thru is 0 so no re-admit occurred (count of 0)


695      4/20/2011      4/22/2011      154 -- only one thru date with no follow-ups so no re-admittance (count of 0)


331      2/11/2011      2/14/2011      87 -- here, sub discharged on 2/14
331      8/2/2011                       8/6/2011                     110-- and re-admitted on 8/2/11

(count of 1)
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
this is wonderful thank you -- if I could just impose for where and how to add in the days_difference (READMIT_DAYS)?

SELECT
    c1.sub#
, SUM(1) AS Readmission_Count
, READMIT_DAYS = ...
, C1.START
, C1.THRU
, C1.CLAIM#
, MAX(C1.LINE#) AS MAXLINE#
, TOT_INPT_DAYS = CASE WHEN (C1.INPT ='Y') THEN C1.LOS -- CALC. BY DST      
                 WHEN (C1.INPT <> 'N') THEN 0  -- SHOULDN'T BE ANY
                   ELSE C1.LOS END --IF NO DISCH. DT. CONSIDERED NOT INPT. BUT ABOVE
FROM CLAIMS_CTE AS c1
WHERE EXISTS(
    SELECT 1
    FROM CLAIMS_CTE AS c2
    WHERE
        c2.sub# = c1.sub# AND
        c2.row_num = c1.row_num + 1 AND
        c2.start > c1.thru
    )
GROUP BY c1.sub#
, C1.START
, C1.THRU
, C1.CLAIM#
, C1.INPT
, C1.LOS
Are you trying to figure out how many days between the re-admittances?

SELECT
    ...
    DATEDIFF(DAY, c1.thru, c2.start) AS READMIT_DAYS --[?]
FROM
    ...


You might need SUM(DATEDIFF(...))
I try the DATEDIFF but it tells me
The multi-part identifier "C2.START" could not be bound...
OOPS, sorry, quite right.

Try this instead:

DATEDIFF(DAY, c1.thru, (SELECT c3.start FROM CLAIMS_CTE c3 WHERE c3.sub# = c1.sub# AND c3.row_num = c1.row_num + 1)) AS READMIT_DAYS --[?]
ok but I get 0 rows returned...
Hmm, so this:


;WITH CLAIMS_CTE AS (
    SELECT
        *, ROW_NUMBER() OVER ( PARTITION BY sub# ORDER BY thru ) AS row_num
    FROM #CLAIMS
)
SELECT
    c1.sub#, SUM(1) AS Readmission_Count,
    DATEDIFF(DAY, c1.thru, (SELECT c3.start FROM CLAIMS_CTE c3 WHERE c3.sub# = c1.sub# AND c3.row_num = c1.row_num + 1)) AS READMIT_DAYS
FROM CLAIMS_CTE AS c1
WHERE EXISTS(
    SELECT 1
    FROM CLAIMS_CTE AS c2
    WHERE
        c2.sub# = c1.sub# AND
        c2.row_num = c1.row_num + 1 AND
        c2.start > c1.thru
    )


was returning rows before the DATEDIFF was added and now it's not?  
Odd, I don't see how the added DATEDIFF could cause fewer rows.
not sure why is occurring...I will work on it some more -- thanks for your help!