HelpSpot Help Desk Software | HelpSpot Blog | HelpSpot Support

SQL Query for same customer opening a request for same problem


#1

I would like to show all request within a 10 day period that the customer had to submit a 2 or 3rd request for the same category.

I would like to query the SQL tables

SELECT HS_Request.sFirstName, HS_Request.sLastName, HS_Request.sEmail, HS_Request_Catagory.*
FROM HS_Request, HS_Request_History
WHERE HS_Request.sEmail, HS_Request.xCatagory = Duplicate within the last 7 days


#2

I think something like this should work for you. We’re doing a subquery to get only counts larger than one. Data and times are stored in unix timestamp format so you need to do a conversion on those. (This is MySQL specific syntax)

(SELECT count(xRequest) as xRequestCount, xCategory, sEmail 
FROM HS_Request 
WHERE dtGMTOpened > UNIX_TIMESTAMP(NOW())-864000 
GROUP BY sEmail, xCategory) 
AS x 
WHERE x.xRequestCount > 1```