SQL Server 2008 - Update a temporary table

Question

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!

Answer 1

Once you've loaded the data into #tickets (as your example does), this query will give you the most recent for each username:

select * from #tickets x
inner join
(select username, max(DateCompleted) as theDate 
    from #tickets group by username) y
ON x.username = y.username, x.DateCompleted = y.theDate

Answer 2

I don't think you need the Tickets temporary table in this case. You just want to know, "For each user in the comma separated list, what was their most recently completed ticket (if any)".

So you need to find out the most recent ticket for each user, and you want to be sure to outer join that result so if user has no ticket you still get them back.

This is untested, but hopefully it gives you the basic idea.

    SELECT u.UserName
         , u.FirstName
         , u.LastName
         , t.ID
         , t.CompletionDateTime
         , t.AssignedTo
         , t.AssignmentDate
         , t.StatusID
      FROM dbo.User u INNER JOIN dbo.ConvertCsvToTable(@userList) ON u.UserName=l.item
   LEFT OUTER JOIN
          (SELECT t.AssignedTo
                , MAX(t.CompletionDateTime) CompletionDateTime)
             FROM Ticket t
         GROUP BY t.AssignedTo) t
    ON t.AssignedTo = u.UserName;