Skip to content

Instantly share code, notes, and snippets.

@stwalkerster
Created December 18, 2012 04:49
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save stwalkerster/4325104 to your computer and use it in GitHub Desktop.
Save stwalkerster/4325104 to your computer and use it in GitHub Desktop.
CREATE
ALGORITHM=UNDEFINED
DEFINER=`acc`@`%.toolserver.org`
SQL SECURITY DEFINER
VIEW `closes` AS
select
concat('Closed ',`acc_emails`.`mail_id`) AS `CONCAT("Closed ",mail_id)`,
`acc_emails`.`mail_desc` AS `mail_desc`
from `acc_emails`
where
(`acc_emails`.`mail_type` = 'Message')
union select
'Closed 0' AS `Closed 0`,
'Dropped' AS `Dropped`
union select
'Closed custom' AS `Closed custom`,
'Closed custom' AS `Closed custom`
union select
'Closed custom-n' AS `Closed custom-n`,
'Closed custom - Not created' AS `Closed custom - Not created`
union select
'Closed custom-y' AS `Closed custom-y`,
'Closed custom - Created' AS `Closed custom - Created`
SELECT
Closed.log_pend AS Request,
u.user_id AS UserID,
Closed.log_user AS User,
TIMEDIFF(Closed.log_time, Reserved.log_time) AS "Time Taken",
mail_desc AS "Close Type",
Closed.log_time AS "Date"
FROM acc_log Closed
INNER JOIN acc_log Reserved
ON Closed.log_pend = Reserved.log_pend
INNER JOIN closes c
ON c.`CONCAT("Closed ",mail_id)` = Closed.log_action
LEFT JOIN acc_user u
ON Closed.log_user = u.user_name
WHERE
Closed.log_action != "Closed 4"
AND
Closed.log_action LIKE "Closed%"
AND
Reserved.log_action = "Reserved"
AND
TIMEDIFF(Closed.log_time, Reserved.log_time) < "00:00:30"
AND
Closed.log_user = Reserved.log_user
AND
TIMEDIFF(Closed.log_time, Reserved.log_time) > "00:00:00"
AND
DATE(Closed.log_time) > DATE(NOW()-INTERVAL 3 MONTH)
ORDER BY
TIMEDIFF(Closed.log_time, Reserved.log_time) ASC
;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment