Trouble Ticket System - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Trouble Ticket System

Author

Topic

deniseaddy
Starting Member

12 Posts

Posted - 2008-10-13 : 10:51:23

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 10:53:50

[code]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[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 11:23:55

and just in case you're using sql 2000 or having compatibility level 80 use the below

SELECT t1.*
FROM tickets t1
INNER JOIN (SELECT ticket_id,max(Date) as latest
            FROM tickets
            GROUP BY ticket_id)t2
ON t2.ticket_id=t1.ticket_id
AND t2.latest=t1.Date

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:26:38

Please note that

quote:

Originally posted by visakh16

SELECT t1.*
FROM tickets t1
INNER JOIN (SELECT ticket_id,max(Date) as latest
            FROM tickets
            GROUP BY ticket_id)t2
ON t2.ticket_id=t1.ticket_id
AND t2.latest=t1.Date

will not work if there are more than one record for a ticket the last day.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 11:31:56

then use id instead

SELECT t1.*
FROM tickets t1
INNER JOIN (SELECT ticket_id,max(id) as latest
            FROM tickets
            GROUP BY ticket_id)t2
ON t2.ticket_id=t1.ticket_id
AND t2.latest=t1.id

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:34:28

And all we now have to hope for, is that id sequence follows date sequence exactly.
God forbid we insert a back-dated record(s).

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 11:43:57

quote:
Originally posted by Peso

And all we now have to hope for, is that id sequence follows date sequence exactly.
God forbid we insert a back-dated record(s).

E 12°55'05.63"
N 56°04'39.26"

i dont think thats a usual thing to happen, especially scenario being issue ticketing system. and if it happens,even the issue resolution time calculations will get affected.
the date field itself reads inserted which means its a kind of audit column, to track how an issue progressed.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-10-13 : 11:45:47

I am just pulling your legs because I am bored right now.

But I am right if backdating can happen.

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-13 : 11:50:29

quote:
Originally posted by Peso

I am just pulling your legs because I am bored right now.
But I am right if backdating can happen.

E 12°55'05.63"
N 56°04'39.26"

yeah..i know

i just told what i know of such a system which i've worked on

deniseaddy
Starting Member

12 Posts

Posted - 2008-10-15 : 11:45:16

Thanks, Peso. Your query seems to work. Much appreciated.

And thanks visakh16 for your suggestions.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources