with HR_demographics as 
(select distinct
  fld_employee_id as Employee_ID
  ,fld_legal_full_name as Employee_name
  ,fld_hire_date as Hire_Date
  ,fld_date_of_birth as Birthdate
  ,fld_work_schedule as Work_Schedule
  ,if(fld_official_last_date is not null
      ,1,0) as Attrition_Flag    
  ,if(fld_official_last_date is not null 
      and fld_separation_category = "Withdrawn"
      ,1,0) as Attrition_Withdrawn_Flag        
  ,if(fld_official_last_date is not null
      and fld_separation_category = "Resignation (Voluntary)"       
      ,1,0) as Attrition_Voluntary_Flag
  ,if(fld_official_last_date is not null 
      and fld_separation_category = "End of Contract"               
      ,1,0) as Attrition_EndOfContract_Flag
  ,if(fld_official_last_date is not null 
      and (fld_separation_category = "Termination (Involuntary)"
           or fld_separation_category = "Dismissal (Involuntary)")  
      ,1,0) as Attrition_Involuntary_Flag
  , if(fld_official_last_date is not null
      and fld_separation_category = "Death"
      ,1,0) as Attrition_Death_Flag
  ,fld_separation_date_learned as Separation_Notice_Date
  ,fld_separation_category as Separation_Category
  ,fld_official_last_date as Official_Last_Date
  ,DATEDIFF(YEAR, fld_date_of_birth, cast("{0}" as date)) AS Age
  ,fld_gender AS Gender
  ,fld_nationality AS Nationality
  , CASE fld_ethnicity
      WHEN "()" THEN NULL
    ELSE
    fld_ethnicity
  END
    AS Ethnicity,
    fld_company_code AS Country,
    CASE fld_department
      WHEN "()" THEN NULL
    ELSE
    fld_department
  END
    AS Department,
    CASE fld_functional_unit
      WHEN "()" THEN NULL
    ELSE
    fld_functional_unit
  END
    AS Functional_Unit,
    CASE fld_sub_unit
      WHEN "()" THEN NULL
    ELSE
    fld_sub_unit
  END
    AS Sub_Unit,
    CASE fld_location
      WHEN "()" THEN NULL
    ELSE
    fld_location
  END
    AS Work_Location,
    SUBSTRING(fld_job_code,1,2) AS Job_Grade,
    fld_job_category AS Job_Category,
    fld_contract_type AS Contract_Type,
    fld_tenure_months AS Tenure,
    fld_working_status AS Working_Status,
    fld_internal_job_title AS Job_Title,
    fld_probationary_status AS Probationary_Status,
    fld_probationary_period_end_date AS Probationary_End_Date
  FROM
    `tbl_hr_demographics_dm`),
