Solved

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

Posted on 2013-01-25
5
282 Views
Last Modified: 2013-01-28
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
0
Comment
Question by:britpopfan74
  • 3
  • 2
5 Comments
 
LVL 35

Expert Comment

by:David Todd
ID: 38820371
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
 

Author Comment

by:britpopfan74
ID: 38820419
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
 
LVL 35

Accepted Solution

by:
David Todd earned 500 total points
ID: 38820446
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
 

Author Comment

by:britpopfan74
ID: 38826640
Sorry David I didn't have access this weekend to the data -- checking this in a bit and will get back to you.
0
 

Author Closing Comment

by:britpopfan74
ID: 38826778
Thank you very much!
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question