Problem:
How can we get the expanded native file size for a given tag.
Resolution
The following query will return native file size for the given Tag name. Please run it in your project database.
SELECT
[tbl_ex_tag].TagName, SUM([tbl_ex_FileInfo].FileSize)/POWER(2.0,30) AS TotalFileSizeBytes
FROM
[tbl_ex_FileInfo]
LEFT JOIN [tbl_ex_filetagassociation] ON [tbl_ex_FileInfo].FileID = [tbl_ex_filetagassociation].FileID
LEFT JOIN [tbl_ex_tag] ON [tbl_ex_tag].TagID = [tbl_ex_filetagassociation].TagID
WHERE
[tbl_ex_tag].TagName = '<Tagname>' --Provide your tag name here
GROUP BY
[tbl_ex_tag].TagName
Now, for HTML/MHT file size for email files and native file size to other docs please run below script in your project database.
SELECT
( SELECT TagName FROM tbl_ex_tag WITH(NOLOCK) WHERE TagId=fta.TagID) AS [TAG Name],
ISNULL(COUNT(*),0) as [Total Count],
ISNULL(SUM(ISNULL(HTML.FileSize,FI.FileSize)),0) /POWER(1024.0,2) as [Total Files Size in MB]
FROM
tbl_ex_tag T WITH(NOLOCK)
INNER JOIN
tbl_ex_FileTagAssociation fta WITH(NOLOCK)
ON
T.TagName='<Tag name>' --Provide your tag name here
AND
T.TagId=fta.TagId
INNER JOIN
tbl_ex_FileInfo fi WITH(NOLOCK)
ON
fta.Fileid = fi.Fileid
LEFT JOIN
tbl_ex_EmailMeta EM WITH(NOLOCK)
ON
EM.FileId=fta.fileid
LEFT JOIN
tbl_ex_HTMLFileLocation HTML WITH(NOLOCK)
ON
HTML.FileId=EM.FileId
GROUP BY
fta.TagId
Comments
0 comments
Please sign in to leave a comment.