education_table AS (
  SELECT
    fld_employee_id AS Employee_ID,
    MAX(Education_Level_Ordinal) AS Education_Level_Ordinal
  FROM (
    SELECT
      fld_employee_id,
      Education_level AS Education_Level_Nominal,
      CASE Education_level
        WHEN "PHD" THEN 14
        WHEN "Master's Degree" THEN 13
        WHEN "Advanced / Higher / Graduate / Post graduate Diploma" THEN 12
        WHEN "Post tertiary" THEN 11
        WHEN "Bachelor's Degree" THEN 10
        WHEN "Tertiary" THEN 10
        WHEN "Associate Degree" THEN 9
        WHEN "Diploma / Technical Degree" THEN 8
        WHEN "Professional qualification and other diploma" THEN 7
        WHEN "High Vocational" THEN 6
        WHEN "Pre-University" THEN 6
        WHEN "Vocational" THEN 6
        WHEN "Vocational School" THEN 6
        WHEN "Post secondary" THEN 5
        WHEN "Secondary" THEN 4
        WHEN "Secondary School" THEN 4
        WHEN "High School and below" THEN 3
        WHEN "Lower Secondary" THEN 2
        WHEN "Primary school" THEN 1
        WHEN "Other" THEN NULL
        WHEN NULL THEN NULL
      ELSE
      NULL
    END
      AS Education_Level_Ordinal,
      fld_field_study AS Education_Field_Of_Study
    FROM (
      SELECT
        fld_employee_id,
      IF
        (fld_qualification_level='Other'
          AND fld_field_study!='Other', 'Tertiary', fld_qualification_level) AS Education_level,
        fld_field_study
      FROM
        `tbl_hr_demographics_dm` ) )
  GROUP BY
    fld_employee_id ),
    training_table AS (
  SELECT
    CAST(Employee_ID AS string) AS Employee_ID,
    SUM(lnd_on_1mo) AS training_hr_on_1mo,
    SUM(lnd_on_2mo) AS training_hr_on_2mo,
    SUM(lnd_on_currentmo) AS currentMo_Lnd_hours
  FROM (
    SELECT
      Employee_ID,
      CASE
        WHEN lnd_from_hire_date='on_1mo' THEN SUM(Learning_Hours)
      ELSE
      0
    END
      AS lnd_on_1mo,
      CASE
        WHEN lnd_from_hire_date='on_2mo' THEN SUM(Learning_Hours)
      ELSE
      0
    END
      AS lnd_on_2mo,
      CASE
        WHEN lnd_from_hire_date='on_current_mo' THEN SUM(Learning_Hours)
      ELSE
      0
    END
      AS lnd_on_currentmo
    FROM (
      SELECT
        fld_employee_id AS Employee_ID,
        Learning_Completion_Month,
        fld_source_name AS Learning_System,
        fld_training_type AS Learning_Training_Type,
        SUM(fld_learning_hours) AS Learning_Hours,
        CASE        
          WHEN Learning_Completion_Month >= DATE_SUB(cast("{0}" as date), 30) THEN 'on_current_mo'
          WHEN Learning_Completion_Month <= DATE_ADD(fld_hire_date, 60)
        AND Learning_Completion_Month > DATE_ADD(fld_hire_date, 30) THEN 'on_2mo'
          WHEN Learning_Completion_Month <= DATE_ADD(fld_hire_date, 30) AND Learning_Completion_Month >= fld_hire_date THEN 'on_1mo'
        ELSE
        NULL
      END
        AS lnd_from_hire_date
      FROM (
        SELECT
          *,
        IF
          (fld_completion_month IS NULL, cast("{0}" as date), fld_completion_month) AS Learning_Completion_Month
        FROM
          `tbl_lnd_dm`
        WHERE
          fld_completion_month <= cast("{0}" as date) )
      WHERE
        fld_learning_hours >= 0
      GROUP BY
        fld_employee_id,
        Learning_Completion_Month,
        fld_source_name,
        fld_hire_date,
        fld_training_type )
		
    GROUP BY
      Employee_ID,
      lnd_from_hire_date )
  GROUP BY
    Employee_ID ),
changesup_table AS (
  SELECT
    *,
    CASE
      WHEN recent_changesup_date >= DATE_SUB(cast("{0}" as date), 180) THEN 1
    ELSE
    0
  END
    AS ChangeSup_last6mo,
    CASE
      WHEN recent_changesup_date >= DATE_SUB(cast("{0}" as date), 360) THEN 1
    ELSE
    0
  END
    AS ChangeSup_last12mo
  FROM (
    SELECT
      Employee_ID,
      MAX(Event_Date) AS recent_changesup_date,
      COUNT(Event_Date) AS number_of_changesup
    FROM (
      SELECT
        DISTINCT fld_employee_id AS Employee_ID,
        fld_event_date AS Event_Date
      FROM
        `tbl_hr_event_reason_fact`
      WHERE
        fld_event_reason = 'DATASUP-Supervisor Change'
        AND fld_event_date <= cast("{0}" as date) )
    GROUP BY
      Employee_ID ) ),
promotion_table AS (
  SELECT
    *,
    CASE
      WHEN recent_promotion_date >= DATE_SUB(cast("{0}" as date), 180) THEN 1
    ELSE
    0
  END
    AS Promoted_last6mo,
    CASE
      WHEN recent_promotion_date >= DATE_SUB(cast("{0}" as date), 360) THEN 1
    ELSE
    0
  END
    AS Promoted_last12mo
  FROM (
    SELECT
      Employee_ID,
      MAX(Event_Date) AS recent_promotion_date,
      COUNT(Event_Date) AS number_of_promotions
    FROM (
      SELECT
        DISTINCT fld_employee_id AS Employee_ID,
        fld_event_date AS Event_Date
      FROM
        `tbl_hr_event_reason_fact`
      WHERE
        fld_event = 'Promotion'
        AND fld_event_date <= cast("{0}" as date) )
    GROUP BY
      Employee_ID ) ),
