Need help with SQL query to display count by retentioncategory

[Aaron_Morris]

Hello,

I have a web page that displays the usage of a person's vault by retention category.  However, the query I used is based on one floating around here that doesn't take in to account items that have been deleted from their categories.  Basically, i've figured out two queries, but need to figure out how to combine them to get one query that gives me everything.

Here is the query that returns the count of items in categories for a user (run against your mailbox store DB):

SELECT VaultIdentity, ex.MbxDisplayName,rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count1, SUM(s.ItemSize)/1024 AS Size
FROM Saveset s Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
WHERE MbxDisplayName LIKE 'Aaron Morris'
GROUP BY VaultIdentity, s.ArchivePointIdentity, rc.RetentionCategoryName, ex.MbxDisplayName

Here is a query that displays the items for that user's archive that are deleted:

SELECT     VaultIdentity, COUNT(VaultIdentity) AS Count
FROM         JournalDelete
WHERE     (ArchiveId = '11328D641BCD60D4C93947014D4C1D43D1110000archive') AND (DeletionStatus = '0')
GROUP BY VaultIdentity
ORDER BY VaultIdentity

So how do I craft one query that basically exludes the results of the second query in the first one?

Thanks!

[MichelZ]

I can't exactly follow on what your goal is here.

Can you elaborate further?

Cheers

[Aaron_Morris]

Hello,

I'm trying to get a count of actual messages and sizes in a user's archive by retention category.  The first query does that, but it includes the items that are recoverable.  Basically I want the real active messages by retention category.  The second query shows all of the messages that are recoverable.  I asically want to have the first query exclude the results of the 2nd.

That way I'll get accurate info about how much data is really in each retention category for a user.

Thanks!

[tmurray1]

Arron,

You might be able to use NOT EXISTS. For example:

SELECT column1, column2, column3

FROM table1 T1

WHERE not exists(SELECT column1, column2, column3 FROM table2 T2 WHERE T1.column1 = T2.column1

)

The result set would consist of records from T1 table where there are no records in T2 table for the given column1

Looks like the queries you supplied would have to be modified some to make this work. 

The query above was modified from the example found at http://www.techonthenet.com/sql/exists.php. Their example of the use of NOT EXISTS with the suppliers and orders tables is helpful.

[Aaron_Morris]

Hello,

I've actually tried the NOT EXISTS with no luck, it excludes too much data.  This is quite the pain!

[eware]

We do a not exists (see the part in italics). We actually don't care about the DeletionStatus, we omit all records in the JournalDelete from our counts to users.

Please note, I suggest you add the with (nolock) section to your query, that will, keep your query from locking the tables.

SELECT VaultIdentity, ex.MbxDisplayName,rc.RetentionCategoryName, COUNT(s.RetentionCategoryIdentity) as Count1, SUM(s.ItemSize)/1024 AS Size
FROM Saveset s Join Archivepoint a on a.ArchivePointIdentity =s.ArchivePointIdentity
Join EnterpriseVaultDirectory.dbo.RetentionCategoryEntry rc on rc.RetentionCategoryIdentity = s.RetentionCategoryIdentity
Join EnterpriseVaultDirectory.dbo.ExchangeMailboxEntry ex on ex.DefaultVaultID = a.ArchivePointID
WHERE MbxDisplayName LIKE 'Aaron Morris'
AND  NOT EXISTS (    SELECT 1 FROM JournalDelete JD with (nolock)                     WHERE JD.ArchivePointIdentity = S.ArchivePointIdentity                     AND JD.ItemSeqNo = S.IndexSeqNo                     AND JD.DeletionStatus = 0 )

[Liam_Finn1]

Arron,

Have you found a solution to this? If yes please mark a post as the solution