September 23, 2015 at 10:53 am
This took me ages, but here you go.... phew
Dataset 1 Sample Data (Logins)
@StartDate was 21/09/2015
@EndDate was 22/09/2015
Sorted Data and pulled out sample data for Room L1.03
DROP TABLE mytable;
CREATE TABLE mytable(
CampusOD BIT NOT NULL PRIMARY KEY
,Campus VARCHAR(12) NOT NULL
,BuildingID INTEGER NOT NULL
,Building VARCHAR(13) NOT NULL
,RoomID INTEGER NOT NULL
,Room VARCHAR(5) NOT NULL
,LabID INTEGER NOT NULL
,Lab VARCHAR(9) NOT NULL
,Machine VARCHAR(9) NOT NULL
,LogonTime VARCHAR(19) NOT NULL
,LogoffTime VARCHAR(19) NOT NULL
,LogonHour INTEGER NOT NULL
,LogoffHour INTEGER NOT NULL
);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 09:08:16','21/09/2015 22:16:00',9,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 12:55:17','21/09/2015 16:02:35',12,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95266','21/09/2015 08:36:12','21/09/2015 16:04:53',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95267','21/09/2015 08:36:43','21/09/2015 16:04:46',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95268','21/09/2015 08:38:16','21/09/2015 16:05:05',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 08:38:29','21/09/2015 09:02:59',8,9);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 09:03:18','21/09/2015 11:33:33',9,11);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95270','21/09/2015 08:40:35','21/09/2015 16:03:54',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95271','21/09/2015 08:40:50','21/09/2015 16:04:12',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95272','21/09/2015 08:41:44','21/09/2015 16:04:21',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95273','21/09/2015 08:41:55','21/09/2015 16:04:32',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 08:44:04','21/09/2015 22:16:00',8,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 13:27:24','21/09/2015 22:16:00',13,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 15:40:34','22/09/2015 10:27:20',15,10);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 08:44:13','21/09/2015 09:49:47',8,9);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 10:18:39','21/09/2015 16:05:44',10,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95276','21/09/2015 08:44:34','21/09/2015 16:05:53',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95277','21/09/2015 08:44:41','21/09/2015 16:06:03',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95278','21/09/2015 08:45:03','21/09/2015 11:23:31',8,11);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95279','21/09/2015 08:45:15','21/09/2015 16:06:23',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95280','21/09/2015 08:45:21','21/09/2015 16:06:32',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95281','21/09/2015 08:45:32','21/09/2015 16:06:40',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95282','21/09/2015 08:46:00','21/09/2015 16:07:04',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95283','21/09/2015 08:46:14','21/09/2015 16:07:12',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95284','21/09/2015 08:46:18','21/09/2015 16:07:25',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95285','21/09/2015 08:46:28','21/09/2015 16:07:34',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 13:01:17','21/09/2015 14:03:16',13,14);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 15:24:55','21/09/2015 15:26:40',15,15);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 09:12:56','21/09/2015 12:30:31',9,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 12:31:28','21/09/2015 12:35:46',12,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 15:24:29','21/09/2015 16:06:56',15,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 08:35:02','21/09/2015 12:00:58',8,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 12:55:02','21/09/2015 16:05:37',12,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95289','21/09/2015 08:48:39','21/09/2015 16:07:36',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95290','21/09/2015 08:48:26','21/09/2015 16:08:09',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95291','21/09/2015 08:48:18','21/09/2015 16:08:33',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95292','21/09/2015 08:46:45','21/09/2015 16:11:26',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95293','21/09/2015 08:46:55','21/09/2015 16:11:01',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95294','21/09/2015 08:47:03','21/09/2015 16:10:34',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95295','21/09/2015 08:47:14','21/09/2015 16:09:05',8,16);
Dataset 2 Sample Data (Bookings)
@Startdate was 21/09/2015
@EndDate was 22/09/2015
@Room was L1.03
DROP TABLE mytable;
CREATE TABLE mytable(
VARCHAR(1)
,hour_description VARCHAR(5) NOT NULL
,Description VARCHAR(9) NOT NULL
,StartDateTime VARCHAR(19) NOT NULL
,EndDateTime VARCHAR(19) NOT NULL
,Name VARCHAR(5) NOT NULL
,hour_number INTEGER
);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('1 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('2 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('3 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('4 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('5 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('6 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('7 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('8 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('9 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('10 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('11 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('12 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('1 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('2 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('3 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('4 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('5 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('6 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('7 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('8 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('9 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('10 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('11 pm',NULL,NULL,NULL,NULL);
Output for both combined should be:
Hour_description, Description, PC Logins
4pm,induction,26
5pm,induction,24
Thanks
September 23, 2015 at 10:54 am
This took me ages, but here you go.... phew
Dataset 1 Sample Data (Logins)
@StartDate was 21/09/2015
@EndDate was 22/09/2015
Sorted Data and pulled out sample data for Room L1.03
DROP TABLE mytable;
CREATE TABLE mytable(
CampusOD BIT NOT NULL PRIMARY KEY
,Campus VARCHAR(12) NOT NULL
,BuildingID INTEGER NOT NULL
,Building VARCHAR(13) NOT NULL
,RoomID INTEGER NOT NULL
,Room VARCHAR(5) NOT NULL
,LabID INTEGER NOT NULL
,Lab VARCHAR(9) NOT NULL
,Machine VARCHAR(9) NOT NULL
,LogonTime VARCHAR(19) NOT NULL
,LogoffTime VARCHAR(19) NOT NULL
,LogonHour INTEGER NOT NULL
,LogoffHour INTEGER NOT NULL
);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 09:08:16','21/09/2015 22:16:00',9,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 12:55:17','21/09/2015 16:02:35',12,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95266','21/09/2015 08:36:12','21/09/2015 16:04:53',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95267','21/09/2015 08:36:43','21/09/2015 16:04:46',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95268','21/09/2015 08:38:16','21/09/2015 16:05:05',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 08:38:29','21/09/2015 09:02:59',8,9);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 09:03:18','21/09/2015 11:33:33',9,11);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95270','21/09/2015 08:40:35','21/09/2015 16:03:54',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95271','21/09/2015 08:40:50','21/09/2015 16:04:12',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95272','21/09/2015 08:41:44','21/09/2015 16:04:21',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95273','21/09/2015 08:41:55','21/09/2015 16:04:32',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 08:44:04','21/09/2015 22:16:00',8,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 13:27:24','21/09/2015 22:16:00',13,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 15:40:34','22/09/2015 10:27:20',15,10);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 08:44:13','21/09/2015 09:49:47',8,9);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 10:18:39','21/09/2015 16:05:44',10,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95276','21/09/2015 08:44:34','21/09/2015 16:05:53',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95277','21/09/2015 08:44:41','21/09/2015 16:06:03',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95278','21/09/2015 08:45:03','21/09/2015 11:23:31',8,11);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95279','21/09/2015 08:45:15','21/09/2015 16:06:23',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95280','21/09/2015 08:45:21','21/09/2015 16:06:32',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95281','21/09/2015 08:45:32','21/09/2015 16:06:40',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95282','21/09/2015 08:46:00','21/09/2015 16:07:04',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95283','21/09/2015 08:46:14','21/09/2015 16:07:12',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95284','21/09/2015 08:46:18','21/09/2015 16:07:25',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95285','21/09/2015 08:46:28','21/09/2015 16:07:34',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 13:01:17','21/09/2015 14:03:16',13,14);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 15:24:55','21/09/2015 15:26:40',15,15);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 09:12:56','21/09/2015 12:30:31',9,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 12:31:28','21/09/2015 12:35:46',12,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 15:24:29','21/09/2015 16:06:56',15,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 08:35:02','21/09/2015 12:00:58',8,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 12:55:02','21/09/2015 16:05:37',12,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95289','21/09/2015 08:48:39','21/09/2015 16:07:36',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95290','21/09/2015 08:48:26','21/09/2015 16:08:09',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95291','21/09/2015 08:48:18','21/09/2015 16:08:33',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95292','21/09/2015 08:46:45','21/09/2015 16:11:26',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95293','21/09/2015 08:46:55','21/09/2015 16:11:01',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95294','21/09/2015 08:47:03','21/09/2015 16:10:34',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95295','21/09/2015 08:47:14','21/09/2015 16:09:05',8,16);
Dataset 2 Sample Data (Bookings)
@Startdate was 21/09/2015
@EndDate was 22/09/2015
@Room was L1.03
DROP TABLE mytable;
CREATE TABLE mytable(
VARCHAR(1)
,hour_description VARCHAR(5) NOT NULL
,Description VARCHAR(9) NOT NULL
,StartDateTime VARCHAR(19) NOT NULL
,EndDateTime VARCHAR(19) NOT NULL
,Name VARCHAR(5) NOT NULL
,hour_number INTEGER
);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('1 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('2 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('3 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('4 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('5 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('6 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('7 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('8 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('9 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('10 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('11 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('12 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('1 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('2 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('3 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('4 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('5 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('6 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('7 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('8 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('9 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('10 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('11 pm',NULL,NULL,NULL,NULL);
Output for both combined should be:
Hour_description, Description, PC Logins
4pm,induction,26
5pm,induction,24
Thanks
September 24, 2015 at 3:19 am
This took me ages, but here you go.... phew
Dataset 1 Sample Data (Logins)
@StartDate was 21/09/2015
@EndDate was 22/09/2015
Sorted Data and pulled out sample data for Room L1.03
DROP TABLE mytable;
CREATE TABLE mytable(
CampusOD BIT NOT NULL PRIMARY KEY
,Campus VARCHAR(12) NOT NULL
,BuildingID INTEGER NOT NULL
,Building VARCHAR(13) NOT NULL
,RoomID INTEGER NOT NULL
,Room VARCHAR(5) NOT NULL
,LabID INTEGER NOT NULL
,Lab VARCHAR(9) NOT NULL
,Machine VARCHAR(9) NOT NULL
,LogonTime VARCHAR(19) NOT NULL
,LogoffTime VARCHAR(19) NOT NULL
,LogonHour INTEGER NOT NULL
,LogoffHour INTEGER NOT NULL
);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 09:08:16','21/09/2015 22:16:00',9,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 12:55:17','21/09/2015 16:02:35',12,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95266','21/09/2015 08:36:12','21/09/2015 16:04:53',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95267','21/09/2015 08:36:43','21/09/2015 16:04:46',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95268','21/09/2015 08:38:16','21/09/2015 16:05:05',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 08:38:29','21/09/2015 09:02:59',8,9);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 09:03:18','21/09/2015 11:33:33',9,11);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95270','21/09/2015 08:40:35','21/09/2015 16:03:54',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95271','21/09/2015 08:40:50','21/09/2015 16:04:12',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95272','21/09/2015 08:41:44','21/09/2015 16:04:21',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95273','21/09/2015 08:41:55','21/09/2015 16:04:32',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 08:44:04','21/09/2015 22:16:00',8,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 13:27:24','21/09/2015 22:16:00',13,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 15:40:34','22/09/2015 10:27:20',15,10);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 08:44:13','21/09/2015 09:49:47',8,9);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 10:18:39','21/09/2015 16:05:44',10,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95276','21/09/2015 08:44:34','21/09/2015 16:05:53',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95277','21/09/2015 08:44:41','21/09/2015 16:06:03',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95278','21/09/2015 08:45:03','21/09/2015 11:23:31',8,11);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95279','21/09/2015 08:45:15','21/09/2015 16:06:23',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95280','21/09/2015 08:45:21','21/09/2015 16:06:32',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95281','21/09/2015 08:45:32','21/09/2015 16:06:40',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95282','21/09/2015 08:46:00','21/09/2015 16:07:04',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95283','21/09/2015 08:46:14','21/09/2015 16:07:12',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95284','21/09/2015 08:46:18','21/09/2015 16:07:25',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95285','21/09/2015 08:46:28','21/09/2015 16:07:34',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 13:01:17','21/09/2015 14:03:16',13,14);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 15:24:55','21/09/2015 15:26:40',15,15);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 09:12:56','21/09/2015 12:30:31',9,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 12:31:28','21/09/2015 12:35:46',12,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 15:24:29','21/09/2015 16:06:56',15,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 08:35:02','21/09/2015 12:00:58',8,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 12:55:02','21/09/2015 16:05:37',12,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95289','21/09/2015 08:48:39','21/09/2015 16:07:36',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95290','21/09/2015 08:48:26','21/09/2015 16:08:09',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95291','21/09/2015 08:48:18','21/09/2015 16:08:33',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95292','21/09/2015 08:46:45','21/09/2015 16:11:26',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95293','21/09/2015 08:46:55','21/09/2015 16:11:01',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95294','21/09/2015 08:47:03','21/09/2015 16:10:34',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95295','21/09/2015 08:47:14','21/09/2015 16:09:05',8,16);
Dataset 2 Sample Data (Bookings)
@Startdate was 21/09/2015
@EndDate was 22/09/2015
@Room was L1.03
DROP TABLE mytable;
CREATE TABLE mytable(
VARCHAR(1)
,hour_description VARCHAR(5) NOT NULL
,Description VARCHAR(9) NOT NULL
,StartDateTime VARCHAR(19) NOT NULL
,EndDateTime VARCHAR(19) NOT NULL
,Name VARCHAR(5) NOT NULL
,hour_number INTEGER
);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('1 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('2 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('3 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('4 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('5 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('6 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('7 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('8 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('9 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('10 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('11 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('12 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('1 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('2 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('3 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('4 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('5 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('6 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('7 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('8 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('9 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('10 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('11 pm',NULL,NULL,NULL,NULL);
Output for both combined should be:
Hour_description, Description, PC Logins
4pm,induction,26
5pm,induction,24
Thanks
September 24, 2015 at 3:19 am
This took me ages, but here you go.... phew
Dataset 1 Sample Data (Logins)
@StartDate was 21/09/2015
@EndDate was 22/09/2015
Sorted Data and pulled out sample data for Room L1.03
DROP TABLE mytable;
CREATE TABLE mytable(
CampusOD BIT NOT NULL PRIMARY KEY
,Campus VARCHAR(12) NOT NULL
,BuildingID INTEGER NOT NULL
,Building VARCHAR(13) NOT NULL
,RoomID INTEGER NOT NULL
,Room VARCHAR(5) NOT NULL
,LabID INTEGER NOT NULL
,Lab VARCHAR(9) NOT NULL
,Machine VARCHAR(9) NOT NULL
,LogonTime VARCHAR(19) NOT NULL
,LogoffTime VARCHAR(19) NOT NULL
,LogonHour INTEGER NOT NULL
,LogoffHour INTEGER NOT NULL
);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 09:08:16','21/09/2015 22:16:00',9,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 12:55:17','21/09/2015 16:02:35',12,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95266','21/09/2015 08:36:12','21/09/2015 16:04:53',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95267','21/09/2015 08:36:43','21/09/2015 16:04:46',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95268','21/09/2015 08:38:16','21/09/2015 16:05:05',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 08:38:29','21/09/2015 09:02:59',8,9);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 09:03:18','21/09/2015 11:33:33',9,11);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95270','21/09/2015 08:40:35','21/09/2015 16:03:54',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95271','21/09/2015 08:40:50','21/09/2015 16:04:12',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95272','21/09/2015 08:41:44','21/09/2015 16:04:21',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95273','21/09/2015 08:41:55','21/09/2015 16:04:32',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 08:44:04','21/09/2015 22:16:00',8,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 13:27:24','21/09/2015 22:16:00',13,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 15:40:34','22/09/2015 10:27:20',15,10);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 08:44:13','21/09/2015 09:49:47',8,9);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 10:18:39','21/09/2015 16:05:44',10,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95276','21/09/2015 08:44:34','21/09/2015 16:05:53',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95277','21/09/2015 08:44:41','21/09/2015 16:06:03',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95278','21/09/2015 08:45:03','21/09/2015 11:23:31',8,11);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95279','21/09/2015 08:45:15','21/09/2015 16:06:23',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95280','21/09/2015 08:45:21','21/09/2015 16:06:32',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95281','21/09/2015 08:45:32','21/09/2015 16:06:40',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95282','21/09/2015 08:46:00','21/09/2015 16:07:04',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95283','21/09/2015 08:46:14','21/09/2015 16:07:12',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95284','21/09/2015 08:46:18','21/09/2015 16:07:25',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95285','21/09/2015 08:46:28','21/09/2015 16:07:34',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 13:01:17','21/09/2015 14:03:16',13,14);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 15:24:55','21/09/2015 15:26:40',15,15);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 09:12:56','21/09/2015 12:30:31',9,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 12:31:28','21/09/2015 12:35:46',12,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 15:24:29','21/09/2015 16:06:56',15,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 08:35:02','21/09/2015 12:00:58',8,12);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 12:55:02','21/09/2015 16:05:37',12,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95289','21/09/2015 08:48:39','21/09/2015 16:07:36',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95290','21/09/2015 08:48:26','21/09/2015 16:08:09',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95291','21/09/2015 08:48:18','21/09/2015 16:08:33',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95292','21/09/2015 08:46:45','21/09/2015 16:11:26',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95293','21/09/2015 08:46:55','21/09/2015 16:11:01',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95294','21/09/2015 08:47:03','21/09/2015 16:10:34',8,16);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95295','21/09/2015 08:47:14','21/09/2015 16:09:05',8,16);
Dataset 2 Sample Data (Bookings)
@Startdate was 21/09/2015
@EndDate was 22/09/2015
@Room was L1.03
DROP TABLE mytable;
CREATE TABLE mytable(
VARCHAR(1)
,hour_description VARCHAR(5) NOT NULL
,Description VARCHAR(9) NOT NULL
,StartDateTime VARCHAR(19) NOT NULL
,EndDateTime VARCHAR(19) NOT NULL
,Name VARCHAR(5) NOT NULL
,hour_number INTEGER
);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('1 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('2 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('3 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('4 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('5 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('6 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('7 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('8 am',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('9 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('10 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('11 am','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('12 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('1 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('2 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('3 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('4 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('5 pm','Induction','21/09/2015 09:00:00','21/09/2015 18:00:00','L1.03');
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('6 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('7 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('8 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('9 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('10 pm',NULL,NULL,NULL,NULL);
INSERT INTO mytable(hour_description,Description,StartDateTime,EndDateTime,Name) VALUES ('11 pm',NULL,NULL,NULL,NULL);
Output for both combined should be:
Hour_description, Description, PC Logins
4pm,induction,26
5pm,induction,24
Thanks
September 24, 2015 at 3:49 am
rkelly58 (9/24/2015)
This took me ages, but here you go.... phewDataset 1 Sample Data (Logins)
@StartDate was 21/09/2015
@EndDate was 22/09/2015
Sorted Data and pulled out sample data for Room L1.03
ok....did you try running your scripts before you posted them.......?
(Violation of PRIMARY KEY constraint errors)
its seems that in a couple of cases for the pc data that you have overlapping times....how do you want that handled?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 24, 2015 at 5:09 am
Hi J,
I used the conversion tool mentioned in the FAQs to convert the output into a table for you to use, I didn't test the script post conversion.
For the overlapping times (for the same PC on the same day) such as this:
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 08:44:04','21/09/2015 22:16:00',8,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 13:27:24','21/09/2015 22:16:00',13,22);
INSERT INTO mytable(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 15:40:34','22/09/2015 10:27:20',15,10);
I was told this could there is number of possibilities, it could be that one person is logged on, when another person does a remote logon onto the same PC. Then a third person logged in and stayed logged in until 10:27am the following day. The fact the first 2 sessions are logged off is likely due to a batch job that puts an end time there if no logoff is present. i.e. PC crashed and no logoff recorded, a script checks and adds logoff time I'm told.
So for this, if there is an "overlap", can we take the earliest logon time for that machine and then take the latest logout time? If the logoff goes on after midnight, cut off at midnight?
Thank you
September 24, 2015 at 5:17 am
try this as a start....it should give some ideas on how you can now move on.
I have broken each step out into separate ctes to make it clear what is going on
not sure why you are storing LogonTime ,LogoffTime as VARCHAR(19) NOT NULL
so I used the logonhour/off columns instead of bothering to convert to a proper datetime
CREATE TABLE #inputtable(
description VARCHAR(20) NOT NULL
,StartDateTime DATETIME NOT NULL
,EndDateTime DATETIME NOT NULL
,Name VARCHAR(6) NOT NULL
);
INSERT INTO #inputtable(description,StartDateTime,EndDateTime,Name) VALUES ('Induction','2015-09-21 09:00:00.000','2015-09-21 18:00:00.000','L1.03');
CREATE TABLE #pclogin(
CampusOD INT NOT NULL
,Campus VARCHAR(12) NOT NULL
,BuildingID INTEGER NOT NULL
,Building VARCHAR(13) NOT NULL
,RoomID INTEGER NOT NULL
,Room VARCHAR(5) NOT NULL
,LabID INTEGER NOT NULL
,Lab VARCHAR(9) NOT NULL
,Machine VARCHAR(9) NOT NULL
,LogonTime VARCHAR(19) NOT NULL
,LogoffTime VARCHAR(19) NOT NULL
,LogonHour INTEGER NOT NULL
,LogoffHour INTEGER NOT NULL
);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 09:08:16','21/09/2015 22:16:00',9,22);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95265','21/09/2015 12:55:17','21/09/2015 16:02:35',12,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95266','21/09/2015 08:36:12','21/09/2015 16:04:53',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95267','21/09/2015 08:36:43','21/09/2015 16:04:46',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95268','21/09/2015 08:38:16','21/09/2015 16:05:05',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 08:38:29','21/09/2015 09:02:59',8,9);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95269','21/09/2015 09:03:18','21/09/2015 11:33:33',9,11);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95270','21/09/2015 08:40:35','21/09/2015 16:03:54',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95271','21/09/2015 08:40:50','21/09/2015 16:04:12',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95272','21/09/2015 08:41:44','21/09/2015 16:04:21',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95273','21/09/2015 08:41:55','21/09/2015 16:04:32',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 08:44:04','21/09/2015 22:16:00',8,22);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 13:27:24','21/09/2015 22:16:00',13,22);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95274','21/09/2015 15:40:34','22/09/2015 10:27:20',15,10);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 08:44:13','21/09/2015 09:49:47',8,9);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95275','21/09/2015 10:18:39','21/09/2015 16:05:44',10,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95276','21/09/2015 08:44:34','21/09/2015 16:05:53',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95277','21/09/2015 08:44:41','21/09/2015 16:06:03',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95278','21/09/2015 08:45:03','21/09/2015 11:23:31',8,11);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95279','21/09/2015 08:45:15','21/09/2015 16:06:23',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95280','21/09/2015 08:45:21','21/09/2015 16:06:32',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95281','21/09/2015 08:45:32','21/09/2015 16:06:40',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95282','21/09/2015 08:46:00','21/09/2015 16:07:04',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95283','21/09/2015 08:46:14','21/09/2015 16:07:12',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95284','21/09/2015 08:46:18','21/09/2015 16:07:25',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95285','21/09/2015 08:46:28','21/09/2015 16:07:34',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 13:01:17','21/09/2015 14:03:16',13,14);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95286','21/09/2015 15:24:55','21/09/2015 15:26:40',15,15);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 09:12:56','21/09/2015 12:30:31',9,12);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 12:31:28','21/09/2015 12:35:46',12,12);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95287','21/09/2015 15:24:29','21/09/2015 16:06:56',15,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 08:35:02','21/09/2015 12:00:58',8,12);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95288','21/09/2015 12:55:02','21/09/2015 16:05:37',12,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95289','21/09/2015 08:48:39','21/09/2015 16:07:36',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95290','21/09/2015 08:48:26','21/09/2015 16:08:09',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95291','21/09/2015 08:48:18','21/09/2015 16:08:33',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95292','21/09/2015 08:46:45','21/09/2015 16:11:26',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95293','21/09/2015 08:46:55','21/09/2015 16:11:01',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95294','21/09/2015 08:47:03','21/09/2015 16:10:34',8,16);
INSERT INTO #pclogin(CampusOD,Campus,BuildingID,Building,RoomID,Room,LabID,Lab,Machine,LogonTime,LogoffTime,LogonHour,LogoffHour) VALUES (1,'Main Campus',6,'Exam Building',17,'L1.03',220,'Main Area','EXB-95295','21/09/2015 08:47:14','21/09/2015 16:09:05',8,16);
;with roomhours (hour_number ,hour_description) as
(
SELECT 0, '12 am' UNION ALL
SELECT 1, '1 am' UNION ALL
SELECT 2, '2 am' UNION ALL
SELECT 3, '3 am' UNION ALL
SELECT 4, '4 am' UNION ALL
SELECT 5, '5 am' UNION ALL
SELECT 6, '6 am' UNION ALL
SELECT 7, '7 am' UNION ALL
SELECT 8, '8 am' UNION ALL
SELECT 9, '9 am' UNION ALL
SELECT 10, '10 am' UNION ALL
SELECT 11, '11 am' UNION ALL
SELECT 12, '12 pm' UNION ALL
SELECT 13, '1 pm' UNION ALL
SELECT 14, '2 pm' UNION ALL
SELECT 15, '3 pm' UNION ALL
SELECT 16, '4 pm' UNION ALL
SELECT 17, '5 pm' UNION ALL
SELECT 18, '6 pm' UNION ALL
SELECT 19, '7 pm' UNION ALL
SELECT 20, '8 pm' UNION ALL
SELECT 21, '9 pm' UNION ALL
SELECT 22, '10 pm' UNION ALL
SELECT 23, '11 pm' )
, pcsummary as (
SELECT
r.hour_number
, r.hour_description
, p.machine
, p.logontime
, p.logofftime
FROM roomhours AS r LEFT OUTER JOIN
#pclogin p ON r.hour_number >= p.logonhour
and r.hour_number < p.logoffhour)
, pctotal as (
SELECT
hour_description
, COUNT(DISTINCT machine) AS pclogin
FROM pcsummary
GROUP BY
hour_number
, hour_description
)
, roomsummary as (
SELECT
r.hour_number
, r.hour_description
, i.description
, i.StartDateTime
, i.EndDateTime
, i.Name
FROM roomhours AS r LEFT OUTER JOIN
#inputtable AS i ON r.hour_number >= DATEPART(hh , i.StartDateTime)
AND r.hour_number < DATEPART(hh , i.EndDateTime)
)
SELECT
rs.hour_description
, rs.description
, rs.StartDateTime
, rs.EndDateTime
, rs.Name
, pt.pclogin
FROM roomsummary AS rs INNER JOIN
pctotal AS pt ON rs.hour_description = pt.hour_description
ORDER BY
rs.hour_number;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 25, 2015 at 5:38 am
Hi J,
Just been trying the script out and it looks good. Had a warning about not having permissions to create Table, but it ran ok.
Can you please change the code so that it uses my tables instead of using the Create Tables?
Really humbled with all the help you've given me on this so far.
Regards,
September 25, 2015 at 5:52 am
rkelly58 (9/25/2015)
Hi J,Just been trying the script out and it looks good. Had a warning about not having permissions to create Table, but it ran ok.
Can you please change the code so that it uses my tables instead of using the Create Tables?
Really humbled with all the help you've given me on this so far.
Regards,
???
why not put your queries into ctes at the start of the code and take out my create table examples?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 25, 2015 at 8:07 am
Had a go at combing my scripts into the code, but no luck, here is what I tried:
SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,
MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime, LogonTimes.LogoffTime,
LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_LabUtilisation_ComputerDetails
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName
(SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT rdowner.V_ACTIVITY.Description, rdowner.V_ACTIVITY_DATETIME.StartDateTime, rdowner.V_ACTIVITY_DATETIME.EndDateTime,
rdowner.V_LOCATION.Name
FROM rdowner.V_ACTIVITY INNER JOIN
rdowner.V_ACTIVITY_DATETIME ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
rdowner.V_ACTIVITY_LOCATION ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
rdowner.V_LOCATION ON rdowner.V_ACTIVITY_LOCATION.LocationId = rdowner.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room))
SELECT r.hour_description, cte_1.Description, cte_1.StartDateTime, cte_1.EndDateTime, cte_1.Name, r.hour_number
FROM roomhours AS r LEFT OUTER JOIN
cte AS cte_1 ON r.hour_number >= DATEPART(hh, cte_1.StartDateTime) AND r.hour_number < DATEPART(hh, cte_1.EndDateTime)
ORDER BY r.hour_number
;with roomhours (hour_number ,hour_description) as
(
SELECT 0, '12 am' UNION ALL
SELECT 1, '1 am' UNION ALL
SELECT 2, '2 am' UNION ALL
SELECT 3, '3 am' UNION ALL
SELECT 4, '4 am' UNION ALL
SELECT 5, '5 am' UNION ALL
SELECT 6, '6 am' UNION ALL
SELECT 7, '7 am' UNION ALL
SELECT 8, '8 am' UNION ALL
SELECT 9, '9 am' UNION ALL
SELECT 10, '10 am' UNION ALL
SELECT 11, '11 am' UNION ALL
SELECT 12, '12 pm' UNION ALL
SELECT 13, '1 pm' UNION ALL
SELECT 14, '2 pm' UNION ALL
SELECT 15, '3 pm' UNION ALL
SELECT 16, '4 pm' UNION ALL
SELECT 17, '5 pm' UNION ALL
SELECT 18, '6 pm' UNION ALL
SELECT 19, '7 pm' UNION ALL
SELECT 20, '8 pm' UNION ALL
SELECT 21, '9 pm' UNION ALL
SELECT 22, '10 pm' UNION ALL
SELECT 23, '11 pm' )
, pcsummary as (
SELECT
r.hour_number
, r.hour_description
, p.machine
, p.logontime
, p.logofftime
FROM roomhours AS r LEFT OUTER JOIN
#LogonTimes p ON r.hour_number >= p.logonhour
and r.hour_number < p.logoffhour)
, pctotal as (
SELECT
hour_description
, COUNT(DISTINCT machine) AS pclogin
FROM pcsummary
GROUP BY
hour_number
, hour_description
)
, roomsummary as (
SELECT
r.hour_number
, r.hour_description
, i.description
, i.StartDateTime
, i.EndDateTime
, i.Name
FROM roomhours AS r LEFT OUTER JOIN
#cte_1 AS i ON r.hour_number >= DATEPART(hh , i.StartDateTime)
AND r.hour_number < DATEPART(hh , i.EndDateTime)
)
SELECT
rs.hour_description
, rs.description
, rs.StartDateTime
, rs.EndDateTime
, rs.Name
, pt.pclogin
FROM roomsummary AS rs INNER JOIN
pctotal AS pt ON rs.hour_description = pt.hour_description
ORDER BY
rs.hour_number;
I'm getting SQL execution errors:
Error is ON Clause near 'SELECT'
Error in ON clause 'FROM'
Unable to parse query text
error message: must declare scalar variable "@StartDate".
Must declare scalar variable "@StartDate".
Any ideas?
September 25, 2015 at 8:27 am
rkelly58 (9/25/2015)
Had a go at combing my scripts into the code, but no luck, here is what I tried:
SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime, LogonTimes.LogoffTime,
LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_LabUtilisation_ComputerDetails
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName
(SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT rdowner.V_ACTIVITY.Description, rdowner.V_ACTIVITY_DATETIME.StartDateTime, rdowner.V_ACTIVITY_DATETIME.EndDateTime,
rdowner.V_LOCATION.Name
FROM rdowner.V_ACTIVITY INNER JOIN
rdowner.V_ACTIVITY_DATETIME ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
rdowner.V_ACTIVITY_LOCATION ON rdowner.V_ACTIVITY.Id = rdowner.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
rdowner.V_LOCATION ON rdowner.V_ACTIVITY_LOCATION.LocationId = rdowner.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room))
SELECT r.hour_description, cte_1.Description, cte_1.StartDateTime, cte_1.EndDateTime, cte_1.Name, r.hour_number
FROM roomhours AS r LEFT OUTER JOIN
cte AS cte_1 ON r.hour_number >= DATEPART(hh, cte_1.StartDateTime) AND r.hour_number < DATEPART(hh, cte_1.EndDateTime)
ORDER BY r.hour_number
;with roomhours (hour_number ,hour_description) as
(
SELECT 0, '12 am' UNION ALL
SELECT 1, '1 am' UNION ALL
SELECT 2, '2 am' UNION ALL
SELECT 3, '3 am' UNION ALL
SELECT 4, '4 am' UNION ALL
SELECT 5, '5 am' UNION ALL
SELECT 6, '6 am' UNION ALL
SELECT 7, '7 am' UNION ALL
SELECT 8, '8 am' UNION ALL
SELECT 9, '9 am' UNION ALL
SELECT 10, '10 am' UNION ALL
SELECT 11, '11 am' UNION ALL
SELECT 12, '12 pm' UNION ALL
SELECT 13, '1 pm' UNION ALL
SELECT 14, '2 pm' UNION ALL
SELECT 15, '3 pm' UNION ALL
SELECT 16, '4 pm' UNION ALL
SELECT 17, '5 pm' UNION ALL
SELECT 18, '6 pm' UNION ALL
SELECT 19, '7 pm' UNION ALL
SELECT 20, '8 pm' UNION ALL
SELECT 21, '9 pm' UNION ALL
SELECT 22, '10 pm' UNION ALL
SELECT 23, '11 pm' )
, pcsummary as (
SELECT
r.hour_number
, r.hour_description
, p.machine
, p.logontime
, p.logofftime
FROM roomhours AS r LEFT OUTER JOIN
#LogonTimes p ON r.hour_number >= p.logonhour
and r.hour_number < p.logoffhour)
, pctotal as (
SELECT
hour_description
, COUNT(DISTINCT machine) AS pclogin
FROM pcsummary
GROUP BY
hour_number
, hour_description
)
, roomsummary as (
SELECT
r.hour_number
, r.hour_description
, i.description
, i.StartDateTime
, i.EndDateTime
, i.Name
FROM roomhours AS r LEFT OUTER JOIN
#cte_1 AS i ON r.hour_number >= DATEPART(hh , i.StartDateTime)
AND r.hour_number < DATEPART(hh , i.EndDateTime)
)
SELECT
rs.hour_description
, rs.description
, rs.StartDateTime
, rs.EndDateTime
, rs.Name
, pt.pclogin
FROM roomsummary AS rs INNER JOIN
pctotal AS pt ON rs.hour_description = pt.hour_description
ORDER BY
rs.hour_number;
I'm getting SQL execution errors:
Error is ON Clause near 'SELECT'
Error in ON clause 'FROM'
Unable to parse query text
error message: must declare scalar variable "@StartDate".
Must declare scalar variable "@StartDate".
Any ideas?
any ideas...yes......you cant just drop your select statements like that.
review how I created the roomstable cte when you said you couldnt create the table ...which was a week ago now !!
research syntax for CTE (common table expression)...learn how it works
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 25, 2015 at 9:00 am
Had another go, looked into cte tables. but I haven't clue about constructing the SQL, only new to this:
I would be able to follow it once its working.
Here's by go again, but still wont load. Used cte2 for the names, referenced them in the query, but still no joy.
,cte2 as (
SELECT MachineLocations.CampusID, MachineLocations.Campus, MachineLocations.BuildingID, MachineLocations.Building, MachineLocations.RoomID,
MachineLocations.Room, MachineLocations.LabID, MachineLocations.Lab, MachineLocations.Machine, LogonTimes.LogonTime, LogonTimes.LogoffTime,
LogonTimes.LogonHour, LogonTimes.LogoffHour
FROM (SELECT CampusID, Campus, BuildingID, Building, RoomID, Room, LabID, Lab, Machine
FROM View_LabUtilisation_ComputerDetails
GROUP BY Room, Lab, Building, Campus, CampusID, RoomID, LabID, BuildingID, Machine) AS MachineLocations LEFT OUTER JOIN
(SELECT MachineName, LogonTime, LogoffTime, DATEPART(hh, LogonTime) AS LogonHour, DATEPART(hh, LogoffTime) AS LogoffHour
FROM AccessManagement.dbo.Auditing_LogonActivity
WHERE (LogonTime >= @StartDate) AND (LogonTime <= @EndDate)
GROUP BY MachineName, LogonTime, LogoffTime, DATEPART(hh, LogoffTime), DATEPART(hh, LogonTime)) AS LogonTimes ON
MachineLocations.Machine = LogonTimes.MachineName
)
;with roomhours (hour_number ,hour_description) as
(
SELECT 0, '12 am' UNION ALL
SELECT 1, '1 am' UNION ALL
SELECT 2, '2 am' UNION ALL
SELECT 3, '3 am' UNION ALL
SELECT 4, '4 am' UNION ALL
SELECT 5, '5 am' UNION ALL
SELECT 6, '6 am' UNION ALL
SELECT 7, '7 am' UNION ALL
SELECT 8, '8 am' UNION ALL
SELECT 9, '9 am' UNION ALL
SELECT 10, '10 am' UNION ALL
SELECT 11, '11 am' UNION ALL
SELECT 12, '12 pm' UNION ALL
SELECT 13, '1 pm' UNION ALL
SELECT 14, '2 pm' UNION ALL
SELECT 15, '3 pm' UNION ALL
SELECT 16, '4 pm' UNION ALL
SELECT 17, '5 pm' UNION ALL
SELECT 18, '6 pm' UNION ALL
SELECT 19, '7 pm' UNION ALL
SELECT 20, '8 pm' UNION ALL
SELECT 21, '9 pm' UNION ALL
SELECT 22, '10 pm' UNION ALL
SELECT 23, '11 pm' )
,cte as (
SELECT Description, StartDateTime, EndDateTime, Name
FROM (SELECT data.V_ACTIVITY.Description, data.V_ACTIVITY_DATETIME.StartDateTime, data.V_ACTIVITY_DATETIME.EndDateTime,
data.V_LOCATION.Name
FROM data.V_ACTIVITY INNER JOIN
data.V_ACTIVITY_DATETIME ON data.V_ACTIVITY.Id = data.V_ACTIVITY_DATETIME.ActivityID INNER JOIN
data.V_ACTIVITY_LOCATION ON data.V_ACTIVITY.Id = data.V_ACTIVITY_LOCATION.ActivityId INNER JOIN
data.V_LOCATION ON data.V_ACTIVITY_LOCATION.LocationId = data.V_LOCATION.Id) AS [#Tempy]
WHERE (StartDateTime >= @StartDate) AND (EndDateTime <= @EndDate) AND (Name = @Room)
)
, pcsummary as (
SELECT
r.hour_number
, r.hour_description
, p.machine
, p.logontime
, p.logofftime
FROM roomhours AS r LEFT OUTER JOIN
#cte2 p ON r.hour_number >= p.logonhour
and r.hour_number < p.logoffhour)
, pctotal as (
SELECT
hour_description
, COUNT(DISTINCT machine) AS pclogin
FROM pcsummary
GROUP BY
hour_number
, hour_description
)
, roomsummary as (
SELECT
r.hour_number
, r.hour_description
, i.description
, i.StartDateTime
, i.EndDateTime
, i.Name
FROM roomhours AS r LEFT OUTER JOIN
#cte_1 AS i ON r.hour_number >= DATEPART(hh , i.StartDateTime)
AND r.hour_number < DATEPART(hh , i.EndDateTime)
)
SELECT
rs.hour_description
, rs.description
, rs.StartDateTime
, rs.EndDateTime
, rs.Name
, pt.pclogin
FROM roomsummary AS rs INNER JOIN
pctotal AS pt ON rs.hour_description = pt.hour_description
ORDER BY
rs.hour_number;
Could you correct it?
September 25, 2015 at 10:52 am
not sure where you did your research....but https://msdn.microsoft.com/en-GB/library/ms175972.aspx explains it reasonly well
you will need to declare your variables
When a CTE is used in a statement that is part of a batch, the statement before it must be followed by a semicolon.
Specifying more than one WITH clause in a CTE is not allowed ....(WITH is the first part of the statement)
you have not referenced the ctes correctly either for some reason you have #cte2 and #cte_1 in the latter part of the code (these references must match the prior cte names)
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 25, 2015 at 11:12 am
Can't u just do it for me?
I won't accuse u of barking again, promise 🙂
September 25, 2015 at 11:26 am
rkelly58 (9/25/2015)
Can't u just do it for me?I won't accuse u of barking again, promise 🙂
I assume you have heard the phrase "give a man a fish........"
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 31 through 45 (of 49 total)
You must be logged in to reply to this topic. Login to reply