K1000: VIP Ticket Rule
I have a ticket rule to set high priority for submitters with a label 'VIP'. This triggers the escalation rule which emails once an hour about the ticket until it is addressed. I tried to make it only change the priority if the day is Monday through Friday. Problem is I get an error: [1305: FUNCTION ORG1.DATEPART does not exist]
Here's the select query:
Select 'charlie.frey@wacoisd.org' as CHARLIE,
-- ticket fields
HD_TICKET.ID, -- $id
HD_TICKET.ID AS TICKNUM, -- $ticknum
HD_TICKET.TITLE, -- $title
DATE_FORMAT(HD_TICKET.CREATED,'%b %d %Y %I:%i:%s %p') AS CREATED, -- $created
DATE_FORMAT(HD_TICKET.MODIFIED,'%b %d %Y %I:%i:%s %p') AS MODIFIED, -- $modified
-- change fields
C.COMMENT, -- $comment
C.DESCRIPTION, -- $description
GROUP_CONCAT(CONCAT('----- Change by ', UPDATER.EMAIL,' at ',H.TIMESTAMP,' -----\n',
H.DESCRIPTION,'\n',H.COMMENT,'\n\nPlease see your ticket at http://kbox.wacoisd.org/userui/ticket.php?ID=',H.HD_TICKET_ID,'\n')
ORDER BY H.ID DESC SEPARATOR '\n') HISTORY, -- $history
-- about the updater
UPDATER.USER_NAME AS UPDATER_UNAME, -- $updater_uname
UPDATER.FULL_NAME AS UPDATER_FNAME, -- $updater_fname
UPDATER.EMAIL AS UPDATER_EMAIL, -- $updater_email
IF(UPDATER.FULL_NAME='',UPDATER.USER_NAME,UPDATER.FULL_NAME) AS UPDATER_CONDITIONAL, -- $updater_conditional
-- about the owner
OWNER.USER_NAME AS OWNER_UNAME, -- $owner_uname
OWNER.FULL_NAME AS OWNER_FNAME, -- $owner_fname
OWNER.EMAIL AS OWNER_EMAIL, -- $owner_email
IFNULL(OWNER.USER_NAME,'Unassigned') OWNER_USER, -- $owner_user
OWNER_LABEL.LABEL_ID,
-- about the submitter
SUBMITTER.USER_NAME AS SUBMITTER_UNAME, -- $submitter_uname
SUBMITTER.FULL_NAME AS SUBMITTER_FNAME, -- $submitter_fname
SUBMITTER.EMAIL AS SUBMITTER_EMAIL, -- $submitter_email
-- about priority
P.NAME AS PRIORITY, -- $priority
-- about status
S.NAME AS STATUS, -- $status
-- about impact
I.NAME AS IMPACT, -- $impact
-- about category
CAT.NAME AS CATEGORY, -- $category
-- other fields
HD_TICKET.CUSTOM_FIELD_VALUE0 AS LOCATION, -- $location
HD_TICKET.CUSTOM_FIELD_VALUE1 AS ROOM, -- $room
HD_TICKET.CUSTOM_FIELD_VALUE2 AS WISD_NUM, -- $wisd_num
HD_TICKET.CUSTOM_FIELD_VALUE7 AS EQUIPMENT, -- $equipment
HD_TICKET.CUSTOM_FIELD_VALUE5 AS DESCRIPTION, -- $description
HD_TICKET.CUSTOM_FIELD_VALUE6 AS USER_EMAIL, -- $user_email
-- -- example of static distribution list
'helpdesk@wacoisd.org' AS NEWTICKETEMAIL -- $newticketemail
FROM HD_TICKET
/* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.HD_TICKET_ID = HD_TICKET.ID
/*AND C.ID=<CHANGE_ID>*/
JOIN HD_TICKET_CHANGE LAST_CHANGE ON LAST_CHANGE.HD_TICKET_ID = HD_TICKET.ID
and LAST_CHANGE.ID=(select MAX(ID) from HD_TICKET_CHANGE where HD_TICKET_CHANGE.HD_TICKET_ID = HD_TICKET.ID)
/* complete history*/ JOIN HD_TICKET_CHANGE H ON H.HD_TICKET_ID = HD_TICKET.ID
/* priority ********/ JOIN HD_PRIORITY P ON P.ID=HD_PRIORITY_ID
/* status **********/ JOIN HD_STATUS S ON S.ID=HD_STATUS_ID
/* impact-severity */ JOIN HD_IMPACT I ON I.ID=HD_IMPACT_ID
/* category ********/ JOIN HD_CATEGORY CAT ON CAT.ID=HD_CATEGORY_ID
/* owner ***********/ LEFT JOIN USER OWNER ON OWNER.ID = HD_TICKET.OWNER_ID
/* labels ***********/ RIGHT JOIN USER_LABEL_JT OWNER_LABEL ON OWNER_LABEL.USER_ID = OWNER.ID
/* labels ***********/ RIGHT JOIN USER_LABEL_JT SUBMITTER_LABEL ON SUBMITTER_LABEL.USER_ID = HD_TICKET.SUBMITTER_ID
/* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
/* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID
WHERE S.NAME='New' AND SUBMITTER_LABEL.LABEL_ID = 252
AND DATEPART() = (2,3,4,5,6)
/* this is necessary when using group by functions */
GROUP BY HD_TICKET.ID
HAVING 1=1
Answers (1)
Nothing in that article can give you the day of the week for a date field. I figured out finally that in MySQL the function is DAYOFWEEK(). So I changed the where clause to the following and it works.
DAYOFWEEK(HD_TICKET.CREATED) between 2 and 6
DAYOFWEEK(HD_TICKET.CREATED) between 2 and 6 - See more at: http://www.itninja.com/question/k1000-vip-ticket-rule#sthash.4AH9iXZm.dpuf
Tried simplifying the query to isolate and get the syntax correct:
Select DATEPART(Dw,HD_TICKET.CREATED) as DAYOFWEEK
From HD_TICKET
WHERE DAYOFWEEK = (2,3,4,5,6)
I get this error "Error Code: 1305. FUNCTION ORG1.DATEPART does not exist"
So, it seems like it doesn't even find the DATEPART fucntion. Should I use a different function like DATENAME? That returns the same error. Help! - lmland 11 years ago