tenure_since_last_promotion as
(
(select fld_employee_id as Employee_ID
      ,month_from_last_Promotion
from
(
SELECT fld_employee_id
      ,fld_event_date
      ,row_number() over(partition by fld_employee_id order by fld_event_date desc) as row_last_promotion
      ,fld_event
      ,fld_event_reason
      ,fld_end_date
      ,fld_job_classification
      ,substr(fld_pay_grade, 1, 2) as Job_Grade
      ,DATEDIFF(day, cast("{0}" as date), fld_event_date)/30 as month_from_last_Promotion
FROM `tbl_hr_event_reason_fact`
where fld_event_reason = 'PROMGT-Promotion - Data Migration' 
    or fld_event_reason = 'PROPWP-Promotion - Pay Change'
)
where row_last_promotion = 1
)
)
,
absenteeism_home
as 
(select Employee_ID
      ,sum(pto_current_month) as pto_current_month
      ,sum(pto_last_month) as pto_last_month
      ,sum(pto_last_2months) as pto_last_2months
      ,sum(absences_current_month) as absences_current_month
      ,sum(absences_last_month) as absences_last_month
      ,sum(absences_last_2months) as absences_last_2months
from
(
select cast(Employee_ID as string) Employee_ID
      ,case
        when absent_month ='on_current_mo' then sum(fld_planned_leave)
        else 0
      end as pto_current_month
      ,case
        when absent_month ='on_last_month' then sum(fld_planned_leave)
        else 0
      end as pto_last_month
      ,case
        when absent_month ='on_last_2months' then sum(fld_planned_leave)
        else 0
      end as pto_last_2months
      ,case
        when absent_month ='on_current_mo' then sum(fld_absent)
        else 0
      end as absences_current_month
      ,case
        when absent_month='on_last_month' then sum(fld_absent)
        else 0
      end as absences_last_month
      ,case
        when absent_month='on_last_2months' then sum(fld_absent)
        else 0
      end as absences_last_2months
from
(select fld_employee_id as Employee_ID
      ,fld_date
      ,fld_absent
      ,fld_planned_leave
      ,case
        when fld_date >= DATE_SUB(cast("{0}" as date), 30)
          then 'on_current_mo'
        when (fld_date >= DATE_SUB(cast("{0}" as date), 60))
            and (fld_date < DATE_SUB(cast("{0}" as date), 30))
          then 'on_last_month'
        when (fld_date >= DATE_SUB(cast("{0}" as date), 90))
            and (fld_date < DATE_SUB(cast("{0}" as date), 60))
          then 'on_last_2months'
        else null
      end as absent_month
from
(
select fld_employee_id
      ,fld_date
      ,cast(fld_absent as numeric) as fld_absent
      ,cast(fld_planned_leave as numeric) as fld_planned_leave
from `tbl_home_attendance_dm`
where fld_date <= cast("{0}" as date)
order by fld_employee_id
)
)
group by Employee_ID, absent_month
)
group by Employee_ID
)
,
ph_complete_data as
(select Name
      ,sum(pto_current_month) as pto_current_month
      ,sum(pto_last_month) as pto_last_month
      ,sum(pto_last_2months) as pto_last_2months
      ,sum(absences_current_month) as absences_current_month
      ,sum(absences_last_month) as absences_last_month
      ,sum(absences_last_2months) as absences_last_2months
from
(
select Crewbie_s_Full_Name as Name
      , absent_month
      ,case
        when absent_month ='on_current_mo' then sum(fld_planned_leave)
        else 0
      end as pto_current_month
      ,case
        when absent_month ='on_last_month' then sum(fld_planned_leave)
        else 0
      end as pto_last_month
      ,case
        when absent_month ='on_last_2months' then sum(fld_planned_leave)
        else 0
      end as pto_last_2months
      ,case
        when absent_month ='on_current_mo' then sum(fld_absent)
        else 0
      end as absences_current_month
      ,case
        when absent_month='on_last_month' then sum(fld_absent)
        else 0
      end as absences_last_month
      ,case
        when absent_month='on_last_2months' then sum(fld_absent)
        else 0
      end as absences_last_2months
from
(
select *
      ,case
        when fld_date >= DATE_SUB(cast("{0}" as date), 30)
          then 'on_current_mo'
        when (fld_date >= DATE_SUB(cast("{0}" as date), 60))
            and (fld_date < DATE_SUB(cast("{0}" as date), 30))
          then 'on_last_month'
        when (fld_date >= DATE_SUB(cast("{0}" as date), 90))
            and (fld_date < DATE_SUB(cast("{0}" as date), 60))
          then 'on_last_2months'
        else null
      end as absent_month
from
(
SELECT Crewbie_s_Full_Name
      ,Air_Date as fld_date
      ,Absent as fld_absent
      ,PTO as fld_planned_leave
FROM `Absenteeism`
where Air_Date <= cast("{0}" as date)
order by Crewbie_s_Full_Name, Air_Date
)
)
group by Crewbie_s_Full_Name, absent_month
)
group by Name
)
,
names_info as 
(
select Name
      ,Person_ID as Employee_ID
from
(
select *
      ,row_number() over(partition by Name order by counts desc) as row_rank
from
(
select Name
      ,Person_ID
      ,count(*) as counts
from
(
SELECT distinct Week_Start, Person_ID, Name
FROM `weekly_roster`
where Week_Start <= cast("{0}" as date) and
      Week_Start >= cast("{1}" as date) and
      Person_ID is not null
order by Name, Week_Start
)
group by Name, Person_ID
)
)
where row_rank=1
)
, absenteeism_table as
(
select Employee_ID
      ,pto_current_month
      ,pto_last_month
      ,pto_last_2months
      ,absences_current_month
      ,absences_last_month
      ,absences_last_2months
from absenteeism_home
union all
select cast(Employee_ID as string) as Employee_ID
      ,pto_current_month
      ,pto_last_month
      ,pto_last_2months
      ,absences_current_month
      ,absences_last_month
      ,absences_last_2months
from ph_complete_data as A
left join names_info as B 
using(Name)
)
,
malaysia_skills as 
(
select cast(Employee_ID as string) as Employee_ID
      ,fld_skills
      ,fld_date
from
(select fld_employee_id as Employee_ID
      ,row_number() over(partition by fld_employee_id order by fld_date desc) as row_rank
      ,fld_skills
      ,fld_date
FROM `tbl_home_attendance_dm`
where fld_date <= cast("{0}" as date)
)
where row_rank=1
)
,
ph_skills as 
(Select *
from
(SELECT Crewbie_s_Full_Name as Name
      ,row_number() over(partition by Crewbie_s_Full_Name order by Air_Date desc) as row_rank
      ,Air_Date as fld_date
      ,Skill
FROM `Absenteeism`
where Air_Date <= cast("{0}" as date)
and Crewbie_s_Full_Name is not null
)
where row_rank=1
)
,
skills_table as
(select * from malaysia_skills
union all
select cast(Employee_ID as string) as Employee_ID
      ,Skill as fld_skills
      ,fld_date
from ph_skills
left join names_info using(Name)
)
,skills_per_employee as
(select Employee_ID
      ,fld_skills
      ,row_number() over(partition by Employee_ID order by fld_date desc) as row_rank
from skills_table
)
, skills_final_table as
(select Employee_ID
      , fld_skills
from skills_per_employee
where row_rank=1 and Employee_ID is not null
)
,
combined_table as
(SELECT
  *
FROM
  HR_demographics AS demographics
LEFT JOIN
  education_table
USING
  (Employee_ID)
LEFT JOIN
  training_table
USING
  (Employee_ID)
LEFT JOIN
  changesup_table
USING
  (Employee_ID)
LEFT JOIN
  promotion_table
USING
  (Employee_ID)
LEFT JOIN tenure_since_last_promotion
using (Employee_ID)
LEFT JOIN absenteeism_table
using (Employee_ID)
Left join skills_final_table
using (Employee_ID)
-- WHERE
--   demographics.Official_Last_Date IS NULL
--   AND demographics.Separation_Notice_Date IS NULL AND
--    demographics.Functional_Unit IN ('Airbnb Ireland (ABNB-PHL)',
--                                     'Airbnb Ireland (ABNB-MYS)',
--                                     'Airbnb CX Japan (ABNB-JPN)')
)
select *except(month_from_last_Promotion
              ,training_hr_on_1mo
              ,training_hr_on_2mo
              ,currentMo_Lnd_hours
              ,ChangeSup_last6mo
              ,ChangeSup_last12mo
              ,Promoted_last6mo
              ,Promoted_last12mo)
       ,case
          when month_from_last_Promotion is null
            then Tenure
          else month_from_last_Promotion
        end as Tenure_in_Grade
       ,case
          when training_hr_on_1mo is null
          then 0
          else training_hr_on_1mo
        end as training_hr_on_1mo
        ,case
          when training_hr_on_2mo is null
          then 0
          else training_hr_on_2mo
        end as training_hr_on_2mo
        ,case
          when currentMo_Lnd_hours is null
          then 0
          else currentMo_Lnd_hours
        end as currentMo_Lnd_hours
        ,case
          when ChangeSup_last6mo is null
          then 0
          else ChangeSup_last6mo
        end as ChangeSup_last6mo
        ,case
          when ChangeSup_last12mo is null
          then 0
          else ChangeSup_last12mo
        end as ChangeSup_last12mo
        ,case
          when Promoted_last6mo is null
          then 0
          else Promoted_last6mo
        end as Promoted_last6mo
        ,case
          when Promoted_last12mo is null
          then 0
          else Promoted_last12mo
        end as Promoted_last12mo
from combined_table