Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-01-25
5
Medium Priority
?
289 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

604 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