Hello,
I have stored procedure in which I am trying to retrieve the last ticket completed by each user listed in a comma-delimited string of usernames. The user may not have a ticket associated with them, in this case I know that i just need to return null. The two tables that I am working with are defined as follows:
User
----
UserName,
FirstName,
LastName
Ticket
------
ID,
CompletionDateTime,
AssignedTo,
AssignmentDate,
StatusID
TicketStatus
------------
ID,
Comments
I have created a stored procedure in which I am trying to return the last completed ticket for a comma-delimited list of usernames. Each record needs to include the comments associated with it. Currently, I'm trying the following:
CREATE TABLE #Tickets
(
[UserName] nvarchar(256),
[FirstName] nvarchar(256),
[LastName] nvarchar(256),
[TicketID] int,
[DateCompleted] datetime,
[Comments] text
)
-- This variable is actually passed into the procedure
DECLARE @userList NVARCHAR(max)
SET @userList='user1,user2,user2'
-- Obtain the user information for each user
INSERT INTO #Tickets
(
[UserName],
[FirstName],
[LastName]
)
SELECT
u.[UserName],
u.[FirstName],
u.[LastName]
FROM
User u
INNER JOIN dbo.ConvertCsvToTable(@userList) l ON u.UserName=l.item
At this point, I have the username, first and last name for each user passed in. However, I do not know how to actually get the last ticket completed for each of these users.
How do I do this? I believe I should be updating the temp table I have created. At the same time, id do not know how to get just the last record in an update statement.
Thank you!
