i want to Calculate and list the average response time taken from the current set of job recruitment records, i.e. all the communications between the School and casual candidates for the express Job, and then update the response time for each casual staff according to updated response time =(currentresponse time + the average response time just calcuated)/ i also try to use this " DATEDIFF "
i still don't know how i can make it. i attached tables .
Any advice or help would be greatly appreciated :)
CREATE TABLE StaffRequest
(
requestNo nvarchar (50) NOT NULL,
candidateID nvarchar (50),
positionID nvarchar (50) NOT NULL,
asID nvarchar (50) NOT NULL,
dateEnlisted datetime,
startDateTime datetime,
CHECK (dateEnlisted<startDateTime),
CONSTRAINT StaffRequest_PK PRIMARY KEY (requestNo),
CONSTRAINT StaffRequest_FK FOREIGN KEY (candidateID)
REFERENCES CasualStaff (candidateID),
CONSTRAINT StaffRequestFK2 FOREIGN KEY (asID)
REFERENCES AcademicSupervisor (asID),
CONSTRAINT StaffRequest_FK3 FOREIGN KEY (positionID)
REFERENCES position (positionID)
);
CREATE TABLE ExpressJob
(
requestNo nvarchar (50) NOT NULL,
datefrom datetime,
dateTo datetime,
CONSTRAINT ExpressJob_PK PRIMARY KEY (requestNo),
CONSTRAINT RegularJob_FK2 foreign key (requestNo)
references StaffRequest (requestNo)
);
CREATE TABLE ResponseTime
(
requestNo nvarchar (50) NOT NULL,
candidateID nvarchar (50) NOT NULL,
respondedDate nvarchar (50),
CONSTRAINT ResponseTime_PK PRIMARY KEY (requestNo, candidateID),
CONSTRAINT ResponseTime_FK FOREIGN KEY (candidateID)
REFERENCES CasualStaff (candidateID),
CONSTRAINT ResponseTime_FK2 FOREIGN KEY (requestNo)
REFERENCES ExpressJob (requestNo)
);
