SELECT * FROM `pwc_students` where ENROLLMENT_STATUS_DATE = "";


SELECT CURRICULUM FROM `pwc_students` GROUP BY `pwc_students`.`CURRICULUM`;

SELECT curriculum_intranet_id, COUNT(*) FROM `pwc_students` GROUP BY curriculum_intranet_id;

update `pwc_students` set ENROLLMENT_STATUS_DATE = null  where ENROLLMENT_STATUS_DATE = "";
update `pwc_students` set FECHA_BAJA = null where FECHA_BAJA = "";

SELECT * FROM pwc_students WHERE ENROLLMENT_STATUS_DATE is not null AND FECHA_BAJA is null;





SELECT *
FROM pwc_students
JOIN pwc_people ON pwc_students.PEOPLE_CODE_ID = pwc_people.PEOPLE_CODE_ID
WHERE ENROLLMENT_STATUS_DATE is not null AND FECHA_BAJA is null
ORDER BY pwc_students.PEOPLE_ID ASC, ENROLLMENT_STATUS_DATE DESC;





SELECT PEOPLE_CODE_ID,count(*) as repetido
FROM pwc_students
GROUP by PEOPLE_CODE_ID

-- 7816 es el numero a importar


SELECT pwc_students.PEOPLE_CODE_ID
FROM pwc_students
JOIN (
    SELECT PEOPLE_CODE_ID, MAX(ENROLLMENT_STATUS_DATE) AS MAX_DATE
    FROM pwc_students
    GROUP BY PEOPLE_CODE_ID
) tabla_filtro
ON pwc_students.PEOPLE_CODE_ID = tabla_filtro.PEOPLE_CODE_ID
AND pwc_students.ENROLLMENT_STATUS_DATE = tabla_filtro.MAX_DATE;




-- consulta final
        SELECT *
        FROM pwc_students
        LEFT JOIN pwc_people ON pwc_students.PEOPLE_CODE_ID = pwc_people.PEOPLE_CODE_ID
        LEFT JOIN pwc_emails ON pwc_emails.PeopleOrgCodeId = pwc_students.PEOPLE_CODE_ID
        JOIN (
            SELECT PEOPLE_CODE_ID, MAX(ENROLLMENT_STATUS_DATE) AS MAX_DATE
            FROM pwc_students
            GROUP BY PEOPLE_CODE_ID
        ) AS tabla_filtro
        ON pwc_students.PEOPLE_CODE_ID = tabla_filtro.PEOPLE_CODE_ID
        AND pwc_students.ENROLLMENT_STATUS_DATE = tabla_filtro.MAX_DATE
        WHERE ENROLLMENT_STATUS_DATE IS NOT NULL AND FECHA_BAJA IS NULL
        AND BIRTH_COUNTRY = ''
        ORDER BY pwc_students.PEOPLE_ID ASC, ENROLLMENT_STATUS_DATE DESC;


-- BUSCAR:
SELECT * FROM `pwc_payments`
WHERE Primer_Nombre LIKE "%MAHO%"
OR Segundo_Nombre LIKE "%MAHO%"
OR Primer_Apellido LIKE "%MAHO%"
OR Segundo_apellido LIKE "%MAHO%";


SELECT POPULATION FROM `pwc_students` GROUP BY POPULATION;
SELECT * FROM `pwc_students` WHERE POPULATION = "";
UPDATE `pwc_students` SET POPULATION = "COLOMBIA" WHERE POPULATION = "COLOMB";
UPDATE `pwc_students` SET POPULATION = "PERÚ" WHERE POPULATION = "PERU";




-- pwc_students.PEOPLE_CODE_ID,
-- pwc_students.ENROLLMENT_STATUS_DATE as inscription_date,
-- pwc_students.FECHA_BAJA,
-- pwc_students.PEOPLE_ID,
-- pwc_students.NOMBRE_COMPLETO,
-- pwc_students.FIRST_NAME,
-- pwc_students.LAST_NAME,
-- pwc_students.Last_Name_Prefix,
-- pwc_students.AÑO_ACADEMICO,
-- pwc_students.CICLO_ACTUAL,
-- pwc_students.CAMPUS,
-- pwc_students.CREDITS,
-- pwc_students.PROGRAMA,
-- pwc_students.DEGREE,
-- pwc_students.GRADO,
-- pwc_students.CURRICULUM,
-- pwc_students.curriculum_intranet_id,
-- pwc_students.PROGRAMA_ACADEMICO,
-- pwc_students.POPULATION AS COUNTRY,
-- pwc_students.MATRIC_DATE
-- pwc_people.NICKNAME
-- pwc_people.BIRTH_DATE
-- pwc_emails.Email
-- pwc_emails.EmailType
-- pwc_emails.IsActive as EmailActive


