Select v_Collection.CollectionID,
v_Collection.Name,
v_Collections_G.LimitToCollectionID as 'LimitToCollectionID',
Collections.LimitToCollectionName as 'LimitingCollectionName',
vSMS_CollectionDependencies.SourceCollectionID as 'Dependant Collection ID',
Case When
vSMS_CollectionDependencies.relationshiptype = 1 then 'Limited '
when vSMS_CollectionDependencies.relationshiptype = 2 then 'Included '
when vSMS_CollectionDependencies.relationshiptype = 3 then 'Excluded '
end as 'Included or excluded',
Case when dbo.v_Collections_G.CollectionType = 1 Then 'User' when dbo.v_Collections_G.CollectionType = 2 Then 'Device' Else 'Others' End as 'CollectionType',
v_Collection.MemberCount as 'MembersCount',
Collections.BeginDate as 'CreatedDate',
pck.Name AS Package_name,
app.ApplicationName,
FullPath AS 'Collection_Path'
from v_Collection
inner Join vFolderMembers ON v_Collection.CollectionID = vFolderMembers.InstanceKey
and vFolderMembers.ObjectTypeName='SMS_Collection_Device'
left join dbo.v_Collections_G on dbo.v_Collection.CollectionID = dbo.v_Collections_G.SiteID
left join dbo.Collections on dbo.v_Collection.CollectionID = dbo.Collections.SiteID
left join dbo.v_CollectionRuleQuery ON dbo.v_Collection.CollectionID = dbo.v_CollectionRuleQuery.CollectionID
left join v_Advertisement adv on v_Collection.CollectionID=adv.CollectionID
left join v_Package pck on adv.PackageID=pck.PackageID
left join v_ApplicationAssignment app on app.CollectionID=v_Collection.CollectionID
left join vSMS_CollectionDependencies on vSMS_CollectionDependencies.DependentCollectionID = v_Collection.CollectionID
Left Join
(Select L1.Name,L1.ContainerNodeID,IsNull(L6.Name+'\','')+IsNull(L5.Name+'\','')+IsNull(L4.Name+'\','')+IsNull(L3.Name+'\','')+IsNull(L2.Name+'\','')+IsNull(L1.Name,'') as FullPath
from vSMS_Folders as L1
left join vSMS_Folders as L2 on L1.ParentContainerNodeID=L2.ContainerNodeID
left join vSMS_Folders as L3 on L2.ParentContainerNodeID=L3.ContainerNodeID
left join vSMS_Folders as L4 on L3.ParentContainerNodeID=L4.ContainerNodeID
left join vSMS_Folders as L5 on L4.ParentContainerNodeID=L5.ContainerNodeID
left join vSMS_Folders as L6 on L5.ParentContainerNodeID=L6.ContainerNodeID) as Mapping
on mapping.ContainerNodeID = vFolderMembers.ContainerNodeID
