SQL Puzzles & Answers- P9

Tham khảo tài liệu 'sql puzzles & answers- p9', công nghệ thông tin, cơ sở dữ liệu phục vụ nhu cầu học tập, nghiên cứu và làm việc hiệu quả | 302 PUZZLE 72 SCHEDULING SERVICE CALLS zip_code CHAR 5 NOT NULL CREATE TABLE Services client_id INTEGER NOT NULL REFERENCES Clients emp_id CHAR 9 NOT NULL REFERENCES Personnel start_time DATETIME NOT NULL FOREIGN KEY client_id emp_id start_time REFERENCES client_id emp_id start_time end_time DATETIME -- null is an open job CHECK start_time end_time sku INTEGER NOT NULL PRIMARY KEY client_id emp_id start_time sku Notice the long natural key. If you do not declare it that way you will have no data integrity. But newbies will get scared and use things like IDENTITY as a key and never worry about data integrity. CREATE TABLE Inventory sku INTEGER NOT NULL PRIMARY KEY stock_descr VARCHAR 50 NOT NULL tax_rate DECIMAL 5 3 NOT NULL duration INTEGER NOT NULL The real trick is to create a Personnel Schedule table that holds all available dates for each employee. CREATE TABLE PersonnelSchedule emp_id CHAR 9 NOT NULL REFERENCES Personnel emp_id avail_start_time DATETIME NOT NULL avail_end_time DATETIME NOT NULL CHECK avail_start_time avail_end_time PRIMARY KEY emp_id avail_start_time Answer 2 We need someone with available time between the scheduled periods for the job. In this query the available time must overlap or exactly contain the service call period. The dummy employee is a handy trick to let the dispatcher see a list of available employees via the PK-FK relationship. Please purchase PDF Split-Merge on to remove this watermark. PUZZLE 72 SCHEDULING SERVICE CALLS 303 SELECT FROM ScheduledCalls AS S PersonnelSchedule AS P WHERE xxxxxxx AND xxxxxxx AND BETWEEN AND AND BETWEEN AND But beware This will produce all of the available personnel. We will have to leave it to the dispatcher to make the actual assignments. Please purchase PDF Split-Merge on .