SQLize Online / PHPize Online  /  SQLtest Online

SELECT COALESCE(NULLIF(CE."Activity name", ''), NULLIF(Staffing."Activity name", '')) AS "Actvity name", COALESCE(NULLIF(CE.academic_year, ''), NULLIF(Staffing.academic_year, '')) AS academic_year, COALESCE(NULLIF(CE."department", ''), NULLIF(Staffing."department", '')) AS "département", COALESCE(NULLIF(CE."type task", ''), NULLIF(Staffing."type task", '')) AS "type task", CE."Quantité CE", Staffing."quantity STAFFING" , COALESCE(NULLIF(CE.unit_of_work, ''), NULLIF(Staffing.unit_of_work, '')) AS unit_of_work, COALESCE(NULLIF(CE."Campus", ''), NULLIF(Staffing.Campus, '')) AS Campus, COALESCE(NULLIF(CE.cursus, ''), NULLIF(Staffing.cursus, '')) AS cursus, COALESCE(NULLIF(CE.academic_level, ''), NULLIF(Staffing.academic_level, '')) AS academic_level, COALESCE(NULLIF(CE."activity_id", ''), NULLIF(Staffing."activity_id", '')) AS "activity_id" FROM ( -- Subquery CE SELECT AT.activity_id AS "activity_id", A.name AS "Activity name", A.academic_year, d.name AS "department", AT.name_fr AS "type task", SUM(AT.tutorial_hour_equivalent_quantity) as "Quantité CE", AT.unit_of_work, COALESCE(NULLIF(AT.discharge_campus, ''), NULLIF(A.campus, '')) AS "Campus", COALESCE(NULLIF(C.name, ''), NULLIF(C2.name, '')) AS "cursus", AT.discharge_academic_level AS "academic_level" FROM activity_task AT LEFT JOIN activity A ON AT.activity_id = A.id LEFT JOIN cursus C ON C.id = A.cursus_id LEFT JOIN cursus C2 ON C2.id = AT.cursus_id LEFT JOIN department d ON d.id = A.department_id group by AT.activity_id ,A.name ,A.academic_year, d.name , AT.name_fr , AT.unit_of_work, COALESCE(NULLIF(AT.discharge_campus, ''), NULLIF(A.campus, '')) , COALESCE(NULLIF(C.name, ''), NULLIF(C2.name, '')), AT.discharge_academic_level ) AS CE FULL OUTER JOIN ( -- Subquery Staffing SELECT DISTINCT ActiviteDetail.id AS "activity_id", ActiviteDetail.name AS "Activity name", ActiviteDetail.academic_year, d.name AS "department", Activite.name_fr AS "type task", CASE WHEN Activite.name_fr = 'Cours' THEN CAST(ActiviteFaite.quantity AS DECIMAL) * 1.5 / 100 WHEN Activite.name_fr = 'TD' THEN CAST(ActiviteFaite.quantity AS DECIMAL) / 100 WHEN Activite.name_fr = 'TP' THEN CAST(ActiviteFaite.quantity AS DECIMAL) / 100 ELSE NULL END AS "quantity STAFFING", Activite.unit_of_work, ActiviteDetail.campus, Cursus.name AS "cursus", NULL AS "academic_level" FROM contractualization INNER JOIN planned_task ActivitePlanifie ON contractualization.id = ActivitePlanifie.contractualization_id INNER JOIN task Activite ON ActivitePlanifie.task_id = Activite.id INNER JOIN done_task ActiviteFaite ON ActivitePlanifie.id = ActiviteFaite.planned_task_id INNER JOIN month_service_entry MoisPaye ON ActiviteFaite.month_service_entry_id = MoisPaye.id AND MoisPaye.status <> 'cancelled_service' INNER JOIN ( SELECT TCR.application_date AS "application_date", TCR.task_id, TCR.contract_id AS "contract_id" FROM task Tk INNER JOIN task_contract_rate TCR ON TCR.task_id = Tk.id WHERE Tk.name_fr = 'TD' ) TD ON TD.contract_id = contractualization.contract_id AND contractualization.start_date > TD.application_date INNER JOIN task_contract_rate TCR2 ON TCR2.contract_id = TD.contract_id AND TCR2.task_id = TD.task_id AND TCR2.application_date = TD.application_date INNER JOIN task_contract_rate ActiviteContrat ON Activite.id = ActiviteContrat.task_id AND (ActiviteContrat.application_date <= MoisPaye.month OR MoisPaye.month IS NULL) AND ActiviteContrat.contract_id = contractualization.contract_id AND (TD.application_date <= MoisPaye.month OR MoisPaye.month IS NULL) INNER JOIN activity_temporary LiaisonActiviteVacataire ON contractualization.activity_temporary_id = LiaisonActiviteVacataire.id INNER JOIN activity ActiviteDetail ON ActiviteDetail.id = LiaisonActiviteVacataire.activity_id LEFT JOIN cursus Cursus ON Cursus.id = ActiviteDetail.cursus_id LEFT JOIN department d ON d.id = ActiviteDetail.department_id INNER JOIN contract Contrat ON Contrat.id = contractualization.contract_id LEFT JOIN "temporary" tempo ON tempo.id = LiaisonActiviteVacataire.temporary_id WHERE ActiviteContrat.application_date = ( SELECT MAX(ActiviteContratbis.application_date) FROM done_task ActiviteFaitebis INNER JOIN month_service_entry MoisPayebis ON ActiviteFaitebis.month_service_entry_id = MoisPayebis.id AND MoisPayebis.status <> 'cancelled_service' INNER JOIN planned_task ActivitePlanifiebis ON ActivitePlanifiebis.id = ActiviteFaitebis.planned_task_id INNER JOIN task Activitebis ON ActivitePlanifiebis.task_id = Activitebis.id INNER JOIN contractualization contractualizationbis ON contractualizationbis.id = ActivitePlanifiebis.contractualization_id AND contractualizationbis.id = contractualization.id INNER JOIN task_contract_rate ActiviteContratbis ON Activitebis.id = ActiviteContratbis.task_id AND (ActiviteContratbis.application_date <= MoisPayebis.month OR MoisPayebis.month IS NULL) AND ActiviteContratbis.contract_id = contractualizationbis.contract_id INNER JOIN activity_temporary LiaisonActiviteVacatairebis ON contractualizationbis.activity_temporary_id = LiaisonActiviteVacatairebis.id INNER JOIN activity ActiviteDetailbis ON ActiviteDetailbis.id = LiaisonActiviteVacatairebis.activity_id WHERE ActivitePlanifie.task_id = ActivitePlanifiebis.task_id GROUP BY ActivitePlanifie.task_id ) GROUP BY ActiviteFaite.planned_task_id, ActiviteFaite.id, MoisPaye.paying_month, ActiviteDetail.external_source_id, CAST(ActiviteFaite.quantity AS DECIMAL) / 100, CAST(ActiviteFaite.quantity * ActiviteContrat.rate AS DECIMAL) / 10000, ActiviteContrat.rate, ActiviteContrat.application_date, contractualization.status, CAST(ActivitePlanifie.quantity * ActivitePlanifie.rate AS DECIMAL) / 10000, contractualization.contract_id, Activite.name_fr, contractualization.id, Contrat.name_fr, ActiviteDetail.department_id, LiaisonActiviteVacataire.id, ActiviteDetail.id, d.name, Cursus.name, Activite.unit_of_work, ActiviteDetail.campus ) AS Staffing ON Staffing.activity_id = CE.activity_id;