Created
December 10, 2021 14:52
-
-
Save drio/48bbb56c97e4f724d7af79263edb1c5f to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Hi, | |
This is actually populating a view in the DW, not sure what format it’s expecting. Question for Mike or Larry perhaps? I don’t think anything fails until Tableau tries to create the extract, so it’s not the DW getting angry about the format, it’s Tableau. | |
SQL below: | |
create or replace view vaccine_vw | |
(email, vax1, vax_dt1, vax_dt2, response_date, emplid, utln, last_name, first_name, pref_phone, tufts_email, person_type, campus, employee_home_dept_ld, school, academic_career, risk, deptid, supervisor_name, employee_jobtitle, hire_dt, type_of_entry, date_approved, vax_source) | |
as | |
with | |
qualtrics_vaccine_data as --qualtrics vax | |
(select /*+ materialize */ | |
case | |
when q18_display_email is null | |
then trim(lower(q20_email)) | |
else lower(q18_display_email) | |
end email, | |
Q21_vaccine_type AS VACCINE, | |
Q24_date AS FIRST_SHOT, | |
Q26_date AS SECOND_SHOT, | |
response_date AS RESPONSE_DATE | |
from covid.s_qualtrics_covid_vaccine_verification_v2 q1 | |
where to_date(response_date||' '||response_time, 'yyyy-mm-dd hh24:mi:ss') = (select MAX(to_date(response_date||' '||response_time, 'yyyy-mm-dd hh24:mi:ss')) | |
from covid.s_qualtrics_covid_vaccine_verification_v2 q2 | |
where ( q1.q18_display_email is not null and q1.q18_display_email = q2.q18_display_email) | |
or | |
( q1.q18_display_email is null and q1.q20_email = q20_email) | |
) ) , | |
manual_data as --manual spreadsheet vax | |
(select distinct lower(email) email, | |
nq.utln, | |
date_first_shot, | |
date_second_shot, | |
vaccine_type, | |
date_approved | |
from covid.VAC_NOT_IN_QUALTRICS nq, | |
covid.person_risk_vw pr --change to plus??? | |
where nq.utln = pr.utln), | |
other_data as | |
(select /*+ materialize */ emplid | |
from | |
(select NVL(emplid, -99999) emplid | |
from covid.vac_not_in_qualtrics | |
where utln is not null | |
union | |
select NVL(emplid, -99999) | |
from covid.vaccine_declination | |
where (utln is not null AND date_approved is not null) | |
union | |
select NVL(pr.emplid,-99999) | |
from covid.VACCINE_COMPLIANCE_EXCEPT ex, | |
covid.person_risk_vw pr | |
where pr.utln = ex.utln | |
union | |
select NVL(emplid, -99999) | |
from covid.saha_vaccine_data | |
where | |
(tfs_saha_imm_vndr in ('PFZ','MOD','JJ','AZ', 'COV', 'NOV', 'AZ', 'VC', 'CSH', 'OTH') | |
OR tfs_saha_imm_exmpt in ('O', 'R', 'M')) --grab immunized AND exemptions | |
and TFS_SAHA_CURR_PREV = 'CURR' | |
and Immunization = 'COV19' | |
and TFS_SAHA_IMM_STAT = 'C' | |
union | |
select NVL(pr.emplid, -1) | |
from qualtrics_vaccine_data q, | |
covid.person_risk_plus_vw pr | |
where lower(q.email) = lower(pr.email))), | |
medicat as | |
(select student_id, | |
cvx_code, | |
dose1, | |
dose2 | |
from covid.medicat_vax_import | |
where covid_vaccination_status = 'Complete') | |
-- MEDICAT table & create denominator w/ covid.VAX_ELIG_COMB | |
--moving medicat 1st screwed up vaccine column name, fixed in DS | |
select distinct pr.email, | |
decode(m.cvx_code, 207,'Moderna', | |
208, 'Pfizer', | |
213, 'Unspecified', | |
210, 'AstraZeneca', | |
212, 'Janssen') vax1, | |
to_char(m.dose1,'yyyy-mm-dd'), | |
to_char(m.dose2,'yyyy-mm-dd'), | |
NULL AS response_date, --not in view yet | |
pr.emplid, | |
pr.utln, | |
pr.last_name, | |
pr.first_name, | |
pr.pref_phone, | |
pr.email as tufts_email, | |
pr.person_type, | |
pr.campus, | |
pr.employee_home_dept_ld, | |
pr.school, | |
pr.academic_career, | |
pr.risk, | |
pr.deptid, | |
pr.supervisor_name, | |
pr.employee_jobtitle, | |
pr.hire_dt, -----10/25/21 | |
null type_of_entry, | |
null date_approved, | |
decode(nvl(cvx_code,'NoSource'), 'NoSource','NoSource', 'Medicat') as vax_source | |
from covid.vax_elig_comb d | |
FULL OUTER JOIN medicat m | |
ON (m.student_id = d.emplid), | |
covid.person_risk_plus_vw pr | |
where (pr.emplid=d.emplid or pr.emplid = m.student_id) | |
and d.emplid not in ( select emplid from other_data ) | |
union --vax spreadsheet | |
select distinct m.email, | |
vaccine_type, | |
date_first_shot, | |
date_second_shot, | |
date_approved AS response_date, | |
pr.emplid, | |
pr.utln, | |
pr.last_name, | |
pr.first_name, | |
pr.pref_phone, | |
pr.email as tufts_email, | |
pr.person_type, | |
pr.campus, | |
pr.employee_home_dept_ld, | |
pr.school, | |
pr.academic_career, | |
pr.risk, | |
pr.deptid, | |
pr.supervisor_name, | |
pr.employee_jobtitle, | |
pr.hire_dt, -----10/25/21 | |
null type_of_entry, | |
null date_approved, | |
'Vax Spreadsheet' as vax_source | |
from manual_data m, | |
covid.person_risk_plus_vw pr | |
where m.utln = pr.utln | |
union --qualtrics | |
select distinct q.email email, | |
vaccine, | |
first_shot, | |
second_shot, | |
q.response_date, | |
pr.emplid, | |
pr.utln, | |
pr.last_name, | |
pr.first_name, | |
pr.pref_phone, | |
pr.email as tufts_email, | |
pr.person_type, | |
pr.campus, | |
pr.employee_home_dept_ld, | |
pr.school, | |
pr.academic_career, | |
pr.risk, | |
pr.deptid, | |
pr.supervisor_name, | |
pr.employee_jobtitle, | |
pr.hire_dt, -----10/25/21 | |
null type_of_entry, | |
null date_approved, | |
'Qualtrics' as vax_source | |
from qualtrics_vaccine_data q, | |
covid.person_risk_plus_vw pr | |
where lower(q.email) = lower(pr.email) | |
AND LOWER(q.email) NOT IN ('none') --added 7/30/21 | |
union --SAHA vaccines | |
select distinct email, | |
tfs_saha_imm_vndr, | |
to_char(tfs_saha_imm_dt1,'yyyy-mm-dd'), | |
to_char(tfs_saha_imm_dt2,'yyyy-mm-dd'), | |
to_char(last_update_dt) AS response_date, --last time record updated, issue with date format | |
pr.emplid, | |
pr.utln, | |
pr.last_name, | |
pr.first_name, | |
pr.pref_phone, | |
pr.email as tufts_email, | |
pr.person_type, | |
pr.campus, | |
pr.employee_home_dept_ld, | |
pr.school, | |
pr.academic_career, | |
pr.risk, | |
pr.deptid, | |
pr.supervisor_name, | |
pr.employee_jobtitle, | |
pr.hire_dt, -----10/25/21 | |
tfs_SAHA_IMM_EXMPT AS type_of_entry, | |
tfs_SAHA_EXMPT_EXP AS date_approved, --really the day it expires in SAHA | |
'SAHA' as vax_source | |
from covid.saha_vaccine_data s, | |
covid.person_risk_plus_vw pr | |
where pr.emplid = s.emplid | |
and (tfs_saha_imm_vndr in ('PFZ','MOD','JJ','AZ', 'COV', 'NOV', 'AZ', 'VC', 'CSH', 'OTH') | |
OR tfs_saha_imm_exmpt in ('O', 'R', 'M')) --grab immunized AND exemptions | |
and TFS_SAHA_CURR_PREV = 'CURR' | |
and Immunization = 'COV19' | |
and TFS_SAHA_IMM_STAT = 'C' | |
union --declines/exemptions from spreadsheet | |
select distinct lower(pr.email) email, | |
null, | |
null, | |
null, | |
null, | |
pr.emplid, | |
pr.utln, | |
pr.last_name, | |
pr.first_name, | |
pr.pref_phone, | |
pr.email as tufts_email, | |
pr.person_type, | |
pr.campus, | |
pr.employee_home_dept_ld, | |
pr.school, | |
pr.academic_career, | |
pr.risk, | |
pr.deptid, | |
pr.supervisor_name, | |
pr.employee_jobtitle, | |
pr.hire_dt, -----10/25/21 | |
type_of_entry, | |
date_approved, | |
'Exemptions Spreadsheet' as vax_source | |
from (select v1.utln, v1.type_of_entry, v1.date_approved ---6/29/21 fix for duplicates, and null dates | |
from covid.vaccine_declination v1 | |
where v1.date_approved = (select max(v2.date_approved) | |
from covid.vaccine_declination v2 | |
where v1.utln = v2.utln)) v, | |
covid.person_risk_plus_vw pr | |
where v.utln = pr.utln; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment