VWEASUserQuotaUsage.SQL: EAS usage view

Author: Robert Lawson
Environment: Windows Server, Microsoft SQL Server, EAS
Description: This view simplifies access to EAS sytem tables and provides key to inhouse Onecard system and match to Active Directory sAMAccount name. Used in conjunction with vbscripts to report over-quota and weekly usage. See EASUsageAudit.vbs and EASNearQuotaEmail.vbs .
Code:

/*
File   : VWEASUserQuotaUsage.SQL
Update : 08-July-2006/Robert Lawson
Purpose: Usage and quota for EAS users (only users with usage)
Notes  :
  - Size and Quota are expressed in MB, Quota = -1, is unlimited
  - Works with VWEASUserTopMsgs

SELECT * FROM VWEASUserQuotaUsage
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[VWEASUserQuotaUsage]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[VWEASUserQuotaUsage]

CREATE VIEW dbo.VWEASUserQuotaUsage AS
SELECT
US.USERID,
RTRIM(US.USERNAME) AS UserName,
US.EASQUOTAOPTION,
QuotaSize = CASE WHEN
  US.EASQUOTAOPTION = 1 THEN GP.EASQUOTA
  ELSE US.EASQUOTA
END,
GP.GROUPNAME,
QuotaType = CASE WHEN
  US.EASQUOTAOPTION = 1 THEN 'Group'
  ELSE 'User'
END,
RTRIM(RIGHT(NT.NTACCOUNT,(LEN(NT.NTACCOUNT) - CHARINDEX('\',NT.NTACCOUNT)))) AS sAMAccountName,
RTRIM(RIGHT(NT.NTACCOUNT,(LEN(NT.NTACCOUNT) - CHARINDEX('\',NT.NTACCOUNT)))) + '@uni.edu' AS Email,
COUNT(*) AS MsgCount,
SUM(CAST(PR.MSGSIZE AS BIGINT))/1048576 AS MsgSize
FROM  PROFILE PR, REFER RF, GROUPS GP, NTACCOUNT NT, USERS US
WHERE
US.EASSTATUS = 0 AND
(US.GROUPID > 0 AND US.GROUPID <> 2) AND
NT.USERID = US.USERID AND
GP.GROUPID = US.GROUPID AND
RF.USERID = US.USERID AND
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
GROUP BY US.USERID, US.USERNAME, US.EASQUOTAOPTION, GP.EASQUOTA, US.EASQUOTA, NT.NTACCOUNT, GP.GROUPNAME