January 29, 2008 at 6:28 am
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
January 29, 2008 at 10:07 am
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
January 30, 2008 at 6:57 am
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