Ad:

MS SQL Discussion Thread
Unsolved
Views: 1232

Oct 13th, 2008

Trouble Ticket System

Expand Post »

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

Similar Threads

Concerning C++ calculations using printf in C
When a host says "included scripts" in Networking

deniseaddy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jul 2008

Permalink

Oct 14th, 2008

Re: Trouble Ticket System

Try This....

Quote ...

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

varmadba

Reputation Points: 22

Solved Threads: 9

Junior Poster in Training

Offline

83 posts
since Jun 2008

Permalink

Oct 15th, 2008

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:

MS SQL Syntax (Toggle Plain Text)
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
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

deniseaddy

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

5 posts
since Jul 2008

Permalink

Oct 25th, 2008

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.

Geek-Master

Reputation Points: 12

Solved Threads: 6

Junior Poster

Offline

156 posts
since Dec 2004

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in MS SQL Forum Timeline: Schema vs. Role Security

Next Thread in MS SQL Forum Timeline: How do I get ONLY DATATYPE and NAME of all the columns available in given table_name

DaniWeb Message

Cancel Changes

User Name
Password