Trouble Ticket System
 

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

Re: Trouble Ticket System
 

Try This....

Re: Trouble Ticket System
 

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

Re: Trouble Ticket System
 

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.