Thanks,
-Bill

select count(DISTINCT(InstantKB_Articles.QuestionID))As QuantityofArticles FROM InstantKB_Articles INNER JOIN
InstantKB_ArticlesTreeNodes ON InstantKB_Articles.QuestionID = InstantKB_ArticlesTreeNodes.QuestionID INNER JOIN
InstantKB_TreeRoles ON InstantKB_ArticlesTreeNodes.NodeID = InstantKB_TreeRoles.NodeID INNER JOIN
InstantKB_Roles ON InstantKB_TreeRoles.RoleID = InstantKB_Roles.RoleID

To Count only public articles:

select count(DISTINCT(InstantKB_Articles.QuestionID))As QuantityofArticles FROM InstantKB_Articles INNER JOIN
InstantKB_ArticlesTreeNodes ON InstantKB_Articles.QuestionID = InstantKB_ArticlesTreeNodes.QuestionID INNER JOIN
InstantKB_TreeRoles ON InstantKB_ArticlesTreeNodes.NodeID = InstantKB_TreeRoles.NodeID INNER JOIN
InstantKB_Roles ON InstantKB_TreeRoles.RoleID = InstantKB_Roles.RoleID
where InstantKB_TreeRoles.RoleID = 1

You can change the where condition as you please. eg.: if you would like to see only public and customers articles you need to check via select * from InstantKB_Roles where the RoleID is the customer role id and then add OR statements like below:

where InstantKB_TreeRoles.RoleID = 1 or InstantKB_TreeRoles.RoleID = 3

Dear flyguy,

Sorry, I misunderstood. Please check below to see if this procedure can solve your problem:

Create Procedure CountArticleInCategory(@strNodeID nvarchar(75))
AS

SELECT Count(DISTINCT (InstantKB_Articles.QuestionID)) AS QtyArticleInCategory FROM InstantKB_Articles INNER JOIN
InstantKB_ArticlesTreeNodes ON InstantKB_Articles.QuestionID = InstantKB_ArticlesTreeNodes.QuestionID INNER JOIN
InstantKB_TreeRoles ON InstantKB_ArticlesTreeNodes.NodeID = InstantKB_TreeRoles.NodeID INNER JOIN
InstantKB_Roles ON InstantKB_TreeRoles.RoleID = InstantKB_Roles.RoleID
WHERE InstantKB_TreeRoles.NodeID IN ( SELECT distinct(t4.NodeID)  FROM InstantKB_Tree t1
INNER JOIN InstantKB_Tree t2 ON t1.Nodeid = t2.ParentNodeID or t2.Nodeid =  @strNodeID
INNER JOIN InstantKB_Tree t3 ON t2.Nodeid = t3.ParentNodeID or t3.Nodeid =  @strNodeID
INNER JOIN InstantKB_Tree t4 ON t3.Nodeid = t4.ParentNodeID or t4.Nodeid =  @strNodeID
where t1.parentnodeid =   @strNodeID   or t1.Nodeid =   @strNodeID )

return

GO

Please don't hesitate to contact us again if you have any further questions,


Kindest Regards,

Ryan Healey
Director / Developer

Explore our products...
http://demos.instantasp.co.uk/

Let me explain (this isn't going to be easy)....

My SQL is at best err basic to n00b status so if you reply please be as plain as possible.

I have been tasked with coming up with a KB status page (web page) with various amounts of figures on it that the senior managers can look over and see what's what.

I've done most of what they want but the last one is killing me.

I want a category count per individual category as per this post BUT this is where it gets tricky.  We have over 500 categories most END of the tree categories are named the same so we have

Product1 - with sub categories of faq, trouble shooting, training, user guides etc etc

product2 - with sub categories of faq, trouble shooting, training, user guides etc etc

select t.parentnodeid, t.nodename, count(*) 'Count' from instantkb_articlestreenodes AS a
join instantkb_tree as t
ON a.nodeid=t.nodeid
group by t.parentnodeid, a.nodeid, t.nodename
order by t.nodename

The above code gives me what I want but I kind figure out how to get the actual parentnodeid to show the actual nodename, in my example I have 0F6O0A - FAQ - 51 (parentnodeid - original nodeid - count).  So in this example if it showed (parentnodeid as nodename - end nodename - count) that would be ace

And to top it all off I am hoping to make it so that when a new category gets added (yes there willl be more) I don't have to go back to the web page stats and alter the code.

Any kind sole help me recover some hair I pulled out ??

This next SQL code gives a total count of articles which appear in both the nodes listed in the SQL.  Great for counting duplicates.