On Friday, one of my co-workers and I decided to hash out a better way for us to find out that customers had added a reply to a help desk ticket. The idea was to poll the database for new comments, but only to have the script notify us if it was new within the last 5 minutes.
Well, that proved to be (at first) something I didn’t know how to do. Thankfully, with a bit of time, research, and trial & error, I was able to put together the code required. The result?
SELECT [SUBJECT],[INCIDENT],[CUST_ID],[COMMENTS] FROM [IncidentSQL].[dbo].[Incident] WHERE ([NEW_DTE] BETWEEN DATEADD(MINUTE, -5, GETDATE()) AND GETDATE()) AND (STATUS_EXT = ‘NFB’);
We select just the four fields of information we need, and the first WHERE statement subtracts 5 minutes from the current date/time, and the AND statement makes sure we only find help desk tickets that have had new comments added in the last 5 minutes, and are currently set to the current status only.
I felt it was quite the feat for someone who hasn’t done any substantial SQL scripting since 2003, and felt good about seeing the results work. Next, we have to figure out how to email the results to the tech support mailbox without adding a ton of complexity.