SQLize
Online
/
PHPize Online
/
SQLtest Online
A
A
A
Share
Donate
Blog
Popular
Donate
A
A
A
Share
Blog
Popular
SQLize.online is a free online SQL environment for quickly running, experimenting with and sharing code.
You can run your SQL code on top of the most popular RDBMS including MySQL, MariaDB, SQLite, PostgreSQL, Oracle and Microsoft SQL Server.
SQL code:
Upload
Copy
Format
Clear
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;
SQL
Server:
MariaDB 11.4
MariaDB 11.5
MariaDB 10
MariaDB 10 Sakila (ReadOnly)
MySQL 5.7
MySQL 5.7 Sakila (ReadOnly)
MySQL 8.0
MySQL 8.0 Sakila (ReadOnly)
SQLite 3
PostgreSQL 10 Bookings (ReadOnly)
PostgreSQL 11
PostgreSQL 12
PostgreSQL 13
PostgreSQL 14
PostgreSQL 15
MS SQL Server 2017
MS SQL Server 2019
MS SQL Server 2022
MS SQL Server 2022 AdventureWorks (ReadOnly)
Firebird 4.0
Firebird 4.0 (Employee)
Oracle Database 19c (HR)
Oracle Database 21c
Oracle Database 23c Free
SOQOL
Version
ER Diagram
Preserve result
Stuck with a problem?
Got Error?
Ask ChatGPT!
Result:
Copy
Clear