[rt-users] SQL Query Help

[Mathew Snyder]

Roy,

The query you provided won't work.  I get zippy from it.  What I've tried doing
is to create a variable with the query in it:
my $query = "'Queue = \"CustomerCare\" AND (Status = \"open\" OR Status =
\"resolved\") AND LastUpdate = $startDate'";

and then using $query as the argument for FromSQL.  This appears to cause the
script to go through every single ticket in the database looking for anything
resolved.  The only way I've been able to get reliable results is if I limit the
search only to tickets that are open and Created after the start date.

Anything else you can point me toward?

Mathew

Roy El-Hames wrote:
> Matthew try:
> 
> $tix->FromSQL('Queue = "CustomerCare" AND (Status = "resolved" OR Status
> = "open") AND (LastUpdated > $startDate OR LastUpdated
> < $endDate)');
> 
> Last updated will also take care of resolved for you, (cause the last
> update was to resolver the ticket)
> By the way I am not sure how FromSQL handles the brackets inside the
> statement( you may need to dig a little to know how to handle them, then
> tell us all so we learn - but you do need these brackets).
> 
> Regards;
> Roy
> 
> 
> 
> Mathew Snyder wrote:
>> I'm trying to write a query the the FromSQL method.  I need to create
>> an object
>> contains tickets in our CustomerCare queue and that have either been
>> worked on
>> or resolved in the last week (which will be based on the date the
>> script is run,
>> not the last week from now).
>>
>> This is what I have so far
>> $tix->FromSQL('Queue = "CustomerCare" AND Status = "resolved" OR
>> Status = "open"');
>>
>> I'm thinking I need to add something like "AND (Created > $startDate AND
>> LastUpdated < $endDate) OR Resoved > $startDate".  Basically, I don't
>> really
>> care when it was created.  I just need the transactions that were
>> added between
>> a start date and an end date.  That makes me think the above snippets
>> are both
>> wrong.
>>
>> I have been trying to use the Query Builder to get this accomplished
>> but it
>> doesn't seem to support both AND and OR requests in the same query. 
>> Changing
>> the aggregator changes it for the entire query, not just one line. 
>> Selecting
>> the Aggregator prior to adding a new criteria does the same thing.
>>
>> Can anyone help me out with this?
>>
>> Mathew
>> _______________________________________________
>> http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users
>>
>> Community help: http://wiki.bestpractical.com
>> Commercial support: sales at bestpractical.com
>>
>>
>> Discover RT's hidden secrets with RT Essentials from O'Reilly Media.
>> Buy a copy at http://rtbook.bestpractical.com
>>
>>   
> 
>