OTRS.ru

Код: Выделить всё

SELECT users.last_name, count(article.create_by) FROM article LEFT JOIN users ON article.create_by = users.id GROUP BY article.create_by

Код: Выделить всё

SELECT count(ticket_id), ticket_id
 FROM article
 GROUP BY ticket_id
 HAVING count(ticket_id) > 5

Код: Выделить всё

SELECT concat(users.last_name, ' ', users.first_name) as agent_fullname,  count(article.create_by) FROM article LEFT JOIN users ON article.create_by = users.id where users.id != 1 GROUP BY article.create_by

Код: Выделить всё

SELECT a.date, b.created, c.closed
  FROM (
    SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as date
    FROM ticket_history
    WHERE create_time > CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY date
  ) as a
  LEFT JOIN (
    SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as date, count(*) as created
    FROM ticket_history
    WHERE history_type_id = 1 AND create_time > CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY date
  ) as b USING (date)
  LEFT JOIN (
    SELECT DATE_FORMAT(create_time,'%Y-%m-%d') as date, count(*) as closed
    FROM ticket_history
     WHERE history_type_id IN (1, 27) AND state_id IN (2, 3) AND create_time > CURRENT_DATE - INTERVAL 30 DAY
    GROUP BY date
  ) as c USING (date)

Код: Выделить всё

SELECT date,
  SUM(history_type_id = 1) AS created,
  SUM(history_type_id IN (1, 27) AND state_id IN (2, 3)) AS closed
FROM (
  SELECT DISTINCT history_type_id, ticket_id, state_id, LEFT(create_time, 10) AS date
  FROM ticket_history
  WHERE history_type_id IN (1, 27) AND create_time > CURRENT_DATE - INTERVAL 30 DAY) AS a
GROUP BY date

Код: Выделить всё

SELECT count(*) FROM ticket WHERE ticket_state_id IN (2)  AND queue_id IN (1,2,4,5) AND DATE(change_time) >= DATE_SUB(CURRENT_DATE, INTERVAL 9 HOUR)

Код: Выделить всё

SELECT count(*) FROM ticket WHERE ticket_state_id   AND queue_id IN (1,2,4,5) AND DATE(create_time) >= DATE_SUB(CURRENT_DATE, INTERVAL 9 HOUR)