;WITH CTE AS (
SELECT [RGTCID]
,ltrim(rtrim(T.N.value('.','varchar(100)'))) as Querystr
FROM [R_GT_Collection]
cross apply RGTCData.nodes('//*[@data-type="Collection"]/@Query') T(N)
where N.value('../@data-from','varchar(100)') is null
)
,CollectionIDs AS(
select y.t_str,count(distinct x.RGTCID) CollectionCount from cte x
CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y
group by y.t_str
)
,CTE2 AS (
SELECT [RGTBID]
,ltrim(rtrim(T.N.value('.','varchar(100)'))) as Querystr
FROM [R_GT_Brand]
cross apply RGTBData.nodes('//*[@data-type="Collection"]/@Query') T(N)
where N.value('../@data-from','varchar(100)') is null
)
,CollectionIDs2 AS(
select y.t_str,count(distinct x.RGTBID) CollectionCount from cte2 x
CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y
group by y.t_str
)
,CTE3 AS (
SELECT [RGTGID]
,ltrim(rtrim(T.N.value('.','varchar(100)'))) as Querystr
FROM [R_GT_Guru]
cross apply RGTSData.nodes('//*[@data-type="Collection"]/@Query') T(N)
where N.value('../@data-from','varchar(100)') is null
)
,CollectionIDs3 AS(
select y.t_str,count(distinct x.RGTGID) CollectionCount from cte3 x
CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y
group by y.t_str
)
,CTE4 AS (
SELECT [RGTSID]
,ltrim(rtrim(T.N.value('.','varchar(100)'))) as Querystr
FROM [R_GT_Story]
cross apply RGTSData.nodes('//*[@data-type="Collection"]/@Query') T(N)
where N.value('../@data-from','varchar(100)') is null
)
,CollectionIDs4 AS(
select y.t_str,count(distinct x.RGTSID) CollectionCount from cte4 x
CROSS APPLY dbo.f_split(SUBSTRING(Querystr,CHARINDEX('(',Querystr)+1,CHARINDEX(')',Querystr)-CHARINDEX('(',Querystr)-1),',') y
group by y.t_str
)
select c.CollectionID,isnull(m.CollectionCountSum,0) as CollectionCountSum FROM [R_GT_Collection] c
left join (
select t_str,sum(CollectionCount) as CollectionCountSum from (
select t_str,CollectionCount from CollectionIDs
union all
select t_str,CollectionCount from CollectionIDs2
union all
select t_str,CollectionCount from CollectionIDs3
union all
select t_str,CollectionCount from CollectionIDs4
) z
group by t_str
) m
on c.CollectionID=m.t_str
order by 1
