question

logan10111 avatar image
logan10111 asked

SQL procedure for reports assistance

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.


sqlserver2012
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.