Solved

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

Posted on 2013-01-25
5
287 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 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

Comparison of Amazon Drive, Google Drive, OneDrive

What is Best for Backup: Amazon Drive, Google Drive or MS OneDrive? In this free whitepaper we look at their performance, pricing, and platform availability to help you decide which cloud drive is right for your situation. Download and read the results of our testing for free!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

688 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