SQL Query to Count number of documents with over 40 activities

1

I am trying to run a query to retrieve distinct count of documents with more than 40 activities. There are 4 tables ( Documents, DocumentHistory, Folders, FolderItems ). Required Fileds for the tables are:

Document: DocName, DOCNUM, EDITWHEN
DocumentHistory : DOCNUM, Activity, DOCUser
Folders: FLD_ID, FLD_PID,FLD_NAME
FolderItems: FLD_ID, Item_ID

In Folders Table: FLD_ID is the identifier and FLD_PID is the parent of the folder.FLD_PID is null for the folders that don't have a parent.
In FolderItems Table: FLD_ID is the location ID matching FK FLD_ID in Folders Table and Item_ID is FK for DOCNUM in Document Table

I revised the query as below:

WITH RecursiveFolders AS(
SELECT FI.FLD_ID, FL.Item_ID
FROM [FolderItems] FI
WHERE FI.FLD_ID IN(
SELECT DISTINCT F.FLD_ID
FROM [Folders] F
WHERE F.FLD_PID = 860)
)
,
DocumentCounts AS(
SELECT D.DocNum, F.FLD_Name, D.DocName, DH.DocUser, DH.Activity, D.EditWhen AS ModifiedDate,
COUNT (DH.Activity) OVER(PARTITION BY D.DocNum) AS ActivityCount
FROM [Documents] D
JOIN [DocumentHistory] DH ON D.DocNum = DH.DocNum
JOIN [FolderItems] FI ON D.DocNum = FI.Item_ID
JOIN [Folders] F ON FI.FLD_ID = F.FLD_ID
)
SELECT DC.DocNum, DC.DocName, DC.DocUser , DC.Activity, DC.ActivityCount
FROM DocumentCounts DC
JOIN RecursiveFolders RF ON DC.DocNum = RF.FLD_ID
WHERE DC.ActivityCount >40

Thanks

I want to get distinct count for documents to count documents that are having more than 40 actions in a folder number 860. This folder contains some subfolders and those subfolders have subs too. The loop should be through two tables only : Folders and FolderItems: First to check all FLD_PID in Folders to be 860 and then retrieve all FLD_IDs and check them in FolderItems by FLD_ID. Also it needs to check the returned FLD_IDs from FLD_PID=860, in the other SELECT where FLD_PID equals those returned FLD_IDs.

I tried this query but it is not working, can someone help me fix this? Thanks

2

hi

hope this helps

; with cte as 
(
SELECT 
     D.DOCNUM
	 , count(Activity) as cnt 
FROM 
    [Documents] D
      JOIN 
	[DocumentHistory] DH ON D.DocNum = DH.DocNum
      JOIN 
    [FolderItems] FI ON D.DocNum = FI.Item_ID
      JOIN 
	( SELECT * FROM [Folders] WHERE FLD_PID = 860) F ON FI.FLD_ID = F.FLD_ID
GROUP BY 
     D.DOCNUM
HAVING 
    count(Activity) > 40 
)
SELECT 
  count ( distinct DOCNUM ) 
FROM 
  cte
1 Like
3

Thanks so much Harish.