sql query with the full folder path

[jpergola329]

i have the following sql query via symantec tech support. it works great, but i need the whole folder path, if it resides in a sub folder.

ie: inbox\saved items etc.

what do i need to change

thanks

    USE EVVSEV8Store_1

     
     DECLARE @ArchivePointID Nvarchar(55)
     SET @ArchivePointID = '10F03B2291FB0114A9A5B0946574784D31110000EV8server'
      
     SELECT    
         AP.ArchivePointId,
         AP.ArchivePointIdentity,
         Archive.ArchiveName,
         ArchiveFolder.FolderName,
         COUNT(*) as CountByFolder,
         Vault.VaultID,
         Vault.VaultIdentity,    
     
         Vault.ArchivedItems as VaultArchivedItems,
         AP.ArchivedItems as APArchivedItems
         
     FROM
         EnterpriseVaultDirectory.dbo.root r1
         LEFT JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.RootIdentity = r2.ContainerRootIdentity
         JOIN EnterpriseVaultDirectory.dbo.Archive on r1.RootIdentity = archive.RootIdentity
         LEFT JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder on r2.RootIdentity = ArchiveFolder.RootIdentity
         RIGHT JOIN Vault on r2.VaultEntryId = Vault.Vaultid
         JOIN ArchivePoint AP on Vault.ArchivePointIdentity = AP.ArchivePointIdentity
         JOIN Saveset on Vault.VaultIdentity = Saveset.VaultIdentity
     WHERE 
         ArchivePointID = @ArchivePointID
             
     GROUP BY
         AP.ArchivePointId,
         Archive.ArchiveName,
         ArchiveFolder.FolderName,
         AP.ArchivePointIdentity,
         Vault.Vaultid,
             Vault.VaultIdentity,
         Vault.ArchivedItems,
         AP.ArchivedItems

[TonySterling · Accepted solution]

Something like this should work, I added CAST(AF.FolderPath AS NVARCHAR(MAX)) ,

  USE EVVSEV8Store_1

     
     DECLARE @ArchivePointID Nvarchar(55)
     SET @ArchivePointID = '10F03B2291FB0114A9A5B0946574784D31110000EV8server'
      
     SELECT    
         AP.ArchivePointId,
         AP.ArchivePointIdentity,
         Archive.ArchiveName,

         CAST(AF.FolderPath AS NVARCHAR(MAX)) ,
         ArchiveFolder.FolderName,
         COUNT(*) as CountByFolder,
         Vault.VaultID,
         Vault.VaultIdentity,    
     
         Vault.ArchivedItems as VaultArchivedItems,
         AP.ArchivedItems as APArchivedItems
         
     FROM
         EnterpriseVaultDirectory.dbo.root r1
         LEFT JOIN EnterpriseVaultDirectory.dbo.Root r2 on r1.RootIdentity = r2.ContainerRootIdentity
         JOIN EnterpriseVaultDirectory.dbo.Archive on r1.RootIdentity = archive.RootIdentity
         LEFT JOIN EnterpriseVaultDirectory.dbo.ArchiveFolder on r2.RootIdentity = ArchiveFolder.RootIdentity
         RIGHT JOIN Vault on r2.VaultEntryId = Vault.Vaultid
         JOIN ArchivePoint AP on Vault.ArchivePointIdentity = AP.ArchivePointIdentity
         JOIN Saveset on Vault.VaultIdentity = Saveset.VaultIdentity
     WHERE 
         ArchivePointID = @ArchivePointID
             
     GROUP BY
         AP.ArchivePointId,
         Archive.ArchiveName,
         ArchiveFolder.FolderName,

         CAST(AF.FolderPath AS NVARCHAR(MAX)) ,
         AP.ArchivePointIdentity,
         Vault.Vaultid,
             Vault.VaultIdentity,
         Vault.ArchivedItems,
         AP.ArchivedItems