Ugh. Ik was er even van uitgegaan dat alle search engines hun query met “q=” beginnen. Dat doet Yahoo! dus niet. En dus wordt de Query From Hell nog langer:
select count(log_id) as tot, max(log_datetime) as latest, case charindex(‘search.yahoo.com’,log_referer) when 0 then case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1)) when 0 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1)) else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3)) end else case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1)) when 0 then lower(left(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1))-1)) else lower(left(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1))-3)) end end as log_referer from tblLog where log_referer LIKE ‘%q=%’ or log_referer LIKE ‘%p=%’ group by case charindex(‘search.yahoo.com’,log_referer) when 0 then case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1)) when 0 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1)) else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3)) end else case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1)) when 0 then lower(left(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1))-1)) else lower(left(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1))-3)) end end order by tot desc, case charindex(‘search.yahoo.com’,log_referer) when 0 then case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1)) when 0 then lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-1)) else lower(left(right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘q=’,log_referer)+1))-3)) end else case charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1)) when 0 then lower(left(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)-1),len(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1))-1)) else lower(left(right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)-1),charindex(‘&’,right(log_referer,len(log_referer)-charindex(‘p=’,log_referer)+1))-3)) end end
Jongens jongens. Misschien wel eens tijd dat ik daar een view van maak zeker?