0

I have the following scenario which I am trying to execute in my SQL Server:

     Root Folder (dataID - 001)
                 Folder A (dataid-123)
                       child folder 1 
                             doc1 (size-6 mbs)
                             doc2  (size- 10 mbs)   
                       child folder 2
                             doc3  (size - 1 mbs)
                             doc4  (size- 50 mbs)
                 Folder B (dataId- 456)
                       child folder 1 
                             doc1 (size-4 mbs)
                             doc2  (size- 8 mbs)    
                       child folder 2
                             doc3  (size - 8 mbs)
                             doc4  (size- 3 mbs)
                       child folder 3
                             doc5  (size - 3 mbs)
                             doc6  (size- 7 mbs)
                                   SubChild folder 1 for child folder 3
                                             doc 7 (size - 9 mbs)
                                                   SubSubChild Folder1 for SubChild folder1                          
                                                         doc 8 (size 5 mbs)

The output format that I am trying to get is below :

              Name       Count of Docs   Total Folder Size:
              FolderA        4               67Mbs
              FolderB        8               47Mbs

The query that I have formed allows me to only get the size and count of all the docs if i give the Root Folder ID:

              WITH n(DataID, Name,ParentID,SubType,versionnum) AS 

             (SELECT DataID, Name ,ParentID,SubType,versionnum
              FROM DTreeCore
              WHERE DataID = 001
              UNION ALL
              SELECT nplus1.DataID, nplus1.Name ,nplus1.ParentID,nplus1.SubType,nplus1.versionnum 
              FROM DTreeCore as nplus1, n
              WHERE n.DataID = nplus1.ParentID)

              SELECT COUNT(d3.docid) AS "Number of Docs",SUM(d3.datasize) /(1024 *1024) "Megabytes" 
              FROM
              n,dversdata d3 
              WHERE 
              n.dataid = d3.docid AND 
               n.versionnum = d3.version

Please advise on how can I achieve the required output. Thanks !

Dale K
  • 25,246
  • 15
  • 42
  • 71
nemish nigam
  • 65
  • 2
  • 10
  • Change the `WHERE` in the anchor part to `WHERE DataID IN (123, 456)`? Or if you want everything, then `WHERE DataID IS NOT NULL` gets all first level folders I think – Charlieface Feb 07 '21 at 21:22
  • I am looking for all the first level folders and their size. I did try WHERE dataid in (123,456) earlier but that didn't quite get me what i was looking for. – nemish nigam Feb 08 '21 at 03:29
  • @nemishnigam Is it possible to have a SQLFidder kind of example so that it will be more easier to provide a solution? – Sathiya Kumar V M Feb 08 '21 at 06:32

0 Answers0