Systems Management

THE FOLLOWING CODE HAS BEEN EDITED april 27th, 2010: It works now, but I need to be able to display 2 or more rows.

Hello. I'm starting to learn sql scripting but I'm still a bit new at it. I found another thread here that lead me in the right directon, but nothing there was working for me (believe me, I tried it all). Currently running version 5.3.45496 if that matters.

What I'd like to do is, after 1 hour has passed, if there are tickets that are still unassigned, it sends an email notifiying this fact. (Ideally I'd also like it to ignore the saturday/sunday hours, but I can work that out later)

So far I've come up with the following horrendous amount of code which is probably 90% unneeded. The reason I have so much is because I took it from an existing code that I know works, one that sends out a notification email whenever a new ticket is submitted. SO there's probably a lot I don't need, but I know that info works, so I left it and just tried to change the outcome.  Also, I took out my email for now, but rest assured it's there in the final version:

 SELECT
      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 CREATEDON, -- $createdon
      HD_TICKET.CREATED AS CREATED,
      -- 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/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
      -- 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
      -- -- example of static distribution list
      \email@company' AS NEWTICKETEMAIL -- $newticketemail
 
FROM HD_TICKET
     /* latest change ***/ JOIN HD_TICKET_CHANGE C ON C.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
     /* submitter *******/ LEFT JOIN USER SUBMITTER ON SUBMITTER.ID = HD_TICKET.SUBMITTER_ID
     /* updater *********/ LEFT JOIN USER UPDATER ON UPDATER.ID = C.USER_ID

/*WHERE (HD_TICKET.CREATED < SUBDATE(NOW(),INTERVAL 1 HOUR)) AND S.NAME = 'new' */

WHERE HD_TICKET.CREATED < SUBDATE(NOW(), INTERVAL 1 HOUR) AND S.NAME ='new'

ORDER BY HD_TICKET.CREATED ASC

The email that is sent is:

The following ticket(s) are still unassigned in KACE.

Please log into http://k1000/ and assign them: 
- TICKET #$ticknum, a $priority priority ticket, was opened

by $submitter_fname was created on $createdon.

Email for result row is checked off and simple enough, and then the update query (which im not sure if it's needed) is:

 update HD_TICKET
    set HD_TICKET.CC_LIST = '1'
  where 
        (HD_TICKET.ID in (<TICKET_IDS>))

Everything else I've tried (the simpler, shorter looking code) gave me errors on the run log, which is why I'm posting this code, since it's the closest I've seem to come to achieving what I need. I appreciate any and all help with me on this matter! I realize I'm new but hopefully I'm learning as we go!

OTHER INFO THAT MIGHT BE IMPORTANT:
- I have only one queue