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 JOIN
s 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 (…) flat
position
is a reserved keyword → pos
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
(
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;