Adding SQL to check the primary diagnosis from the row above to existing code

Dear Experts,

I have existing code that folks on this forum have helped me with that helps me find which patients have been re-admitted with a specified time frame.

Now, I want to add in criteria to check if the patient had the SAME diagnosis while admitted during that time frame.

It seems like it could be handled by an inner join and/or a CTE -- what's getting me hung up is how to look to the row immediately preceding the "thru" or discharge date.

I'm attaching the code I have and can attach data if it helps; thank you in advance


---------------------------------
--3--BEGIN RE-ADMIT CALC -- NEED TO LOOKBACK TO SAME OR DIFF DX ABOVE
---------------------------------
--DROP TABLE #READMITS
--SELECT * FROM #READMITS

;WITH CLAIMS_CTE AS (   --C1
    SELECT
        *, ROW_NUMBER() OVER (PARTITION BY mem_no ORDER BY thru) AS row_num
    FROM #CLAIMS
)
SELECT C1.MEM_NO
  , C1.SUB#
  , C1.HEALTH_ID
  , C1.REGION
  , C1.LAST_NAME
  , C1.FIRST_NAME
  , C1.DOB
  , C1.AGE
  , C1.GENDER
  , C1.START
  , C1.THRU
  , C1.CLAIM#
  , C1.PRIMARY_DX
  , TOT_INPT_DAYS = ABS(DATEDIFF(DD, C1.THRU, C1.START)) + 1
  , SUM(1) AS Readmission_Count
  , DATEDIFF(DAY, c1.thru,
      (SELECT c3.start FROM CLAIMS_CTE c3
            WHERE
                  c3.mem_no = c1.mem_no
                  AND c3.row_num = c1.row_num + 1)) AS DAYS_LATER_READMIT -- USE FROM HERE
, SUM(C1.TOTAL_PAY) AS TOTAL_PD
INTO #READMITS
FROM CLAIMS_CTE AS c1
WHERE EXISTS(
    SELECT 1
    FROM CLAIMS_CTE AS c2
    WHERE
        c2.mem_no = c1.mem_no
            AND c2.row_num = c1.row_num + 1
        AND c2.start > c1.thru
    )  
GROUP BY c1.mem_no
  , C1.SUB#
  , C1.HEALTH_ID
  , C1.REGION
  , C1.LAST_NAME
  , C1.FIRST_NAME
  , C1.DOB
  , C1.AGE
  , C1.GENDER
, C1.START
, C1.THRU
, C1.CLAIM#
, C1.PRIMARY_DX
, C1.INPT
, C1.LOS
, c1.row_num
ORDER BY C1.LAST_NAME
, C1.FIRST_NAME

SELECT * FROM #READMITS

SELECT
        R1.MEM_NO, R1.CLAIM#, R1.PRIMARY_DX, R1.THRU
       , ROW_NUMBER() OVER (PARTITION BY R1.mem_no ORDER BY R1.PRIMARY_DX)  row_num
FROM #READMITS R1
britpopfan74Asked:
Who is Participating?
 
David ToddConnect With a Mentor Senior DBACommented:
Hi,

Any luck with some sample data? preferably a csv or excel file so I can easily import it?

Here's a refined guess

select
      -- do expand the necessary columns
      aa.*
from (
      select
            st1.HEALTH_ID
            , st1.primary_dx
            , st1.start
            , datediff(day, st1.start, st2.start ) as DaysElapsed
      from #READMITS st1
      -- self join
      inner join #READMITS st2
            on st2.Health_ID = st1.Health_ID
            and st2.primary_dx = st1.primary_dx
            and st2.CLAIM# <> st1.CLAIM# -- no assumptions about order entry, but prevent joining to self
      group by
            st1.Health_ID
            , st1.primary_dx
            , st1.start
) aa
where
      abs( aa.DaysElapsed ) < 30 -- days
;

HTH
  David
0
 
David ToddSenior DBACommented:
Hi,

Can you please clarify - do you care about the preceding admission, or any admission that is the same as this one within the timeframe? And does it have to match this diagnosis?

Say I'm particularly accident prone -
Admitted for breaking leg in June
Admitted for multiple contusions later in June
Admitted for breaking other leg in August
Admitted because of issues with first leg later in August
Admitted with fishhook in hand in September

In this case, I suggest that you are looking for the June broken leg, and subsequent issues in late August. Possibly including the second leg early August.

I hope that primary_dx is a coded number and not a user supplied string!

For me this is a simple query - self-join on the patient ID and diagnosis ID and then check the start dates to be within the requested range. With the patient ID, then go look up the rest of the details ...

Here is my take on the inner select, identifying the patients and diagnosis of interest.

select
    st1.HealthID
    , st1.primary_dx
from dbo.sometable st1
-- self join
inner join dbo.sometable st2
    on st2.Health_ID = st1.Health_ID
    and st2.primary_dx = st1.primary_dx
    and st2.claim != st1.claim -- no assumptions about order entry, but prevent joining to self
group by
    st1.Health_ID
having
    abs( datediff( day, st1.start, st2.start )) < 120 -- days
;

HTH
  David

PS Likely I've grossly over-simplified the issue, but need a significant sample to test and develop further
0
 
britpopfan74Author Commented:
Thanks David...the code almost works except for:

Column '#READMITS.START' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

Luckily the diagnosis isn't hard-coded and it's to see if a Medicare recipient was re-admitted within 30 days with the same dx which affects reimbursement

select
    st1.HEALTH_ID
    , st1.primary_dx
from #READMITS st1
-- self join
inner join #READMITS st2
    on st2.Health_ID = st1.Health_ID
    and st2.primary_dx = st1.primary_dx
    and st2.CLAIM# <> st1.CLAIM# -- no assumptions about order entry, but prevent joining to self
group by
    st1.Health_ID
   --, st1.start
having
    abs(datediff(day, st1.start, st2.start )) <= 30 -- days
0
 
britpopfan74Author Commented:
Sorry David I didn't have access this weekend to the data -- checking this in a bit and will get back to you.
0
 
britpopfan74Author Commented:
Thank you very much!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.