CREATE TABLE DimTemps(id_temps INT PRIMARY KEY, date_jour DATE, mois VARCHAR(10), trimestre VARCHAR(2), annee INT);
CREATE TABLE DimService(id_service INT PRIMARY KEY, service VARCHAR(40), pole VARCHAR(40), hopital VARCHAR(30));
CREATE TABLE DimPatient(id_patient_sk INT PRIMARY KEY, id_patient INT, nom VARCHAR(30), tranche_age VARCHAR(10), ville VARCHAR(30), date_debut DATE, date_fin DATE);
CREATE TABLE DimMotif(id_motif INT PRIMARY KEY, motif VARCHAR(40), categorie VARCHAR(30));
CREATE TABLE PassageUrgences(id_temps INT, id_service INT, id_patient_sk INT, id_motif INT, attente_min INT, duree_pec_min INT, cout DECIMAL(8,2));
INSERT INTO DimTemps VALUES (1,'2026-01-15','Janvier','T1',2026),(2,'2026-02-03','Février','T1',2026),(3,'2026-02-20','Février','T1',2026),(4,'2026-04-08','Avril','T2',2026),(5,'2026-05-19','Mai','T2',2026);
INSERT INTO DimService VALUES (1,'Urgences adultes','Pôle Urgences-Réa','CHU Nord'),(2,'Urgences pédiatriques','Pôle Femme-Enfant','CHU Nord'),(3,'Urgences gynécologiques','Pôle Femme-Enfant','CHU Nord');
INSERT INTO DimPatient VALUES (1,101,'Martin','30-44','Lyon','2020-01-01','2026-03-31'),(2,101,'Martin','30-44','Villeurbanne','2026-04-01','9999-12-31'),(3,102,'Girard','65+','Lyon','2019-06-01','9999-12-31'),(4,103,'Faure','0-14','Paris','2022-02-01','9999-12-31'),(5,104,'Lemoine','15-29','Paris','2021-09-01','9999-12-31');
INSERT INTO DimMotif VALUES (1,'Fracture du poignet','Traumatologie'),(2,'Douleur thoracique','Cardiologie'),(3,'Fièvre','Infectiologie'),(4,'Entorse','Traumatologie');
INSERT INTO PassageUrgences VALUES
 (1,1,1,1,40,60,120.00),
 (2,1,3,2,25,90,210.00),
 (3,2,4,3,60,45,95.00),
 (4,1,2,4,50,30,70.00),
 (4,2,4,3,30,40,85.00),
 (5,3,5,4,20,35,80.00),
 (5,1,3,2,45,100,230.00),
 (5,2,4,3,40,50,90.00);

SELECT s.pole,t.trimestre,Avg(p.attente_min)
FROM PassageUrgences p
INNER JOIN DimTemps t ON t.id_temps=p.id_temps
INNER JOIN DimService s ON s.id_service=p.id_service
GROUP BY s.pole, t.trimestre;

SELECT Avg (p.attente_min)
    FROM PassageUrgences p;