select 
	p.ProductName as p_name, v.Name as v_name, sv.VersionId.ToString() as v_id, sv.ChangesetId as cs, 
	i_s.ItemGuid as [searchItemGuid], s.searchId, sv.search_versionid, s.name as [searchName], 	
	(CASE WHEN (select COUNT(1) from search_version where searchId = sv.searchId and VersionId = sv.VersionId) = 1 THEN 0 ELSE 1 END) as [updated],
	(CASE WHEN EXISTS (SELECT null FROM dbo.search_version WHERE searchId = sv.searchId AND VersionId.IsDescendantOf(sv.VersionId)=1 AND VersionId <> sv.VersionId) THEN 1 ELSE 0 END) as [hasNewerVersion],	
	sv.searchxml, sv.UseSolr, sv.spName, sv.state, sv.spNameProxy, sv.stateProxy, sv.Deleted, sv.DateDeleted , sv.SolrDataVersion

from search s
	inner join item i_s on s.ItemID = i_s.itemid
	inner join search_version sv on s.searchId = sv.searchid
	inner join Version v 
		inner join Product p on p.ProductId = (select TOP 1 ProductId from ProductVersion pv where v.VersionId.IsDescendantOf(pv.VersionId) = 1)
	on sv.VersionId = v.VersionId

where 1=1 AND
	(
	 cast(sv.searchxml as nvarchar(max)) like '%digibatch_generic.%' OR
	 cast(sv.searchxml as nvarchar(max)) like '%digibatch_generic_jobinfo.%' OR
	 cast(sv.searchxml as nvarchar(max)) like '%member_asset_jobchain.%' OR
	 cast(sv.searchxml as nvarchar(max)) like '%member_asset_jobchain_jobinfo.%'
	)
order by s.name, sv.VersionId, sv.ChangesetId