Solved

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

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now