Hi Forum
I am hoping someone on here can give me some guidance/advice on the following.
I am writing a live report to average the size of compound emails across our whole fileplan so it could be quite a large number.
This is the SQL i am intending to use but i just wondered if someone on here might have done this already or can maybe take a quick look at my statement and check that it is doing the job efficiently ...
SQL:
Select AVG(SUM(d2.DataSize))/1024 "KB"
From DTree d1, DVersData d2
Where d2.DocID IN (Select DataID From DTree
Where ParentID IN(Select DataID From DTree Where Subtype = %1))
And d1.DataID = d2.DocID
Group By d1.ParentID
So basically what i am doing is first SQL is gathering all the Compound Email Parent containers (%1 is entered by the user so 557 for compound emails), SQL 2 is getting all the contents of all the parent compound email containers, SQL 3 is retrieving the DataSize from DVersData and grouping by the Parent ID, this way i am summing the Compound Emails instead of the sum of their contents in a whole, i am then getting the average of this list.
Is this the correct way to do this in your opinion ?
Regards
Mike.
