ianlandsman

// This can be a custom WHERE for a filter/report to limit the results to requests that have been open greater than/less than
// a certain amount of time

(HS_Request.dtGMTClosed - HS_Request.dtGMTOpened) < 259200

ianlandsman

function strftime_win32($format, $ts = null) {
   if (!$ts) $ts = time();

   $mapping = array(
       '%C' => sprintf("%02d", date("Y", $ts) / 100),
       '%D' => '%m/%d/%y',
       '%e' => sprintf("%' 2d", date("j", $ts)),
       '%h' => '%b',
       '%n' => "\n",
       '%r' => date("h:i:s", $ts) . " %p",

ianlandsman

Something like this should work. Note, this will be very heavy on the DB so I wouldn't recommend enabling counts on the filter unless you know your DB server has the horsepower.

Also the info inside tEmailHeaders is serialized PHP, it's not really designed to be searched so double quotes are escaped. There could be some other things along those lines that might trip you up as well.

xRequest NOT IN (SELECT xRequest FROM HS_Request_History WHERE tEmailHeaders LIKE "%campaignmonitor.com%")

ianlandsman

xRequest NOT IN (SELECT xRequest FROM HS_Request_ReportingTags WHERE HS_Request_ReportingTags.xRequest = xRequest)