Your example is working the way I wanted and it is what I need. Thanks.
However I have not figured out how to implement the same logics in my project. I need detailed explaination. Here is my code which does not work.
OLEDB SQL server connection string
Asthmap_patients:
load *;
sql
select *
from cp_medicine.zFluShot;
OLEDB oracle connection sttring
ImmunizationTemp:
LOAD *;
SQL
select distinct case when d.name like '%INFLUENZA%' then 'Y' else 'N' end as FluVaccine, c.immune_date as IMMUNE_DATE,
to_char(c.immunization_time,'MM/DD/YYYY HH24:MI:SS') as immune_time
,b.pat_name as Patient, b.pat_mrn_id AS MRN, c.DOSE, C.IMMNZTN_STATUS_C, C.DEFER_REASON_C, d.name as Vaccine
,a.hsp_account_id as HospitalAccountID
from pat_enc_hsp a
inner join patient b on a.pat_id = b.pat_id
left join Immune c on c.pat_id = a.Pat_id
left join clarity_immunzatn d on d.IMMUNZATN_ID = c.IMMUNZATN_ID
where C.IMMUNZATN_ID in (select IMMUNZATN_ID from CLARITY_IMMUNZATN where name like 'INFLUENZA%')
and C.immune_date > to_date('09/17/2013', 'mm/dd/yyyy') and c.IMMNZTN_STATUS_C <> 2;
left join (Asthmap_patients) load
HOSPITALACCOUNTID,
admitdts,
DischargeDTS,
admitdt,
DischargeDT,
YearMonth,
patient
Resident ImmunizationTemp;
left join (Asthmap_patients)
load HOSPITALACCOUNTID, if(isnull(IMMUNE_DATE),1,0) as Flag Resident Asthmap_patients;
Immunization:
Load
HOSPITALACCOUNTID,
IMMUNE_DATE,
FLUVACCINE,
if(FLUVACCINE='Y',1,0) as BlnFluShot,
//if(IMMUNE_DATE <admitdts or IMMUNE_DATE > DischargeDTS, 0, 1)as InHospital,
if(IMMUNE_DATE <admitdt, 'Before', if(IMMUNE_DATE<=DischargeDT and IMMUNE_DATE>admitdt, 'InHospital','After'))as FlushotTime
Resident ImmunizationTemp
where not isnull(admitdts);
//where IMMUNE_DATE >=admitdts and IMMUNE_DATE <=DischargeDTS;
drop table ImmunizationTemp;
