britpopfan74
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
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
>> 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?
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?
ASKER
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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(...))
SELECT
...
DATEDIFF(DAY, c1.thru, c2.start) AS READMIT_DAYS --[?]
FROM
...
You might need SUM(DATEDIFF(...))
ASKER
I try the DATEDIFF but it tells me
The multi-part identifier "C2.START" could not be bound...
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 --[?]
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 --[?]
ASKER
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.
;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.
ASKER
not sure why is occurring...I will work on it some more -- thanks for your help!
ASKER
deid-demo-data09172012.xlsx