brycecaruthers
6/16/2017 - 4:01 PM

Originally created for case 125488, this query pulls the accounts, users/emails, and reports that have been viewed for a group of accounts i

Originally created for case 125488, this query pulls the accounts, users/emails, and reports that have been viewed for a group of accounts in a particular date range. The main edits will occur on the two lines for lskin restrictions and the date for viewed. axreport_actionid of 4 is "View"

report_views.sql
content_copyfile_download
SELECT l.lskinid, l.refname 'Account', u.descrip1 'User', ar.recipient 'Email', r.id 'Report ID', r.subject 'Report Name', a.viewed
FROM axreport r
    JOIN axreport_action_log a ON a.axreportid = r.id
 JOIN lskin l ON l.lskinid = r.frn_lskinid
 LEFT JOIN axreport_recipient ar ON  ar.id = a.axreport_recipientID
    LEFT JOIN leuser u ON u.leuserid = ar.frn_leuserid
WHERE (r.frn_lskinid IN (7936,7964,7989,7990,8007,7966)
       OR l.refname LIKE 'MileOne - Heritage%')
  AND r.isActive = 1
    AND a.axreport_actionID = 4
   AND a.viewed >= '2017-06-08'
ORDER BY l.refname, u.descrip1, a.viewed desc