SELECT
pwc_students.PEOPLE_CODE_ID,
pwc_students.ENROLLMENT_STATUS_DATE,
pwc_students.FECHA_BAJA,
pwc_students.PEOPLE_ID,
pwc_students.NOMBRE_COMPLETO,
pwc_students.FIRST_NAME,
pwc_students.LAST_NAME,
pwc_students.Last_Name_Prefix,
pwc_students.AÑO_ACADEMICO,
pwc_students.CICLO_ACTUAL,
pwc_students.CAMPUS,
pwc_students.CREDITS,
pwc_students.PROGRAMA,
pwc_students.DEGREE,
pwc_students.GRADO,
pwc_students.CURRICULUM,
pwc_students.curriculum_intranet_id,
pwc_students.PROGRAMA_ACADEMICO,
pwc_students.POPULATION AS COUNTRY,
pwc_students.MATRIC_DATE,
pwc_people.NICKNAME,
pwc_people.STUDENT_NUMBER,
pwc_people.BIRTH_DATE,
pwc_emails.Email,
pwc_emails.EmailType,
pwc_emails.IsActive as EmailActive,
pwc_average.PROMEDIO,
pwc_average.ACTUALIZADO_EL as AVERAGE_UPDATE_AT,
pwc_telephones.CountryId as phoneCountry,
pwc_telephones.PhoneNumber as PhoneNumber
                 FROM pwc_students
                LEFT JOIN pwc_average ON pwc_students.PEOPLE_CODE_ID = pwc_average.PEOPLE_CODE_ID
                LEFT JOIN pwc_people ON pwc_students.PEOPLE_CODE_ID = pwc_people.PEOPLE_CODE_ID
                LEFT JOIN pwc_emails ON pwc_emails.PeopleOrgCodeId = pwc_students.PEOPLE_CODE_ID
                LEFT JOIN pwc_telephones ON pwc_telephones.PersonId = pwc_people.PersonId
                JOIN (
                    SELECT PEOPLE_CODE_ID, MAX(ENROLLMENT_STATUS_DATE) AS MAX_DATE
                    FROM pwc_students
                    GROUP BY PEOPLE_CODE_ID
                ) AS tabla_filtro
                ON pwc_students.PEOPLE_CODE_ID = tabla_filtro.PEOPLE_CODE_ID
                AND pwc_students.ENROLLMENT_STATUS_DATE = tabla_filtro.MAX_DATE
                WHERE ENROLLMENT_STATUS_DATE IS NOT NULL AND FECHA_BAJA IS NULL
                ORDER BY pwc_students.PEOPLE_ID ASC, ENROLLMENT_STATUS_DATE DESC;





--COLOMB
--PERU
--MÉXICO






SELECT t1.PersonPhoneId, t1.PersonId, t1.PhoneNumber,t1.CountryId
FROM pwc_telephones t1
WHERE t1.PersonPhoneId =
( SELECT MIN(t2.PersonPhoneId) FROM pwc_telephones t2 WHERE t2.PersonId = t1.PersonId );

-- 334 + 12637 =

SELECT t1.PersonPhoneId, t1.PersonId, t1.PhoneNumber,t1.CountryId
FROM pwc_telephones t1
WHERE t1.PersonPhoneId not in
( SELECT MIN(t2.PersonPhoneId) FROM pwc_telephones t2 WHERE t2.PersonId = t1.PersonId );




SELECT
pwc_students.PEOPLE_CODE_ID,
pwc_students.ENROLLMENT_STATUS_DATE,
pwc_students.FECHA_BAJA,
pwc_students.PEOPLE_ID,
pwc_students.NOMBRE_COMPLETO,
pwc_students.FIRST_NAME,
pwc_students.LAST_NAME,
pwc_students.Last_Name_Prefix,
pwc_students.AÑO_ACADEMICO,
pwc_students.CICLO_ACTUAL,
pwc_students.CAMPUS,
pwc_students.CREDITS,
pwc_students.PROGRAMA,
pwc_students.DEGREE,
pwc_students.GRADO,
pwc_students.CURRICULUM,
pwc_students.curriculum_intranet_id,
pwc_students.PROGRAMA_ACADEMICO,
pwc_students.POPULATION AS COUNTRY,
pwc_students.MATRIC_DATE,
pwc_people.NICKNAME,
pwc_people.STUDENT_NUMBER,
pwc_people.BIRTH_DATE,
pwc_emails.Email,
pwc_emails.EmailType,
pwc_emails.IsActive as EmailActive,
pwc_average.PROMEDIO,
pwc_average.ACTUALIZADO_EL as AVERAGE_UPDATE_AT,
pwc_telephones.CountryId as phoneCountry,
pwc_telephones.PhoneNumber as PhoneNumber
                 FROM pwc_students
                LEFT JOIN pwc_average ON pwc_students.PEOPLE_CODE_ID = pwc_average.PEOPLE_CODE_ID
                LEFT JOIN pwc_people ON pwc_students.PEOPLE_CODE_ID = pwc_people.PEOPLE_CODE_ID
                LEFT JOIN pwc_emails ON pwc_emails.PeopleOrgCodeId = pwc_students.PEOPLE_CODE_ID
                LEFT JOIN pwc_telephones ON pwc_telephones.PersonId = pwc_people.PersonId
                JOIN (
                    SELECT PEOPLE_CODE_ID, MAX(ENROLLMENT_STATUS_DATE) AS MAX_DATE
                    FROM pwc_students
                    GROUP BY PEOPLE_CODE_ID
                ) AS tabla_filtro
                ON pwc_students.PEOPLE_CODE_ID = tabla_filtro.PEOPLE_CODE_ID
                AND pwc_students.ENROLLMENT_STATUS_DATE = tabla_filtro.MAX_DATE
                WHERE ENROLLMENT_STATUS_DATE IS NOT NULL AND FECHA_BAJA IS NULL
                ORDER BY pwc_students.PEOPLE_ID ASC, ENROLLMENT_STATUS_DATE DESC;



