-- Generate Non-Duplicated PARENT FOLDER List
DROP TABLE DataInventory;
CREATE TABLE DataInventory AS
Select * from (
		SELECT SERVER,
			REPLACE(
				Parent,
				"X:\","\\mpe.ca\datadrive\") AS UNCPath,  
		REPLACE(REPLACE(Parent,("\\mpe.ca\datadrive\" || Server),"/Shared/N-Data"),"\","/") AS EgnytePath,
		sum(FileCount) as FileCount, Sum(FileSize) as FileSize, 'Parent' as Source, Status from FolderData 
		Where Parent in (
			SELECT Parent FROM NonDupParent
		)
		AND FolderData.fld_MigrationID IS NULL
GROUP by UNCPath
)
union
SELECT SERVER,
	REPLACE(PATH,"X:\","\\mpe.ca\datadrive\") AS UNCPath,  
	REPLACE(REPLACE(PATH,("\\mpe.ca\datadrive\" || Server),"/Shared/N-Data"),"\","/") AS EgnytePath,
	FileCount, FileSize, 'Project' as Source, Status from FolderData 
Where FolderData.Project in (
	SELECT Project FROM NonDupProject)
AND FolderData.fld_MigrationID IS NULL;