Hello,
I am needing help with converting the below Oracle script in to a SQL script. Can anyone help me with this? Or does anyone have a SQL script that would give me the following information?
1. All Medicare-as-primary patients, including
standard Medicare and Medicare replacement products, excluding patients who
only have Medicare products as secondary to their primary coverage.
2. If the EMR prohibits data extraction based on
Medicare-as-primary payer, extract data for all patients over age 65 instead.
3. The most recent visit for every patient seen
in the past 24 months.
4. Include all ICD9 diagnoses associated with the
active problem list for each patient, as reflected in the most recent
visit. ICD-9 codes, when retrieved from the EHR, should be in TEXT format
(to pull in the 4th and 5th digits, such as xxx.xx), not in numerical format
(which may round to the nearest whole number, such as xxx). We need to identify
the current state highest-level specificity for each ICD code assigned to each
patient.
Oracle Script:
select trunc(sysdate)
"Date", sub1.lname "Provider Last Name", sub1.fname
"Provider First Name", P.MEDRECNO "MRN"
,(trunc((trunc(sysdate) - p.DateOfBirth)/365)) "Patient Age",
p.DateOfBirth "Patient
DOB",
case
when p.sex = 'M' then 'Male'
when p.sex = 'F' then 'Female'
else ''
end as "Patient Sex",
ins.ins "Primary
Insurance",
sub1.lastvisdate "Most
Recent Visit",
diag.code "ICD9 Diagnosis
Code",
diag.des "ICD9 Diagnosis
description",
p.lastname "Patient
Lastname",
p.firstname "Patient
Firstname"
from person p,locreg l,
--Insurance Company - List of
Medicare insurance provided below are used
--BRAVO HEALTH
TEXAN PLUS (741107) AETNA MEDICARE PFFS
AARP/SECURE HORIZONS MEDICARE COMPLETE
--SECURE HORIZONS
DIRECT AMERIVANTAGE HUMANA CHOICE PPO
MEDICARE WELLCARE SECURE
HORIZONS
--CARE IMPROVEMENT
PLUS CARE IMPROVEMENT PLUS RAILROAD
MEDICARE BLUE MEDICARE PPO
MEDICARE
--
EVERCARE/MEDICARE AARP/ MEDICARE COMPLETE
AARP/ MEDICARE COMPLETE BLUE MEDICARE PPO
ADVANTRA FREEDOM
--AARP MEDICARE
COMPLETE WELLCARE TODAY'S OPTIONS
(
select p.pid,B.SEARCHNAME ins
from person p, insuranc i, insureco c, business b
where p.pid=i.PID
and i.INSPLANID=C.INSPLANID
and c.BUSID = b.BUSID
and I.PSISTATE = 'P'
group by p.pid, B.SEARCHNAME
)ins,
--diagnosis
(
select
p.pid, PROB.CODE code, PROB.DESCRIPTION des
from problem prob, person p
where prob.pid = p.pid
and prob.xid = 100000000000000000000000000000000000
and PROB.STOPDATE = to_date('12/31/4700','mm/dd/yyyy')
group by p.pid, PROB.CODE,PROB.DESCRIPTION
)diag,
--
Get Last visit date and provider
(
select p.pid,
d.lastvisdate,d.lname, d.fname
from person p,
(select p.pid,
convert_id_to_date(d.clinicaldate) lastvisdate,u.lastname lname, u.firstname
fname,
rank() over
(PARTITION BY p.pid order
by p.pid, convert_id_to_date(d.clinicaldate) Desc) AS S_Rank
from
person p,document d, doctypes dt, usr u, jobtitle j, locreg l
where p.pid=d.pid
and D.DOCTYPE=DT.DTID
and D.USRID = U.PVID
and U.JOBTITLE = J.JTID
and U.HOMELOCATION = L.LOCID
and j.description in ('Physician
- Faculty')
and U.STATUS = 'A'
and DT.DESCRIPTION in ('Office
Visit', 'Medicaid Visit', 'Medicare Visit')
and d.xid =
100000000000000000000000000000000000
and
convert_id_to_date(d.clinicaldate) >= trunc((add_months(sysdate,
-24)))
order by p.pid,
convert_id_to_date(d.clinicaldate) Desc )d
where S_Rank = 1
and p.pid=d.pid(+)
)sub1
Where
p.pid = sub1.pid
and p.pid=ins.pid(+)
and p.pid= diag.pid(+)
and P.HOMELOCATION = L.LOCID
and (upper(p.LastName) <>
'SBCTEST' and upper(p.LastName) <> 'TEST' and upper(p.LastName) <>
'MOUSE' and upper(p.LastName) <> 'PATIENT' and upper(p.LastName) <>
'HYPERTENSION' and
upper(p.LastName) <> 'LIPID')
and P.ISPATIENT='Y'
and P.PSTATUS = 'A'
and trunc(sub1.lastvisdate) >= trunc((add_months(sysdate,
-24))) --
Visit in the last 2 years
Thanks for looking,
Jessica
