|
|
Send us your question
|
| ______________________________________________________ |
| the Search Position Tracker |
The application searches for the location of the specified keywords in selected search engines (google, seznam.cz) for a given URL.
Result is stored in the database.
|
|
| ______________________________________________________ |
|
| Search Position Manager. |
|
the app. SearchPositionManager.exe, manages Search Position Tracker for keyword search..
|
|
|
| DB diagram |
|
| PROCEDURE [dbo].[sp_GetResult] |
| This procedure dynamically generates a script that converts keywords to columns using the expression "PIVOT". This displays a clear table with the result of the position. |
|
| ______________________________________________________ |
|
-- =============================================
-- Author: Pavel Pindora
-- Create date: 20.02.2020
-- Description: Get Results from Position Tracer
-- =============================================
ALTER PROCEDURE [dbo].[sp_GetResult]
@projTyp int = 3,
@QueryURL NVARCHAR(500) = 'google',
@QueryURLNotLike NVARCHAR(100) = '*************'
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @project NVARCHAR(300) = 'Retro elektro Vacuum Sez GGLen',
@columns NVARCHAR(MAX) = '',
@colsWit NVARCHAR(MAX) = '',
@sql NVARCHAR(MAX) = '';
set @project = case when @projTyp=1 then 'Retro eletro Kategorie Sez GGLen'
when @projTyp=2 then 'Retro elektro Vacuum Sez GGLen'
when @projTyp=3 then 'Retro elektro shop Producers Sez GGLen'
when @projTyp=4 then 'Retro elektro RAM Sez GGLen'
when @projTyp=5 then 'Retro elektro shop Sez GGLen'
when @projTyp=6 then 'Retro elektro PC Cards'
end
SELECT @columns+=QUOTENAME(s_KeyWord) + ',' FROM [dbo].[tbl_Result] where s_Projekt=@project group by s_KeyWord
SELECT @colsWit+='IsNull(' + QUOTENAME(s_KeyWord) + ','''') as [' + s_KeyWord +'],' FROM [dbo].[tbl_Result] where s_Projekt=@project group by s_KeyWord;SET @colsWit = LEFT(@colsWit, LEN(@colsWit) - 1);
-- remove the last comma
SET @columns = LEFT(@columns, LEN(@columns) - 1);
SET @sql ='
;with pivoted (Project,Query,Date,URL,' +@columns + ') as
(
select * from
(
select tr.s_Projekt,
case
when s_QueryString like ''%google%;cs%'' then ''google;cs''
when s_QueryString like ''%google%'' then ''google''
when s_QueryString like ''%seznam%'' then ''seznam'' end as s_QueryString,
s_KeyWord,
--n_Count,
IsNull(n_ListPosition,''0'') as n_ListPosition,
d_Date
,s_URL
from [dbo].[tbl_Result] tr
inner join [tbl_SearchPT] tpt on fk_tbl_SearchPT=pk_tbl_SearchPT
where n_Count>0
and tr.s_Projekt='''+ @project +'''
and s_QueryString like ''%' + @QueryURL + '%'' and s_QueryString not like ''%' + @QueryURLNotLike + '%''
) d
pivot
(
max( n_ListPosition )
for s_KeyWord in ('+ @columns +')
) piv
)select Project,Query,Date,URL,' +@colsWit + ' from pivoted order by Date desc'
;EXECUTE sp_executesql @sql;
END