• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 855
  • Last Modified:

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
0
britpopfan74
Asked:
britpopfan74
  • 6
  • 5
1 Solution
 
britpopfan74Author Commented:
0
 
Scott PletcherSenior DBACommented:
>> 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?
0
 
britpopfan74Author Commented:
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)
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Scott PletcherSenior DBACommented:
;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, c1.[...]
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
    )
0
 
britpopfan74Author Commented:
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
0
 
Scott PletcherSenior DBACommented:
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(...))
0
 
britpopfan74Author Commented:
I try the DATEDIFF but it tells me
The multi-part identifier "C2.START" could not be bound...
0
 
Scott PletcherSenior DBACommented:
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 --[?]
0
 
britpopfan74Author Commented:
ok but I get 0 rows returned...
0
 
Scott PletcherSenior DBACommented:
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.
0
 
britpopfan74Author Commented:
not sure why is occurring...I will work on it some more -- thanks for your help!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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