pepsi_investigation.sql

SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
    EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
    AND eligibility:employer_specified_status = 'N'
    AND employment:status IN('T', 'Terminated');
    

SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
    EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
    AND employment:status IN('T', 'Terminated');

SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
    EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
    AND employment:status NOT IN('Active', 'A')
    AND employment:tenure:separation_date >= dateadd(day, -90, CURRENT_TIMESTAMP)
    AND NOT employment:tenure:separation_date > CURRENT_TIMESTAMP
// earliest: "2023-04-14T00:00:00+00:00"
// latest:  "2023-07-10T00:00:00+00:00"
    
SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
    EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
    AND employment:status NOT IN('Active', 'A')
    AND NOT employment:tenure:separation_date > CURRENT_TIMESTAMP;
// earliest: "2020-09-10T00:00:00+00:00"
// latest: "2023-07-10T00:00:00+00:00"

SELECT COUNT(DISTINCT(employee_id)), employment:status FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
    EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
    AND employment:tenure:separation_date > CURRENT_TIMESTAMP
    GROUP BY employment:status;
// earliest: "2023-07-13T00:00:00+00:00"
// latest: "3016-11-30T00:00:00+00:00" (and then "2024-07-27T00:00:00+00:00")
    
    
SELECT DISTINCT(employee_id) FROM BI_BASE.ETL.STG_EMPLOYER_SERVICE__EMPLOYEE_LIVE WHERE
    EMPLOYER_ID = '9297466f-7d4c-4553-9eb6-1464b2a2e51a'
    AND employment:tenure:separation_date < dateadd(day, -90, CURRENT_TIMESTAMP)
// earliest: "2010-08-21T00:00:00+00:00"
// latest:  "2023-04-13T00:00:00+00:00"