VWEASUserTopMsgs.sql: EAS top user attachments

Author: Robert Lawson
Environment: Windows Server, Microsoft SQL Server, EAS
Description: This view simplifies access to EAS sytem tables and list of largest email attachments. Used in conjunction with vbscripts to report over-quota and weekly usage. See EASUsageAudit.vbs .
Code:

/*
File   : VWEASUserTopMsgs.sql
Update : 08-July-2006/Robert Lawson
Purpose: Get biggest emails for a given user
Notes  :
  - Size is in MB to be consistent with Quota and VWEASUserQuotaUsage
  - Works with VWEASUserQuotaUsage (gets the user information)

SELECT TOP 20 * FROM VWEASUserTopMsgs WHERE USERID = 9 ORDER BY MsgSize DESC
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VWEASUserTopMsgs]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VWEASUserTopMsgs]

CREATE VIEW dbo.VWEASUserTopMsgs AS
SELECT
RF.MSGID,
RF.USERID,
FL.FOLDERNAME,
PR.SUBJECT,
PR.FROMFLD,
PR.MSGDATE,
PR.MSGSIZE/1048576 MsgSize
FROM FOLDER FL, PROFILE PR, REFER RF
WHERE
RF.FOLDERID > 0 AND
RF.FOLDERID = (SELECT MAX(R2.FOLDERID) FROM REFER R2 WHERE
                R2.USERID = RF.USERID AND R2.MSGID = RF.MSGID AND R2.FOLDERID > 0) AND
PR.MSGID = RF.MSGID AND
FL.USERID = RF.USERID AND
FL.FOLDERID = RF.FOLDERID