Solved

# Total count of re-admission visits

Posted on 2012-09-17
844 Views
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.

--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
Question by:britpopfan74

Author Comment

0

LVL 68

Expert Comment

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

Author Comment

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

LVL 68

Accepted Solution

;WITH CLAIMS_CTE AS (
SELECT
*, ROW_NUMBER() OVER ( PARTITION BY sub# ORDER BY thru ) AS row_num
FROM #CLAIMS
)

SELECT
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

Author Comment

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#
, 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

LVL 68

Expert Comment

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

Author Comment

I try the DATEDIFF but it tells me
The multi-part identifier "C2.START" could not be bound...
0

LVL 68

Expert Comment

OOPS, sorry, quite right.

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

Author Comment

ok but I get 0 rows returned...
0

LVL 68

Expert Comment

Hmm, so this:

;WITH CLAIMS_CTE AS (
SELECT
*, ROW_NUMBER() OVER ( PARTITION BY sub# ORDER BY thru ) AS row_num
FROM #CLAIMS
)
SELECT
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

Author Comment

not sure why is occurring...I will work on it some more -- thanks for your help!
0

## Featured Post

### Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.