CREATE TABLE dbo.HelpDeskCalls
(RefID int, TaskID tinyint, Description nvarchar(4000), EnteredBy int)
GO
CREATE TABLE dbo.HelpDeskUsers
(Users bigint, Name nvarchar(4000), Surname nvarchar(4000), AllowedToLog int)
GO
INSERT HelpDeskCalls VALUES
(1, 1, 'PC not working', 1),
(1, 2, 'Technician sent', 2),
(1, 3, 'Antivirus updated', 2),
(1, 4, 'Case Closed', 1),
(2, 1, 'Change password', 3),
(2, 2, 'Password changed', 2),
(2, 3, 'Case closed', 3),
(3, 1, 'Replace monitor', 4),
(3, 2, 'Monitor replaced', 2),
(3, 3, 'Case closed', 4),
(3, 4, 'Monitor still faulty', 4),
(3, 5, 'Monitor replaced again', 2),
(3, 6, 'Case closed', 4)
INSERT HelpDeskUsers VALUES
(1, 'Bob', 'Smith', 1),
(2, 'John', 'Doe', 1),
(3, 'Peter', 'Well', 1),
(4, 'Mary', 'Williams', 1),
(5, 'Berry', 'Andrews', 0)
SELECT * FROM HelpDeskCalls
SELECT * FROM HelpDeskUsers
We need to following to be completed.
• Design a stored proc that will add an entry to HelpDeskCalls.
• For new entries, RefID must be sequential. For updates to existing tasks, the TaskID must be sequential.
• Only the users that are allowed to log entries can do so.
• Calls are closed, but can be reopened if the user not satisfied with the outcome.
We need the following reports.
• The number of calls logged, and the average amount of tasks it took to complete.
• The person that logged the most calls.
• The person that fixed the most calls.
• The person that had the most calls reopened.
