In "Part 1" is showed a sql query example of how you could get a rolling total of all content added to a content database on a monthly basis. Here I will show how to further filter this query to exclude records of items that may exist within the recycle bin, items which are only document related, etc.
You may need to do this when you want to report on only documents and their versions that exist within lists and libraries, but not in Recycle Bins. Remember that once the default storage period of a recycle bin is reached, the record of the item is removed from all related tables, this could be problematic when you include the items in the recycle bin in your total item count (it will differ from a previous version of the report).
So...on with the examples:
Returning the amount of documents excluding those in recyclebin
In this example I need to join two tables: Alldocs and AllUserData; the reason for this is that I need to query the tp_deletetransactionid which is stored as a var Binary value in the AllUserData table.
USE {add your selected content database here}
SELECT DATENAME([month], x1.monthdate) + ' ' + DATENAME([year], x1.monthdate) AS [Month and Year], MAX(x1.added) AS [Added in Month], SUM(x2.added) AS [Rolling Total]
FROM
(SELECT MONTH(TimeCreated) AS monthno, DATEADD([month], DATEDIFF([month], 0, TimeCreated), 0) AS monthdate, COUNT(*) AS added FROM alldocs WHERE id IN (SELECT id FROM alldocstreams) GROUP BY DATEADD([month], DATEDIFF([month], 0, TimeCreated), 0), MONTH(TimeCreated)) AS x1
INNER JOIN
(SELECT MONTH(TimeCreated) AS monthno, DATEADD([month], DATEDIFF([month], 0, TimeCreated), 0) AS monthdate, COUNT(*) AS added FROM alldocs WHERE id IN (SELECT id FROM alldocstreams) GROUP BY DATEADD([month], DATEDIFF([month], 0, TimeCreated), 0), MONTH(TimeCreated)) AS x2
ON x1.monthdate >= x2.monthdate
GROUP BY x1.monthdate ORDER BY x1.monthdate
Returning all content excluding that which is created from Site Templates and Features
Whenever we use the default site templates and features avaiable to us in MOSS 2007 (and custom ones) a bunch of records are created in the AllDocs table for the files that are created from these templates and features. When it comes to reporting on actual content usage, it is important to keep these numbers out as they bump up the numbers when they should not (creating a site / activating a feature is not actual content usage). Obviously the clients definition of content usage may differ :)
USE {add your select content database here}
SELECT DATENAME([year], x1.monthdate) + '-' + RIGHT('00' + CONVERT(varchar, DATEPART(m, x1.monthdate)), 2) AS [Month and Year], MAX(x1.added) AS [Added in Month], SUM(x2.added) AS [Rolling Total]
FROM
(SELECT MONTH(AllUserData.tp_Created) AS monthno, DATEADD([month], DATEDIFF([month], 0, AllUserData.tp_Created), 0) AS monthdate, COUNT(*) AS added
FROM
AllDocs INNER JOIN
AllUserData ON AllDocs.LeafName = AllUserData.tp_LeafName AND
AllDocs.DirName = AllUserData.tp_DirName AND
AllDocs.ListId = AllUserData.tp_ListId
WHERE (AllDocs.SetupPath IS NULL) AND alldocs.id IN (SELECT id FROM alldocstreams)
GROUP BY DATEADD([month], DATEDIFF([month], 0, AllUserData.tp_Created), 0), MONTH(AllUserData.tp_Created)) x1
INNER JOIN
SELECT MONTH(AllUserData.tp_Created) AS monthno, DATEADD([month], DATEDIFF([month], 0, AllUserData.tp_Created), 0) AS monthdate, COUNT(*) AS added
FROM
AllDocs INNER JOIN
AllUserData ON AllDocs.LeafName = AllUserData.tp_LeafName AND
AllDocs.DirName = AllUserData.tp_DirName AND
AllDocs.ListId = AllUserData.tp_ListId
WHERE (AllDocs.SetupPath IS NULL) AND alldocs.id IN (SELECT id FROM alldocstreams)
GROUP BY DATEADD([month], DATEDIFF([month], 0, AllUserData.tp_Created), 0), MONTH(AllUserData.tp_Created)) ) x2
ON x1.monthdate >= x2.monthdate
GROUP BY x1.monthdate
Part 3 comming soon....