Issue
It is often necessary to find the overall amount of storage used in Zantaz EAS.
Solution
In order to find this out use the following SQL script in attachments.
SELECT COUNT(USERS.USERID) as [No Archives], SUM(P4.[No Messages]) as [No Messages],
ROUND(SUM(CAST(P4.[Compressed Size GB] as real)),2) as [Total Vol GB]
FROM (SELECT USERID, COUNT(MSGID) AS [No Messages], SUM(COMPRESSEDSIZE * REF_USER / REF_TOTAL) / 1073741824 AS [Compressed Size GB]
FROM (SELECT U1.USERID, U1.MSGID, U1.REF_USER, P2.REF_TOTAL, P2.COMPRESSEDSIZE
FROM (SELECT USERID, MSGID, COUNT(MSGID) AS REF_USER
FROM REFER
GROUP BY USERID, MSGID) AS U1 INNER JOIN
(SELECT P1.MSGID, P1.COMPRESSEDSIZE,R.REF_TOTAL
FROM (SELECT MSGID, SUM(CAST(COMPRESSEDSIZE AS DECIMAL)) AS COMPRESSEDSIZE
FROM PROFILELOCATION
GROUP BY MSGID) AS P1 INNER JOIN
(SELECT MSGID, COUNT(MSGID) AS REF_TOTAL
FROM REFER AS REFER_1
GROUP BY MSGID) AS R ON P1.MSGID = R.MSGID) AS P2 ON U1.MSGID = P2.MSGID) AS P3
GROUP BY USERID) AS P4 INNER JOIN
USERS ON P4.USERID = USERS.USERID

Print Friendly, PDF & Email