MS SQL

Trouble Ticket System

Please support our MS SQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jul 2008

Posts: 5

Reputation: deniseaddy is an unknown quantity at this point

Solved Threads: 0

deniseaddy deniseaddy is offline

Offline

Newbie Poster

Trouble Ticket System

Oct 13th, 2008

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

•

Join Date: Jun 2008

Posts: 79

Reputation: varmadba is an unknown quantity at this point

Solved Threads: 8

varmadba

Offline

Junior Poster in Training

Re: Trouble Ticket System

Oct 14th, 2008

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

:- Varma

We are Happy to inform launch of a new site with loads of database related information Site offers wide range of functionality Forums,Blogs,Articles,Editorials and much more
http://www.sqllibrarian.info/

•

Join Date: Jul 2008

Posts: 5

Reputation: deniseaddy is an unknown quantity at this point

Solved Threads: 0

deniseaddy deniseaddy is offline

Offline

Newbie Poster

Re: Trouble Ticket System

Oct 15th, 2008

Thanks for your help.

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

 Help with Code Tags 
 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

•

Join Date: Dec 2004

Posts: 149

Reputation: Geek-Master is an unknown quantity at this point

Solved Threads: 6

Geek-Master Geek-Master is offline

Offline

Junior Poster

Re: Trouble Ticket System

Oct 25th, 2008

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.

If in doubt, reach into the trash can and remove the user guide.