A Set-Based solution instead of multiple passes?

  • Here's the problem:

    I have medical claim data. Each record tells me the patient's medical number (MIS), the date of service and the type of provider (1 = medical; 2 = therapy). I need to report on the total number of patients receiving services since 10-1-2007. Additionally, I need to report the total number of med-only clients as well as therapy-only clients since 10-1-2007.

    Med-only is defined as follows: For each medical service during the period, there can be no therapy services 180 days prior to the medical service. Therapy-only is defined similarly as having had no medical services in the preceding 180 days.

    Getting the total number of patients is trivial, of course. However, I'm having trouble getting the count of med-only clients and therapy-only clients. Now I've done this creating temp tables, running update queries and basically approaching it procedurally. However, I'm betting that there's a more elegant, set-based approach. And that's the solution that I'm looking for.

    I've included some example code to highlight what we'd expect. Based on the data that I've included, (1) Patient 12345 would not be counted because no services fell on or after 10/1/2007; (2) 15142 would get counted as a patient. And though the services from 1-18-2008 and 12-21-2007 do not have any therapy services in the preceding 180 days, this patient would still not be counted as med-only because of the 6-20-07 service that falls within 180 days of the 10-5-07 service (as well as the other services up through 11-14-07); (3) 22222 would be considered med-only because of the service falling after 10-1-07 and because there are no therapy services in the preceding 180 days; (4) 33333 is a therapy-only based on same logic as #3; (5) 99999 is neither med-only nor therapy-only as there's a mix of services since 10-1-07.

    Any help, greatly appreciated!

    -Steve Lord

    -- sample data

    CREATE TABLE #Serv (

    MIS int

    , ServDate datetime

    , Type tinyint

    )

    INSERT INTO #Serv

    VALUES(12345, '7-1-2007', 1)

    INSERT INTO #Serv

    VALUES(12345, '8-1-2007', 1)

    INSERT INTO #Serv

    VALUES(12345, '9-1-2007', 1)

    INSERT INTO #Serv

    VALUES(12345, '10-1-2006', 2)

    -- Med-only services

    INSERT INTO #Serv

    VALUES(22222, '7-15-2007', 1)

    INSERT INTO #Serv

    VALUES(22222, '8-15-2007', 1)

    INSERT INTO #Serv

    VALUES(22222, '9-15-2007', 1)

    INSERT INTO #Serv

    VALUES(22222, '10-15-2007', 1)

    -- Therapy-only services

    INSERT INTO #Serv

    VALUES(33333, '7-15-2007', 2)

    INSERT INTO #Serv

    VALUES(33333, '8-15-2007', 2)

    INSERT INTO #Serv

    VALUES(33333, '9-15-2007', 2)

    INSERT INTO #Serv

    VALUES(33333, '10-15-2007', 2)

    -- The problem case:

    INSERT INTO #Serv

    VALUES(15142, '01-18-2008', 1)

    INSERT INTO #Serv

    VALUES(15142, '12-21-2007', 1)

    INSERT INTO #Serv

    VALUES(15142, '11-14-2007', 1)

    INSERT INTO #Serv

    VALUES(15142, '10-24-2007', 1)

    INSERT INTO #Serv

    VALUES(15142, '10-05-2007', 1)

    INSERT INTO #Serv

    VALUES(15142, '09-05-2007', 1)

    INSERT INTO #Serv

    VALUES(15142, '07-18-2007', 1)

    INSERT INTO #Serv

    VALUES(15142, '06-20-2007', 1)

    INSERT INTO #Serv

    VALUES(15142, '06-20-2007', 2)

    -- Another problem case:

    INSERT INTO #Serv

    VALUES(99999, '01-18-2008', 2)

    INSERT INTO #Serv

    VALUES(99999, '12-21-2007', 1)

    INSERT INTO #Serv

    VALUES(99999, '11-14-2007', 1)

    INSERT INTO #Serv

    VALUES(99999, '10-24-2007', 1)

    SELECT * FROM #serv ORDER BY MIS, ServDate DESC

  • Here is solution:

    declare @PeriodStartDtdatetime

    ,@PeriodEndDtdatetime

    ,@MedicalServiceint

    ,@TherapyService int

    ,@OtherServiceDays int

    set@PeriodStartDt= '2007-10-01'

    set@PeriodEndDt= @PeriodStartDt + 180

    set@MedicalService= 1

    set@TherapyService = 2

    set@OtherServiceDays = 180

    /*

    selectcount(*)as AnyServiceCnt

    ,SUM(CASE when MedicalWOTherapy.MIS is not null then 1 else 0 end ) as Medical_WO_Therapy_Cnt

    ,SUM(CASE when TherapyWOMedical.MIS is not null then 1 else 0 end ) as Therapy_WO_Medical_Cnt

    */

    selectPatients.MIS

    ,CASE when MedicalWOTherapy.MIS is null and TherapyWOMedical.MIS is null then 'Medical and Therapy'

    when MedicalWOTherapy.MIS is null then 'Medical Only'

    else 'Therapy Only'

    end as CarProvided

    from(select distinct MIS

    from#serv

    whereServDate between @PeriodStartDt and @PeriodEndDt

    ) as Patients

    left outer join

    (selectMedicalService.MIS

    from(select MIS

    ,min(ServDate)as MedicalServiceOldestDt

    from#serv

    whereType = @MedicalService

    andServDate between @PeriodStartDt and @PeriodEndDt

    group by MIS

    ) as MedicalService

    wherenot exists

    (select1

    from #serv

    where#serv.Type= @TherapyService

    and#serv.MIS= MedicalService.MIS

    and#serv.ServDate

    between MedicalService.MedicalServiceOldestDt - @OtherServiceDays

    and @PeriodEndDt

    )

    ) As MedicalWOTherapy

    on MedicalWOTherapy.MIS = Patients.MIS

    left outer join

    (selectTherapyService.MIS

    from(select MIS

    ,min(ServDate)as TherapyServiceOldestDt

    from#serv

    whereType = @TherapyService

    andServDate between @PeriodStartDt and @PeriodEndDt

    group by MIS

    ) as TherapyService

    wherenot exists

    (select1

    from #serv

    where#serv.Type= @MedicalService

    and#serv.MIS= TherapyService.MIS

    and#serv.ServDate

    between TherapyService.TherapyServiceOldestDt - @OtherServiceDays

    and @PeriodEndDt

    )

    ) As TherapyWOMedical

    on TherapyWOMedical.MIS = Patients.MIS

    SQL = Scarcely Qualifies as a Language

  • that is my solution

    select distinct s1.MIS,'only' + '-' + case when type=1 then 'medical' else 'therapy' end State

    from #serv s1

    where not exists (select 1 from #serv s2 where s1.MIS=s2.MIs and s1.type<>s2.type and datediff(day,s1.servdate,s2.servdate)<180)

    and exists (select 1 from #serv t where s1.MIS=t.MIS and t.servdate>='20071001')

    union all

    select distinct s1.MIS,'Patient' State

    from #serv s1

    left join #serv s2 on s1.MIS=s2.MIS and s1.type<>s2.type

    where datediff(day,s1.servdate,s2.servdate)<180

    and exists (select 1 from #serv t where s1.MIS=t.MIS and t.servdate>='20071001')

    http://transactsql.blogspot.com/

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply