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)
);

What have you already tried? Please post the SQL statement you've got, and we'll give it a look.

Some sample data (suitably scrubbed, of course) would be helpful, as well as what you expect your result set to look like. Also, I notice there are quite a few foreign key constraints that reference tables you haven't included. Can we assume they aren't relevant to your query?

Last, what DBMS and version are you using for your database?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.