Need some help with a SQL query

[christophergidlof]

Hello everyone i need some help with a SQL query that Select all tickets from the OTRS Database that have in the history table been moved from a any queue to a specific queue in the past 10 days

ex on a query that works on the create_time from the ticket table

$queue_3rdline = "(55,173,174,175)";
$state_id_new = "(1)";

$time = time() - 60*60*24*10;
$days10 = date("Y-m-d",$time);

$sql = "SELECT count(id) as total from (
SELECT id as id,
create_time as start
FROM ticket
WHERE ticket.queue_id in $queue_3rdline AND ticket.ticket_state_id in $state_id_new
) as tmp
WHERE start < '$days10'";



The Database is postgres and the Sql is written in php

Thanks in advance.

[crythias]

You should look at ticket history ...

Basically, the pseudo query is
select (fields)
from ticket t
left join ticket_history th ON t.id = th.ticket_id
left join ticket_history_type tht ON tht.id = th.ticket_history_type_id
left join queue q ON q.id = t.queue_id
WHERE tht.name = "QueueUpdate"
AND q.name = "DestinationQueue"
AND tht.create_time < "your range"

I pulled this from my head. It's probably not fully correct and has bugs, misspellings, bad sql grammar, won't work, but might be more relevant to you as a concept.