SQL Server Performance

Overwhelming Query Logic

Discussion in 'General Developer Questions' started by jtyson, Aug 28, 2003.

  1. jtyson New Member

    I am trying to get a handle on where to start with this, and could really use some help.

    I have a dataset consisting of 2 1/2 years of medical claims data. I have to obtain from this data (1) a count of new patient treatment episodes, and (2) visits per treatment episode, broken down by quarter, diagnosis, and age group.

    A treatment episode has a distinct beginning and end, as defined by the initial evaluation when the patient is first diagnosed, and a subsequent 30-day "clean period" during which the patient doesn't have any dates of service.

    The initial evaluation date is identified by:
    1. Procedure Code = 97001 or 97003; OR
    2. First date of service for patient where no previous dates of service in last 30 days (Procedure Code does not have to = 97001 or 97003 in this case)

    End of treatment episode is identified by clean period as described above.

    A patient can be treated for multiple diagnoses during a period of time. Different diagnoses represent different treatment episodes. Diagnosis Code can be used to help identify different treatment episodes for same patient.

    If treatment episode crosses multiple quarters, the data for the entire treatment episode belongs in the quarter the initial evaluation occurred.

    Also, there is the complication of claims reversals, indicated by a negative number value in Unit Count column. Each date of service will have one or more positive units; if the claim is reversed there will be a duplicate record except the Unit Count will be negative. When determining number of new patient treatment episodes and visits per treatment episode, this has to be taken into account so that these counts are not inflated.

    Relevant data fields:

    MEMBERID (unique patient identifier)
    DATE OF SERVICE
    PROCEDURE CODE
    DIAGNOSIS CODE
    UNIT COUNT

    The analysis time being given for the is only a number of weeks; I am not sure I want to go so far as to write a program to slice and dice this data (I am also not sure if I have the expertise to either). The only thought I have, is if I can assign each treatment episode a unique identifier and link related visits together. It would be a cake walk from that point on.

    If someone could offer some advice I would really appreciate it - this criteria makes my head swim. If further clarification is needed please respond and I will do my best to provide clearer information.

    Thank you,

    John
  2. Twan New Member

    Is this a one off exercise or an ongoing requirement?<br /><br />I'd say tying the events together is the first step. <br /><br />for each visit get the next visit greater than it<br /><br />create table #t(<br /> id int identity not null,<br /> memberid int not null,<br /> date_of_service datetime not null,<br /> procedure_code varchar(10) not null,<br /> diagnosis_code varchar(10) not null,<br /> unit_count int not null,<br /> next_date datetime null,<br /> visitid int not null<br />)<br /><br />-----------------------------------------<br />-- get the date of the next visit <br />-- to allow us to tie visits together<br />-----------------------------------------<br /><br />insert into #t(<br /> memberid,<br /> date_of_service,<br /> procedure_code,<br /> diagnosis_code,<br /> unit_count,<br /> next_date <br />)select memberid,<br /> date_of_service,<br /> procedure_code,<br /> diagnosis_code,<br /> unit_count,<br /> next_date = (select min( v2.date_of_service )<br /> from visit v2<br /> where v1.memberid = v2.memberid<br /> and v1.date_of_service &lt; v2.date_of_service ))<br />from visit v1<br /><br />------------------------------------------<br />-- update the start of each visit<br />------------------------------------------<br /><br />update #t<br />set visit_id = id<br />where procedure_code in (97001 97003)<br />or isnull( datediff( day, date_of_service, next_date ), 31 ) &gt; 30<br /><br />------------------------------------------<br />-- now update each subsequent date of service<br />-- for the same visit<br />------------------------------------------<br /><br />set @rowcount = 1<br />while @rowcount &gt; 0<br />begin<br /> update t1<br /> set visit_id = t2.visit_id<br /> from #t t1<br /> inner join #t t2<br /> on t1.memberid = t2.memberid<br /> and t1.date_of_service = t2.next_date<br /><br /> set @rowcount = @@rowcount<br />end<br /><br />now that things are tied to visitid you should be able to issue selects to get your answers<br /><br />the first one is presumably a sum of unit_count (not sure how the refunds work into this...)<br />the second one a count(*)/count(distinct visitid) grouped by quarter, diagnosis, and age group<br /><br />this is just off the top of my head, not tested at all, but gives you the rough outline.<br /><br />I don't know how refunds fit since presumably a refund should be regarded as its own visit and should force a new visit to be started the next time the member is seen (regardless of whether it has been 30 days or not)...?<br /><br />Very hard to work this out without knowing much about your business/data or actual requirement <img src='/community/emoticons/emotion-1.gif' alt=':)' /><br /><br />Cheers<br />Twan

Share This Page