Trouble Ticket System

[deniseaddy]

Hi,

I'm having trouble building the correct syntax select statement for a system to handle support tickets.

The table "tickets" has the following columns:

id (int)
inserted (smalldatetime)
ticket_id (nvarchar)
owner (nvarchar)
subject( nvarchar)
category (nvarchar)
message( nvarchar)
status (nvarchar)

The id is unique for each record but the ticket_id is generated to group a set of support records together. For example, there may be five communications back and forth between admin and customer and so those five records have unique record ids but the same ticket_id since they are associated with the same initital support issue.

What I need to do is obtain all fields from the most recent record in each "ticket_id" group.

So if there are three groups "issue1" "issue2" and "issue3" and they all have five records each, I need to display the most recent record for "issue1" and "issue2" and "issue3" as follows:

Ticket ID Date Category Status
issue1 10/10/08 Technical Question Open
issue2 10/09/08 Billing Question Resolved
issue3 10/07/08 General Question Updated

Can anyone point me in the right direction?

Thanks,

Denise

[varmadba]

Try This....

select t1.ID,t1.Ticket_ID,t1.inserted as 'Date',t1.Category,t1.Status from ticket t1
inner join
(select t2.Ticket_id,Max(ID) as ID from ticket t2
group by t2.Ticket_ID
)total
on t1.ID=total.ID

[deniseaddy]

Thanks for your help.

I've actually used the solution given to me in another forum and it seems to work:

SELECT	TicketID,
	Date,
	Category,
	Status
FROM	(
		SELECT	TicketID,
			Date,
			Category,
			Status,
			ROW_NUMBER() OVER (PARTITION BY TicketID ORDER BY Date DESC) AS RecID
		FROM	Tickets
	) AS d
WHERE	RecID = 1

This is just a suggestion, but you might want to normalize that table into two separate tables: TicketMaster and TicketMessages.

TicketMaster
id (int)
inserted (smalldatetime)
lastUpdated (smalldatetime) DEFAULT GetDate()
ticket_id (int)
owner (nvarchar)
subject(nvarchar)
details(nvarchar)
category (nvarchar)
status (nvarchar)

TicketMessages
UniqueID(int) --or bigint if there is a lot of messaging and you need to retain conversions for a long time
ticket_id (int)
message(nvarchar)
postedBy(nvarchar) --or a user id
postedOn(smalldatetime)

Each message in TicketMessages has a unique id in case you need to delete a message. This allows you a little more information on the conversation which can help. The other setup doesn't tell which is the primary record for all the messages. Plus it also tells you who posted the last message instead of leaving it up to the user to post a signature. I changed the ticket_id column to an integer, you may require alpha characters in your ticket ids so I didn't think about that until after the fact. But if you don't need the alpha characters then you can use the identity feature to auto increment that is built into sql.