I'm trying to set up a query that would display the data in a certain way, but I can't manage it.
Tables:
CREATE TABLE T_EMPLOYEES (
EMP_ID INTEGER IDENTITY NOT NULL,
EMP_LASTNAME VARCHAR (32) NOT NULL,
EMP_FIRSTNAME VARCHAR (32) NOT NULL,
CONSTRAINT PK_EMP_ID PRIMARY KEY (EMP_ID)
);
CREATE TABLE T_BENEFICIARIES (
BEN_ID INTEGER IDENTITY NOT NULL,
BEN_LASTNAME VARCHAR (32) NOT NULL,
BEN_FIRSTNAME VARCHAR (32) NOT NULL,
CONSTRAINT PK_BEN_ID PRIMARY KEY (BEN_ID)
);
CREATE TABLE T_DAYS (
DAY_ID INTEGER NOT NULL,
DAY_LBL VARCHAR (10) NOT NULL,
CONSTRAINT PK_DAY_ID PRIMARY KEY (DAY_ID)
);
CREATE TABLE T_PLANNINGS (
PLA_ID INTEGER NOT NULL,
CONSTRAINT PK_PLA_ID PRIMARY KEY (PLA_ID),
CONSTRAINT FK_PLA_SAL FOREIGN KEY (PLA_ID) REFERENCES T_EMPLOYEES (EMP_ID)
);
CREATE TABLE T_SLOTS (
SLO_ID INTEGER IDENTITY NOT NULL,
PLA_ID INTEGER NOT NULL,
BEN_ID INTEGER NOT NULL,
DAY_ID INTEGER NOT NULL,
SLO_BEG TIME NOT NULL,
SLO_END TIME NOT NULL,
CONSTRAINT PK_SLO_ID PRIMARY KEY (SLO_ID),
CONSTRAINT FK_SLO_PLA FOREIGN KEY (PLA_ID) REFERENCES T_PLANNINGS (PLA_ID),
CONSTRAINT FK_SLO_BEN FOREIGN KEY (BEN_ID) REFERENCES T_BENEFICIARIES (BEN_ID),
CONSTRAINT FK_SLO_DAY FOREIGN KEY (DAY_ID) REFERENCES T_DAYS (DAY_ID)
);
Insert Script
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (1, 'Dermien', 'Isabelle');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (2, 'Declercq', 'Mireille');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (3, 'Montosier', 'Émilie');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (4, 'Arsenault', 'Florence');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (5, 'Texel', 'Isabelle');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (6, 'Vassent', 'Carole');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (1, 'Duval', 'Sophie');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (2, 'Hivet', 'Marine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (3, 'Fobert', 'Michelle');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (4, 'Merlet', 'Perrine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (5, 'Loiseau', 'Thomas');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (6, 'Chevalier', 'Ambre');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (7, 'Frouin', 'Antoine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (8, 'Herbin', 'Séverine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (9, 'Masson', 'Danika');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (10, 'Gilbert', 'Anna');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (11, 'Chauvin', 'Béatrice');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (12, 'Brunet', 'Valériane');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (13, 'Oger', 'Lucien');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (14, 'Lemesle', 'Marielle');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (15, 'Bellanger', 'Maxime');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (16, 'Noblecourt', 'Alain');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (17, 'Delaune', 'Constance');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (18, 'Metayer', 'Maxence');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (19, 'Bedouet', 'Jules');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (20, 'Lecomte', 'Sylvie');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (21, 'Maurier', 'Gaétan');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (22, 'Trichet', 'Oscar');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (23, 'Monnier', 'Liliane');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (24, 'Jupin', 'William');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (25, 'Charrier', 'Pierre');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (26, 'Bourget', 'Jasmine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (27, 'Dumont', 'Méline');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (28, 'Grimaud', 'Raymond');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (29, 'Lefèvre', 'Hélène');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (30, 'Piron', 'Victor');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (31, 'Blot', 'Blandine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (32, 'Bourgeois', 'Béatrice');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (33, 'Cappe', 'Julia');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (34, 'Thiroux', 'Alba');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (35, 'Crespin', 'Fabien');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (36, 'Dubuc', 'Suzanne');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (37, 'Venant', 'Achille');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (38, 'Couty', 'Clara');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (39, 'Le Gall', 'Marc');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (40, 'Clichet', 'Angélique');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (41, 'Didiot', 'Nina');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (42, 'Poidevin', 'Laure');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (43, 'Barbier', 'Michael');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (44, 'Monthuit', 'Laurence');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (45, 'Godard', 'Joy');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (46, 'Haupa', 'Alain');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (47, 'Gobeaux', 'Charlie');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (48, 'Gaillet', 'Mireille');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (49, 'Jacob', 'Sylvia');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (50, 'Lenoir', 'Francis');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (51, 'Courtois', 'Anthony');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (52, 'Maillet', 'Gabriel');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (53, 'Migeaux', 'Héloïse');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (54, 'Hainault', 'Lucas');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (55, 'Cochet', 'Jules');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (56, 'Lefèvre', 'Murielle');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (57, 'Bousquet', 'Éva');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (58, 'Dulin', 'Grégory');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (59, 'Dermien', 'Alice');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (60, 'Prez', 'Anne-Sophie');;
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (1, 'Monday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (2, 'Tuesday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (3, 'Wednesday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (4, 'Thursday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (5, 'Friday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (6, 'Saturday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (7, 'Sunday');
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (1);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (2);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (3);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (4);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (5);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (6);
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (1, 1, 26, 1, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (2, 1, 1, 3, '13:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (3, 1, 16, 1, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (4, 1, 25, 2, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (5, 1, 25, 5, '14:00:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (6, 1, 7, 1, '11:00:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (7, 1, 20, 4, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (8, 1, 29, 4, '13:30:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (9, 1, 9, 2, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (10, 1, 4, 5, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (11, 1, 30, 3, '16:30:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (12, 2, 15, 1, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (13, 2, 15, 5, '16:00:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (14, 2, 11, 1, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (15, 2, 24, 1, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (16, 2, 24, 5, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (17, 2, 17, 2, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (18, 2, 27, 2, '11:00:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (19, 2, 27, 5, '10:00:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (20, 2, 22, 2, '14:00:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (21, 2, 22, 4, '16:00:00', '18:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (22, 2, 8, 4, '13:30:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (23, 2, 3, 4, '08:00:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (24, 2, 13, 3, '14:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (25, 2, 13, 1, '17:00:00', '19:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (26, 2, 2, 3, '08:00:00', '11:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (27, 3, 19, 1, '08:30:00', '10:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (28, 3, 19, 5, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (29, 3, 12, 2, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (30, 3, 6, 2, '13:30:00', '15:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (31, 3, 6, 5, '13:30:00', '15:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (32, 3, 10, 1, '10:30:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (33, 3, 28, 2, '15:30:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (34, 3, 28, 5, '15:30:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (35, 3, 14, 1, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (36, 3, 5, 1, '16:00:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (37, 3, 5, 4, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (38, 3, 21, 3, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (39, 3, 21, 4, '16:00:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (40, 3, 18, 3, '13:00:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (41, 3, 23, 5, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (42, 3, 23, 4, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (43, 4, 31, 1, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (44, 4, 31, 5, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (45, 4, 32, 2, '08:00:00', '10:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (46, 5, 44, 5, '15:30:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (47, 4, 32, 4, '08:00:00', '10:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (48, 4, 33, 2, '14:00:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (49, 4, 40, 1, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (50, 4, 40, 5, '10:00:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (51, 4, 38, 1, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (52, 4, 38, 6, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (53, 4, 35, 3, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (54, 4, 36, 4, '13:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (55, 4, 34, 2, '10:30:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (56, 4, 34, 5, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (57, 4, 39, 3, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (58, 4, 37, 4, '10:30:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (59, 4, 37, 6, '11:00:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (60, 5, 46, 1, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (61, 5, 42, 3, '13:30:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (62, 5, 41, 1, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (63, 5, 48, 2, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (64, 5, 47, 2, '13:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (65, 5, 45, 3, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (66, 5, 46, 5, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (67, 5, 43, 4, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (68, 5, 49, 1, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (69, 5, 49, 5, '13:00:00', '15:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (70, 5, 50, 4, '13:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (71, 5, 44, 2, '16:30:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (72, 6, 60, 5, '16:00:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (73, 6, 57, 1, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (74, 6, 57, 5, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (75, 6, 55, 1, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (76, 6, 55, 4, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (77, 6, 51, 2, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (78, 6, 59, 3, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (79, 6, 58, 3, '13:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (80, 6, 54, 1, '16:00:00', '18:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (81, 6, 54, 5, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (82, 6, 56, 2, '13:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (83, 6, 52, 2, '16:00:00', '18:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (84, 6, 52, 5, '10:00:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (85, 6, 53, 4, '08:30:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (86, 6, 60, 1, '10:00:00', '12:00:00');
I created this query:
SELECT
PLA_ID,
(EMP_LASTNAME || ' ' || EMP_FIRSTNAME) AS EMPLOYEE,
CASE
WHEN DAY_ID = 1
THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME)
END AS MONDAY,
CASE
WHEN DAY_ID = 2
THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME)
END AS TUESDAY,
CASE
WHEN DAY_ID = 3
THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME)
END AS WEDNESDAY,
CASE
WHEN DAY_ID = 4
THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME)
END AS THURSDAY,
CASE
WHEN DAY_ID = 5
THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME)
END AS FRIDAY,
CASE
WHEN DAY_ID = 6
THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME)
END AS SATURDAY,
CASE
WHEN DAY_ID = 7
THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME)
END AS SUNDAY
FROM
T_PLANNINGS P
INNER JOIN
T_EMPLOYEES E ON P.PLA_ID = E.EMP_ID
INNER JOIN
T_SLOTS S ON P.PLA_ID = S.PLA_ID
INNER JOIN
T_BENEFICIARIES B ON S.BEN_ID = B.BEN_ID
INNER JOIN
T_DAYS D ON S.DAY_ID = D.DAY_ID
I get what is shown here:
| employee | monday | tuesday | wednesday | thursday | friday |
|---|---|---|---|---|---|
| Dermien Isabelle | [08:00:00 -11:00:00] Bourget Jasmine | ||||
| Dermien Isabelle | [11:00:00 -13:00:00] Frouin Antoine | ||||
| Dermien Isabelle | [14:00:00 -17:00:00] Noblecourt Alain | ||||
| Dermien Isabelle | [08:00:00 -10:00:00] Charrier Pierre | ||||
| Dermien Isabelle | [10:00:00 -12:00:00] Masson Danika | ||||
| Dermien Isabelle | [13:30:00 -16:30:00] Duval Sophie | ||||
| Dermien Isabelle | [16:30:00 -18:30:00] Piron Victor | ||||
| Dermien Isabelle | [08:00:00 -12:00:00] Lecomte Sylvie | ||||
| Dermien Isabelle | [13:30:00 -17:00:00] Lefèvre Hélène | ||||
| Dermien Isabelle | [08:00:00 -12:00:00] Merlet Perrine | ||||
| Dermien Isabelle | [14:00:00 -16:30:00] Charrier Pierre |
I'd like to obtain the compact "grouping" of rows as shown here:
| employee | monday | tuesday | wednesday | thursday | friday |
|---|---|---|---|---|---|
| Dermien Isabelle | [08:00:00 -11:00:00] Bourget Jasmine | [08:00:00 -10:00:00] Charrier Pierre | [13:30:00 -16:30:00] Duval Sophie | [08:00:00 -12:00:00] Lecomte Sylvie | [08:00:00 -12:00:00] Merlet Perrine |
| Dermien Isabelle | [11:00:00 -13:00:00] Frouin Antoine | [10:00:00 -12:00:00] Masson Danika | [16:30:00 -18:30:00] Piron Victor | [13:30:00 -17:00:00] Lefèvre Hélène | [14:00:00 -16:30:00] Charrier Pierre |
| Dermien Isabelle | [14:00:00 -17:00:00] Noblecourt Alain |
First of all, it would be easier to factorize the "formatting" part by getting '[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME outside of the day-by-day CASE, in a first step where DAY_ID is still a column instead of 7 columns.
Then what we can do is flag each slot with its position in this day for this employee, thanks to the row_number() window function that helps comparing a row with its siblings (other rows with the same employee and day), within the resultset (that is, after the JOINs and WHERE have applied).
Thus we can get this position thanks to:
ROW_NUMBER() over (PARTITION BY P.PLA_ID, D.DAY_ID ORDER BY SLO_BEG)
You then just have to output 1 row per employee and position (so group 3, 1 will be all first slots for employee 3: be it the monday first slot, tuesday first slot, and so on);
on each of the slots, to get you can either PIVOT (which is uneasy in PostgreSQL, relying on the crosstab function),
or manually pivot, using:
GROUP BY to aggregate the (up to) 7 rows with the same position into 1 row onlyCASE WHEN.But as we now are in a GROUP BY, we have to choose an aggregate function to only get 1 row's BEN_SLOT fill each column.
As your CASE WHEN DAY_ID = 1 THEN BEN_SLOT END will only return a non-null value for 1 out of the 7 rows, and as most aggregate functions ignore NULL rows, we can use any of those aggregate function, so let's choose MAX, around the CASE:
MAX(CASE WHEN DAY_ID = 1 THEN BEN_SLOT END) AS MONDAY, -- ← Return only 1 out of 7 values for this column; as 6 of them are NULL thanks to the CASE, this ensures we get only the intended value: MAX(NULL, NULL, X, NULL) = X).
MAX(CASE WHEN DAY_ID = 2 THEN BEN_SLOT END) AS TUESDAY,
…
GROUP BY PLA_ID, position
Thus your final query will be:
WITH
flat AS
(
SELECT
P.PLA_ID,
D.DAY_ID,
ROW_NUMBER() over (PARTITION BY P.PLA_ID, D.DAY_ID ORDER BY SLO_BEG) position,
(EMP_LASTNAME || ' ' || EMP_FIRSTNAME) AS EMPLOYEE,
'[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME AS BEN_SLOT
FROM T_PLANNINGS P
INNER JOIN T_EMPLOYEES E ON P.PLA_ID = E.EMP_ID
INNER JOIN T_SLOTS S ON P.PLA_ID = S.PLA_ID
INNER JOIN T_BENEFICIARIES B ON S.BEN_ID = B.BEN_ID
INNER JOIN T_DAYS D ON S.DAY_ID = D.DAY_ID
)
SELECT
PLA_ID, EMPLOYEE,
MAX(CASE WHEN DAY_ID = 1 THEN BEN_SLOT END) AS MONDAY,
MAX(CASE WHEN DAY_ID = 2 THEN BEN_SLOT END) AS TUESDAY,
MAX(CASE WHEN DAY_ID = 3 THEN BEN_SLOT END) AS WEDNESDAY,
MAX(CASE WHEN DAY_ID = 4 THEN BEN_SLOT END) AS THURSDAY,
MAX(CASE WHEN DAY_ID = 5 THEN BEN_SLOT END) AS FRIDAY,
MAX(CASE WHEN DAY_ID = 6 THEN BEN_SLOT END) AS SATURDAY,
MAX(CASE WHEN DAY_ID = 7 THEN BEN_SLOT END) AS SUNDAY
FROM flat
GROUP BY PLA_ID, EMPLOYEE, position
ORDER BY PLA_ID, EMPLOYEE, position;
| pla_id | employee | monday | tuesday | wednesday | thursday | friday | saturday | sunday |
|---|---|---|---|---|---|---|---|---|
| 1 | Dermien Isabelle | [08:00:00 -11:00:00] Bourget Jasmine | [08:00:00 -10:00:00] Charrier Pierre | [13:30:00 -16:30:00] Duval Sophie | [08:00:00 -12:00:00] Lecomte Sylvie | [08:00:00 -12:00:00] Merlet Perrine | null | null |
| 1 | Dermien Isabelle | [11:00:00 -13:00:00] Frouin Antoine | [10:00:00 -12:00:00] Masson Danika | [16:30:00 -18:30:00] Piron Victor | [13:30:00 -17:00:00] Lefèvre Hélène | [14:00:00 -16:30:00] Charrier Pierre | null | null |
| 1 | Dermien Isabelle | [14:00:00 -17:00:00] Noblecourt Alain | null | null | null | null | null | null |
(see it as the first SELECT of a fiddle)
WITH flat AS (…) SELECT … FROM flat into SELECT … FROM (…) flatposition is a reserved keyword → posSELECT
PLA_ID, EMPLOYEE,
MAX(CASE WHEN DAY_ID = 1 THEN BEN_SLOT END) AS MONDAY,
MAX(CASE WHEN DAY_ID = 2 THEN BEN_SLOT END) AS TUESDAY,
MAX(CASE WHEN DAY_ID = 3 THEN BEN_SLOT END) AS WEDNESDAY,
MAX(CASE WHEN DAY_ID = 4 THEN BEN_SLOT END) AS THURSDAY,
MAX(CASE WHEN DAY_ID = 5 THEN BEN_SLOT END) AS FRIDAY,
MAX(CASE WHEN DAY_ID = 6 THEN BEN_SLOT END) AS SATURDAY,
MAX(CASE WHEN DAY_ID = 7 THEN BEN_SLOT END) AS SUNDAY
FROM
(
SELECT
P.PLA_ID,
D.DAY_ID,
(SELECT COUNT(1) FROM T_SLOTS SALL WHERE SALL.PLA_ID = S.PLA_ID AND SALL.DAY_ID = S.DAY_ID AND SALL.SLO_BEG < S.SLO_BEG) pos, -- Correlated subquery: we count strictly inferior SLO_BEGs, so our count will start from 0 instead of the 1 returned by row_number().
(EMP_LASTNAME || ' ' || EMP_FIRSTNAME) AS EMPLOYEE,
'[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME AS BEN_SLOT
FROM T_PLANNINGS P
INNER JOIN T_EMPLOYEES E ON P.PLA_ID = E.EMP_ID
INNER JOIN T_SLOTS S ON P.PLA_ID = S.PLA_ID
INNER JOIN T_BENEFICIARIES B ON S.BEN_ID = B.BEN_ID
INNER JOIN T_DAYS D ON S.DAY_ID = D.DAY_ID
) flat
GROUP BY PLA_ID, EMPLOYEE, pos
ORDER BY PLA_ID, EMPLOYEE, pos;