collate reservations, MySQL

collate reservations

CREATE TABLE IF NOT EXISTS
tbl (id int auto_increment primary key, pid int, name nvarchar(64), start date, enddt date, 
                  room1 varchar(2),room2 varchar(2),room3 varchar(2),room4 varchar(2),
                  room5 varchar(2),room6 varchar(2),room7 varchar(2),room8 varchar(2),
                  room9 varchar(2),room10 varchar(2),room11 varchar(2),room12 varchar(2),
                  room13 varchar(2),room14 varchar(2));
delete from tbl;
INSERT INTO tbl 
 (pid,name,start,enddt,room1,room2,room3,room4,room5,room6,room7,room8,room9,room10,room11,room12,room13,room14) VALUES
 (47,'Charlie','20170822','20170825','','','1','','','','','','','','','','',''),
 (13,'Louise','20170812','20170815','','1','','','','','','','','','','','',''),
 (34,'Harry','20170813','20170817','','1','','','','','','','','','','','',''),
 (35,'Sally','20170814','20170901','','1','','','','','','','','','','','',''),
 (45,'Patty','20170822','20170905','','','1','','','','','','','','','','','')
-- (,'','20170922','20170925','','','','','','','','','','','','','',''),
; 
SELECT group_concat(name) names, count(CASE room1 WHEN '1' THEN 1 END) r1, count(CASE room2 WHEN '1' THEN 1 END) r2, 
  count(CASE room3 WHEN '1' THEN 1 END) r3, count(CASE room4 WHEN '1' THEN 1 END) r4, count(CASE room5 WHEN '1' THEN 1 END) r5,
  count(CASE room6 WHEN '1' THEN 1 END) r6, count(CASE room7 WHEN '1' THEN 1 END) r7, count(CASE room8 WHEN '1' THEN 1 END) r8,
  count(CASE room9 WHEN '1' THEN 1 END) r9, count(CASE room10 WHEN '1' THEN 1 END) r10,count(CASE room11 WHEN '1' THEN 1 END) r11,
  count(CASE room12 WHEN '1' THEN 1 END) r12, count(CASE room13 WHEN '1' THEN 1 END) r13, count(CASE room14 WHEN '1' THEN 1 END) r14
FROM tbl where '20170823' between start and enddt-interval 1 day


run \| edit \| history \| help	0