Count of emails in the Monitoring Volume, by Day

How would I get a count of emails for each Day?

The monitoring volume has a structure of:

<Root>
----Year (subtype=751)
--------Month (subtype=751)
------------Day (subtype=751)
----------------Hour (subtype=751)
--------------------<buckets 1-n> (subtype=751)
------------------------Emails (subtype=749)

I can get a count down to the hour level, but am getting stuck there.
Here is what I have so far:

SELECT d1.DATAID, d1.parentid, d1.NAME, count(d2.dataid) Children FROM DTreeCore d1
join DtreeCore d2 on (d1.dataid = d2.parentid) and d1.subtype in (751,749)
and d1.parentid = %1
group by d1.dataid,d1.parentid,d1.name
union
SELECT d1.DATAID, d1.parentid, d1.NAME, count(d2.dataid) Children FROM DTreeCore d1
join DtreeCore d2 on (d1.dataid = d2.parentid) and d1.subtype in (751,749)
and d1.dataid = %1
group by d1.dataid,d1.parentid,d1.name
union
SELECT d1.DATAID, d1.parentid, d1.NAME, count(d2.dataid) FROM DTreeCore d1
join DtreeCore d2 on (d1.dataid = d2.parentid) and d1.subtype in (751,749)
and d1.dataid in
(select dataid from dtreecore where parentid in
(select dataid from dtreecore where parentid = %1) and subtype in (751,749))
group by d1.dataid,d1.parentid,d1.name

%1 = the DataID of the Year level
Results look similar to:

DATAID   parentid   NAME   Children
1137450   1119878       2016   2
1137451   1137450       11       1
1182522   1137451       30       13
1195621   1137450       12       31
1195622   1195621       1       24

So I can see that:
There are 2 Month folders in 2016
There is 1 Day folder in 11
There are 13 Hour folders in 30

If I was to do it manually, say for 30-11-2016 as an example, I would just use:
SELECT COUNT(*) FROM DTree
WHERE DataID IN (SELECT DataID FROM DTreeAncestors where AncestorID=1182522)
AND SubType = 749

But how would I do it for all days in 2016? Am I even on the right track here?

Find more posts tagged with

Comments

John Simon

If you use an Oracle DB you can use the old "connect by" query to traverse the tree. That buys you being able to use the LEVEL keyword so you can do something like "select dataid from dtree where level = 3 start with dataid = xxxx connect by prior dataid = parentid" to specify a specific level in the tree.

Using that you could "select count(*) from dtree dt join dtreeancestors dta on dta.ancestorid = dt.dataid where dta.ancestorid in (select dataid from dtree where level = 3 start with dataid = xxxx connect by prior dataid = parentid) and subtype = 749".

Cheers...

Walt Ruppar

Thanks for the reply John. No Oracle here... using SQL Server, but I think I sorted it out...

/* Enter DataID of Year Folder: */
SELECT Get_DTName(%1) AS 'Year',
   d1.parentid, Get_DTName(d1.parentid) AS 'Month',
   d1.DATAID, d1.NAME AS 'Day',
   (SELECT COUNT(*) FROM DTreeCore
       WHERE DataID IN
           (SELECT DataID FROM DTreeAncestors WHERE AncestorID=d2.parentid)
           AND SubType=749) AS 'DailyEmailCount'
FROM DTreeCore d1
JOIN DtreeCore d2 ON (d1.DataID = d2.ParentID) AND d1.SubType in (751,749)
AND d1.DataID IN
   (SELECT DataID FROM DTreecore
       WHERE parentid IN
       (SELECT DataID FROM DTreecore
/* Enter DataID of Year Folder: */
           WHERE ParentID = %1) AND Subtype IN (751,749))
GROUP BY d1.DataID,d1.ParentID,d1.Name,d2.ParentID

I created a function named Get_DTName that simply Returns the Object Name from DTree for a Given DataID so I didn't need to have so many union/joins

Colin_Schmidt

This is a modification (in ORACLE, though) of an LR I found in the Forum several years ago. It accumulates by the Hour. It can be modified to fit a variety of time periods: one Day, a Week, etc.. I don’t use it often, but it does indicate when certain activity takes place throughout a day. You can use it to indicate a particular Audit activity or in your case, a specific SubType. Originally, it was written to count the activity of a User throughout a 24-hour period. It’s useful for seeing activity in a company over a 24-hour period, especially one that may have many time zones in it’s operation.

I don’t know if it’s convertible to SQL Server or not. It has “UserID” in it, but that’s replaceable or expendable. It excludes the Admin ID, but it’s not necessary(I exclude it to remove my activity).

I don’t know who the author was, but it’s a gem. I seem to recall a French account user.

Colin J. Schmidt

From: eLink Entry: Content Server LiveReports Forum [mailto:livereportsdiscussion@elinkkc.opentext.com]
Sent: Thursday, June 01, 2017 8:44 AM
To: eLink Recipient <devnull@elinkkc.opentext.com>
Subject: Count of emails in the Monitoring Volume, by Day

Count of emails in the Monitoring Volume, by Day

Posted by Ruppar, Walt On 06/01/2017 08:41 AM

Thanks for the reply John. No Oracle here... using SQL Server, but I think I sorted it out...

I created a function named Get_DTName that simply Returns the Object Name from DTree for a Given DataID so I didn't need to have so many union/joins

[To post a comment, use the normal reply function]
Topic:	Count of emails in the Monitoring Volume, by Day
Forum:	Content Server LiveReports Forum
Content Server:	Knowledge Center CS16

The information contained in this e-mail is confidential and/or proprietary to Capital One and/or its affiliates and may only be used solely in performance of work or services for Capital One. The information transmitted herewith is intended only for use by the individual or entity to which it is addressed. If the reader of this message is not the intended recipient, you are hereby notified that any review, retransmission, dissemination, distribution, copying or other use of, or taking of any action in reliance upon this information is strictly prohibited. If you have received this communication in error, please contact the sender and delete the material from your computer.

HourlyActivity.txt