SELECT pa.PEOPLE_CODE_ID, pa.PROGRAM, pa.DEGREE, pa.CURRICULUM, pa.PROMEDIO, pa.ACTUALIZADO_EL
FROM pwc_average pa
INNER JOIN (
    SELECT PEOPLE_CODE_ID, MAX(ACTUALIZADO_EL) AS MAX_ACTUALIZADO_EL
    FROM pwc_average
    GROUP BY PEOPLE_CODE_ID
) sub
ON pa.PEOPLE_CODE_ID = sub.PEOPLE_CODE_ID AND pa.ACTUALIZADO_EL = sub.MAX_ACTUALIZADO_EL
ORDER BY pa.PEOPLE_CODE_ID ASC;
-- 4536
-- 6404
-- 1868


SELECT
pwc_students.PEOPLE_CODE_ID,
pwc_students.ENROLLMENT_STATUS_DATE,
pwc_students.FECHA_BAJA,
pwc_students.PEOPLE_ID,
pwc_students.NOMBRE_COMPLETO,
pwc_students.FIRST_NAME,
pwc_students.LAST_NAME,
pwc_students.Last_Name_Prefix,
pwc_students.AÑO_ACADEMICO,
pwc_students.CICLO_ACTUAL,
pwc_students.CAMPUS,
pwc_students.CREDITS,
pwc_students.PROGRAMA,
pwc_students.DEGREE,
pwc_students.GRADO,
pwc_students.CURRICULUM,
pwc_students.curriculum_intranet_id,
pwc_students.PROGRAMA_ACADEMICO,
pwc_students.POPULATION AS COUNTRY,
pwc_students.MATRIC_DATE,
pwc_people.NICKNAME,
pwc_people.STUDENT_NUMBER,
pwc_people.BIRTH_DATE,
pwc_emails.Email,
pwc_emails.EmailType,
pwc_emails.IsActive as EmailActive,
pwc_average.PROMEDIO,
pwc_average.ACTUALIZADO_EL as AVERAGE_UPDATE_AT,
pwc_telephones.CountryId as phoneCountry,
pwc_telephones.PhoneNumber as PhoneNumber
                 FROM pwc_students
                LEFT JOIN pwc_average ON pwc_students.PEOPLE_CODE_ID = pwc_average.PEOPLE_CODE_ID
                LEFT JOIN pwc_people ON pwc_students.PEOPLE_CODE_ID = pwc_people.PEOPLE_CODE_ID
                LEFT JOIN pwc_emails ON pwc_emails.PeopleOrgCodeId = pwc_students.PEOPLE_CODE_ID
                LEFT JOIN pwc_telephones ON pwc_telephones.PersonId = pwc_people.PersonId
                JOIN (
                    SELECT PEOPLE_CODE_ID, MAX(ENROLLMENT_STATUS_DATE) AS MAX_DATE
                    FROM pwc_students
                    GROUP BY PEOPLE_CODE_ID
                ) AS tabla_filtro
                ON pwc_students.PEOPLE_CODE_ID = tabla_filtro.PEOPLE_CODE_ID
                AND pwc_students.ENROLLMENT_STATUS_DATE = tabla_filtro.MAX_DATE
                WHERE ENROLLMENT_STATUS_DATE IS NOT NULL AND FECHA_BAJA IS NULL
                ORDER BY pwc_students.PEOPLE_ID ASC, ENROLLMENT_STATUS_DATE DESC;



SELECT pa.PEOPLE_CODE_ID, pa.PROGRAM, pa.DEGREE, pa.CURRICULUM, pa.PROMEDIO, pa.ACTUALIZADO_EL
FROM pwc_average pa
INNER JOIN (
    SELECT PEOPLE_CODE_ID, MAX(ACTUALIZADO_EL) AS MAX_ACTUALIZADO_EL
    FROM pwc_average
    GROUP BY PEOPLE_CODE_ID
) sub
ON pa.PEOPLE_CODE_ID = sub.PEOPLE_CODE_ID AND pa.ACTUALIZADO_EL = sub.MAX_ACTUALIZADO_EL
ORDER BY pa.PEOPLE_CODE_ID ASC;
