rachel_query_qualtrics.sql

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;