<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.5700.6" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><SPAN class=953182307-09052007><FONT face=Arial
color=#0000ff size=2>Hi,</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953182307-09052007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=953182307-09052007><FONT face=Arial
color=#0000ff size=2>In our RT we have a ticket-custom field holding the
Customer Id. The following SQL shows the number of tickets per customer
id:</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953182307-09052007><FONT face=Arial
color=#0000ff size=2></FONT></SPAN> </DIV>
<DIV dir=ltr align=left><SPAN class=953182307-09052007><FONT face=Arial
color=#0000ff size=2>select<BR> objcf1.content CustomerId, count(*)
TicketCount<BR>from<BR> tickets, customfields cf1,
objectcustomfieldvalues objcf1<BR>where<BR>
objcf1.objectid=tickets.id<BR> and
objcf1.objecttype='RT::Ticket'<BR> and
objcf1.customfield=cf1.id<BR> and cf1.name='Customer
ID'<BR> and tickets.status in
('new','open','stalled','offer')<BR>group by<BR>
objcf1.content<BR></FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953182307-09052007><FONT face=Arial
color=#0000ff size=2>The syntax for the SQL may vary depending on the database
(we're running Oracle).</FONT></SPAN></DIV>
<DIV dir=ltr align=left><SPAN class=953182307-09052007><FONT face=Arial
color=#0000ff size=2> </DIV></FONT></SPAN>
<DIV><SPAN class=953182307-09052007></SPAN><FONT face=Arial><FONT
color=#0000ff><FONT size=2>/<SPAN
class=953182307-09052007>Steen</SPAN></FONT></FONT></FONT><BR></DIV>
<BLOCKQUOTE
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid; MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader lang=da dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B> rt-users-bounces@lists.bestpractical.com
[mailto:rt-users-bounces@lists.bestpractical.com] <B>On Behalf Of </B>John
Paul Hayes<BR><B>Sent:</B> Tuesday, May 08, 2007 6:25 PM<BR><B>To:</B>
rt-users@lists.bestpractical.com<BR><B>Subject:</B> [rt-users] DB
Query<BR></FONT><BR></DIV>
<DIV></DIV>Hi All,<BR><BR>I am running a script with accesses the rtdb
directly (for speed purposes). One of the requirements is that I have to
<BR>find any ticket what was resolved with a custom field entry
selected.<BR><BR>Does anybody know off hand how to link a ticket id to the
custom field entry id in the db? <BR><BR>Thanks in advance,<BR>JP<BR
clear=all><BR><BR><BR><BR>-- <BR><BR>"If you make people think they're
thinking, they'll love you; but if you really make them think they'll hate
you."<BR><BR>— Don Marquis </BLOCKQUOTE></BODY></HTML>