· 9 years ago · Jan 31, 2017, 09:52 PM
1USE [LocalMarketData]
2GO
3/****** Object: StoredProcedure [dbo].[SearchMarketData] Script Date: 1/31/2017 2:58:21 PM ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE [dbo].[SearchMarketData]
9@Company_ID int, @UserSession_ID int, @PayMarket_ID varchar(50), @SearchText varchar(500), @SearchDescription varchar(500), @Publishers varchar(MAX), @Surveys varchar(MAX), @JobFamilies varchar(MAX), @JobLevels varchar(MAX), @Country varchar(50), @Year varchar(4), @MinRange float, @MaxRange float, @Industry_Label varchar(255), @Industry_Value varchar(255), @Size_Label varchar(255), @Size_Value varchar(255), @Geo_Label varchar(255), @Geo_Value varchar(255), @DefaultScope varchar(255), @WeightingType varchar(50), @ScopeSearch varchar(500), @ShowAll varchar(50), @User_ID int
10WITH EXEC AS CALLER
11AS
12SET ANSI_WARNINGS OFF
13
14Declare @Effective_Date datetime,@Aging_Factor float,@Rate varchar(10),@Currency varchar(10),@Base_Reference_Point float,@TCC_Reference_Point float,@Bonus_Reference_Point float,@TCCTarget_Reference_Point float
15Declare @Publisher varchar(255),@Source varchar(255),@Job_Title varchar(255),@job_Code varchar(50),@Job_Family varchar(255),@Job_Level varchar(255),@Base50 float,@TCC50 float,@CutType varchar(10),@CutId varchar(50),@Job_ID varchar(255),@Job_Type int,@Survey_Job_ID int
16Declare @Payfactors binary,@Survey binary
17Declare @Filters varchar(MAX),@Results varchar(MAX)
18Declare @ReturnText varchar(MAX)
19Declare @sqlCommand varchar(MAX)
20Declare @DefaultCountry varchar(50),@matches int,@country_code varchar(10),@HasDefaultScopes bit
21
22--if we have default scopes...apply filter
23Set @HasDefaultScopes=0
24Select @HasDefaultScopes=1 where exists (Select 1 from CompanySurveyScopes where Company_ID = @Company_ID AND CompanyPayMarket_ID = @PayMarket_ID)
25if @HasDefaultScopes=0
26BEGIN
27 set @DefaultScope=''
28END
29
30
31Set @Filters=''
32Set @Results=''
33
34Print 'Start'+convert(varchar(50),Datepart(Ss,getdate()))
35
36Set @MinRange=isnull(@MinRange,'')
37Set @MaxRange=isnull(@MaxRange,'')
38
39
40--RESULTS
41create table #Results(Publisher varchar(255),Survey varchar(255),Source varchar(255),Job_Title varchar(255),Job_Code varchar(50),Job_Family varchar(255),Job_Level varchar(255),Base50 float,TCC50 float,CutType varchar(10),CutId varchar(50),Job_ID varchar(255),Job_Type int,Survey_Job_ID int,SurveyTitle_ID int,matches int,country_code varchar(10))
42CREATE INDEX IDX1_Results ON #Results(Survey_Job_ID,Publisher,Survey,Job_Family,Job_Level,Base50)
43CREATE INDEX IDX2_Results ON #Results(Survey_Job_ID)
44CREATE INDEX IDX3_Results ON #Results(SurveyTitle_ID,Job_Code)
45
46create table #FilterResults(Publisher varchar(255),Survey varchar(255),Source varchar(255),Job_Title varchar(255),Job_Code varchar(50),Job_Family varchar(255),Job_Level varchar(255),Base50 float,TCC50 float,CutType varchar(10),CutId varchar(50),Job_ID varchar(255),Job_Type int,Survey_Job_ID int,SurveyTitle_ID int,matches int,country_code varchar(10))
47
48
49
50Select
51@Effective_Date=Effective_Date,
52@Aging_Factor=Aging_Factor,
53@Rate=Rate,
54@Currency=Currency,
55@Base_Reference_Point=Base_Reference_Point,
56@TCC_Reference_Point=TCC_Reference_Point,
57@Bonus_Reference_Point=Bonus_Reference_Point,
58@TCCTarget_Reference_Point=TCCTarget_Reference_Point
59from usersessions where usersession_id=@UserSession_ID
60
61--If PayMarket exists...override project currency
62if isNull(@PayMarket_ID,'')<>''
63 Select @Currency=Currency_Code from CompanyPayMarkets where CompanyPayMarket_ID=@PayMarket_ID
64
65--PayFactors/Survey or both?
66if @SearchText<>'' or @SearchDescription<>'' or @JobFamilies<>'' or @JobLevels <>''
67Begin
68 --if (@Publishers='PayFactors' or @Surveys='PayFactors')-- or CHARINDEX('PayFactors',@Publishers)>0) and (@Surveys='' or CHARINDEX('PayFactors',@Surveys)>0)
69 Set @PayFactors=1
70
71 --if @Publishers<>'PayFactors'-- and @Surveys<>'PayFactors'
72 Set @Survey=1
73End
74
75if @PayFactors=1
76Begin
77 --360 Jobs
78 if @PayMarket_ID='' or @PayMarket_ID='NULL'
79 Select @PayMarket_ID=(Select us.companypaymarket_id from userscopelisttemp us where us.usersession_id=@UserSession_ID)
80
81 insert into #FilterResults (Publisher,Survey,Source,Job_Title,job_Code,Job_Family,Job_Level,Base50,TCC50,CutType,CutId,Job_ID,Job_Type,Survey_Job_ID,matches,country_code)
82 (Select 'PayFactors' as Publisher,'PayFactors' as Survey, 'PayFactors' as Source, Job_Title,job_Code,Job_Family,
83 job_title_level,case when @Rate='Annual' then dbo.convertrate(dbo.ConvertCurrency_New(Base_50_Annual*1000,(Select cc.currency_code from countrycurrency cc where cc.country_code=mdjobs.country_code),@Currency,@Effective_Date),'Annual',@rate)/1000 else dbo.convertrate(dbo.ConvertCurrency_New(Base_50_Annual*1000,(Select cc.currency_code from countrycurrency cc where cc.country_code=mdjobs.country_code),@Currency,@Effective_Date),'Annual',@rate) end as Base50,case when @Rate='Annual' then dbo.convertrate(dbo.ConvertCurrency_New(TCC_50_Annual*1000,(Select cc.currency_code from countrycurrency cc where cc.country_code=mdjobs.country_code),@Currency,@Effective_Date),'Annual',@rate)/1000 else dbo.convertrate(dbo.ConvertCurrency_New(TCC_50_Annual*1000,(Select cc.currency_code from countrycurrency cc where cc.country_code=mdjobs.country_code),@Currency,@Effective_Date),'Annual',@rate) end as TCC50,'360Jobs' as CutType, job_code as CutId,job_title as Job_ID, 1 as Job_Type,NULL as Survey_Job_ID,
84 (Select count(1) from companyjobs_pricings cp,companyjobs_pricingsmatches cpm where cp.company_id=@Company_ID and cp.CompanyJobPricing_ID = cpm.CompanyJobPricing_ID and cpm.mdjob_code=mdjobs.job_code and cp.effective_Date=(Select max(cpp.effective_date) from companyjobs_pricings cpp where cpp.companyjob_id=cp.companyjob_id and cpp.companypaymarket_id=cp.companypaymarket_id) ) as matches,
85 mdjobs.country_Code
86 from mdjobs
87 join country on country.country_code=mdjobs.country_code and (@Country='' or @Country='All' or @Country=country.Country_Name) and country.country_code=(Select cp.country_code from companypaymarkets cp where cp.companypaymarket_id=@PayMarket_ID) and (country.country_code='USA' or exists (Select 1 from companydatasets cds where cds.Company_ID=@Company_ID and cds.Country_Code=country.country_code))
88 where Base_50_annual>0
89 and (@SearchText='' or (job_title like '%'+@SearchText+'%' or job_code like '%'+@SearchText+'%'))
90 and (@SearchDescription='' or job_description like '%'+@SearchDescription+'%')
91 --and (@Publishers='' or CHARINDEX('PayFactors',@Publishers)>0)
92 --and (@Surveys='' or CHARINDEX('PayFactors',@Surveys)>0)
93 --and (@Surveys='' or 'PayFactors' = @Surveys )
94 --and (@JobFamilies='' or @JobFamilies=Job_Family)
95 --and (@JobLevels='' or Job_Level = @JobLevels )
96 and (isnull(@MinRange,'')='' or @MinRange<case when @Rate='Annual' then dbo.convertrate(dbo.ConvertCurrency_New(Base_50_Annual*1000,(Select cc.currency_code from countrycurrency cc where cc.country_code=mdjobs.country_code),@Currency,@Effective_Date),'Annual',@rate)/1000 else dbo.convertrate(dbo.ConvertCurrency_New(Base_50_Annual*1000,(Select cc.currency_code from countrycurrency cc where cc.country_code=mdjobs.country_code),@Currency,@Effective_Date),'Annual',@rate) end)
97 and (isnull(@MaxRange,'')='' or @MaxRange>case when @Rate='Annual' then dbo.convertrate(dbo.ConvertCurrency_New(Base_50_Annual*1000,(Select cc.currency_code from countrycurrency cc where cc.country_code=mdjobs.country_code),@Currency,@Effective_Date),'Annual',@rate)/1000 else dbo.convertrate(dbo.ConvertCurrency_New(Base_50_Annual*1000,(Select cc.currency_code from countrycurrency cc where cc.country_code=mdjobs.country_code),@Currency,@Effective_Date),'Annual',@rate) end)
98 and @DefaultScope=''
99 and @WeightingType=''
100 and @ScopeSearch=''
101 )
102
103
104
105
106End
107
108PRINT '360 Jobs'+convert(varchar(50),Datepart(Ss,getdate()))
109
110if @Survey=1
111Begin
112
113 if @PayMarket_ID='' or @PayMarket_ID='NULL'
114 Select @PayMarket_ID=(Select us.companypaymarket_id from userscopelisttemp us where us.usersession_id=@UserSession_ID)
115
116 set @sqlCommand='insert into #FilterResults (Publisher,Survey,Source,Job_Title,job_Code,Job_Family,Job_Level,Base50,TCC50,CutType,CutId,Job_ID,Job_Type,Survey_Job_ID,SurveyTitle_ID,matches,country_code) '
117 set @sqlCommand=@sqlCommand+'(Select Survey_Publisher as Publisher,Survey_Name as Survey, Survey_Publisher + '' - '' + Survey_Name as Source, left(Job_Title,50) as Job_Title,job_Code,'
118 --set @sqlCommand=@sqlCommand+'(Select max(md.job_family) from MDJobs md,mdjobssurveyjobmatch mdjsm where mdjsm.Survey_ID=s.survey_id and md.job_code=mdjsm.MDJob_Code and mdjsm.Survey_Job_ID=sj.survey_job_id and md.job_family is not NULL) as Job_Family,'
119 --set @sqlCommand=@sqlCommand+'(Select max(md.job_level) from MDJobs md,mdjobssurveyjobmatch mdjsm where mdjsm.Survey_ID=s.survey_id and md.job_code=mdjsm.MDJob_Code and mdjsm.Survey_Job_ID=sj.survey_job_id and md.job_level is not NULL) as Job_level,'
120 set @sqlCommand=@sqlCommand+'m.job_family,m.job_level,'
121 set @sqlCommand=@sqlCommand+'NULL as Base50,NULL as TCC50, '
122 set @sqlCommand=@sqlCommand+'''Survey'' as CutType, NULL as CutId, CONVERT(VARCHAR(24),sj.survey_job_id) as Job_id,4 as Job_Type,sj.Survey_Job_ID,s.surveytitle_ID, '
123 --set @sqlCommand=@sqlCommand+'from surveys s, surveyjob sj, companysurveys cs '
124 --set @sqlCommand=@sqlCommand+'where cs.company_id='+Convert(varchar(10),@Company_ID) +' and cs.survey_id=s.survey_id and sj.survey_id=s.survey_id '
125 set @sqlCommand=@sqlCommand+' (Select count(1) from companyjobs_pricingsmatches cpm,surveydata sd where cpm.company_id='+Convert(varchar(10),@Company_ID) +' and sd.survey_id=s.survey_id and sd.survey_job_id=sj.survey_job_id and sd.survey_data_id=cpm.survey_data_id) as matches, '
126 set @sqlCommand=@sqlCommand+' Null as country_code'
127 set @sqlCommand=@sqlCommand+' from surveys s Inner Join surveyjob sj on sj.survey_id=s.survey_id'
128 set @sqlCommand=@sqlCommand+' Inner Join companysurveys cs on cs.company_id='+Convert(varchar(10),@Company_ID) +' and cs.survey_id=s.survey_id'
129 set @sqlCommand=@sqlCommand+' left outer join (Select max(md.job_family) as job_family,max(md.job_level) as job_level,Survey_Job_ID from MDJobs md,mdjobssurveyjobmatch mdjsm where md.job_code=mdjsm.MDJob_Code group by Survey_Job_ID ) m on m.Survey_Job_ID=sj.Survey_Job_ID'
130 set @sqlCommand=@sqlCommand+' where exists (Select 1 from surveydata sdd where sdd.Survey_Job_ID = sj.Survey_Job_ID'
131 set @sqlCommand=@sqlCommand+' and ( not exists (Select 1 from userpermissions up where up.user_id='+Convert(varchar(10),@User_ID) +' and up.DataType=''Survey Data'') or exists (Select 1 from userpermissions up where up.user_id='+Convert(varchar(10),@User_ID) +' and up.DataType=''Survey Data'' and DataValue=sdd.country_code ))'
132
133 if @Country<>'' and @Country<>'All'
134 set @sqlCommand=@sqlCommand+' and exists (Select 1 from country c where c.country_code=sdd.Country_Code and c.country_name='''+@Country+''')'
135
136 set @sqlCommand=@sqlCommand+')'
137
138
139 if @SearchText <> ''
140 set @sqlCommand=@sqlCommand+' and (sj.job_title like ''%'+replace(@SearchText,'''','''''')+'%'' or sj.job_code like ''%'+replace(@SearchText,'''','''''')+'%'')'
141 if @SearchDescription<>''
142 set @sqlCommand=@sqlCommand+' and sj.job_description like ''%'+replace(@SearchDescription,'''','''''')+'%'''
143 --if @Publishers<>''
144 -- set @sqlCommand=@sqlCommand+' and CHARINDEX(Survey_Publisher,'''+@Publishers+''')>0'
145 --if @Surveys<>''
146 -- set @sqlCommand=@sqlCommand+' and CHARINDEX(Survey_Name,'''+@Surveys+''')>0'
147
148 if @Year=''
149 set @sqlCommand=@sqlCommand+' and s.effective_Date=(Select max(sss.Effective_Date) from surveys sss,companysurveys css,surveyyears sy,surveyyears syy where s.surveyyear_id=sy.surveyyear_id and syy.Surveytitle_ID=sy.surveytitle_id and syy.SurveyYear_ID = sss.SurveyYear_ID and sss.survey_id=css.survey_id and css.company_id='+Convert(varchar(10),@Company_ID)+')'
150 if @Year<>''
151 set @sqlCommand=@sqlCommand+' and left(s.effective_Date,4)=(Select max(left(sss.Effective_Date,4)) from surveys sss,companysurveys css,surveyyears sy,surveyyears syy where s.surveyyear_id=sy.surveyyear_id and syy.Surveytitle_ID=sy.surveytitle_id and syy.SurveyYear_ID = sss.SurveyYear_ID and sss.survey_id=css.survey_id and css.company_id='+Convert(varchar(10),@Company_ID)+' and left(sss.effective_date,4) = '''+@Year +''' and left(s.effective_date,4) ='''+@Year +''')'
152
153 --set @sqlCommand=@sqlCommand+' and exists(Select 1 from surveydata sd where sd.survey_id=s.survey_id and sd.survey_job_id=sj.survey_job_id'
154
155 --if @Country<>'' and @Country<>'All'
156 --set @sqlCommand=@sqlCommand+' and exists (Select 1 from country c where c.country_code=sd.Country_Code and c.country_name='''+@Country+''')'
157
158 --set @sqlCommand=@sqlCommand+')'
159
160
161 set @sqlCommand=@sqlCommand+')'
162
163 --Print @sqlCommand
164
165 EXEC (@sqlCommand)
166
167
168End
169
170CREATE INDEX IDX1_FilterResults ON #FilterResults(Publisher,Survey,Job_Family,Job_Level)
171CREATE INDEX IDX2_FilterResults ON #FilterResults(Publisher)
172CREATE INDEX IDX3_FilterResults ON #FilterResults(Survey)
173CREATE INDEX IDX4_FilterResults ON #FilterResults(Job_Family)
174CREATE INDEX IDX5_FilterResults ON #FilterResults(Job_Level)
175
176PRINT 'Survey Jobs'+convert(varchar(50),Datepart(Ss,getdate()))
177
178--POP RESULTS
179
180insert into #Results (Publisher,Survey,Source,Job_Title,job_Code,Job_Family,Job_Level,Base50,TCC50,CutType,CutId,Job_ID,Job_Type,Survey_Job_ID,SurveyTitle_ID,matches,country_code)
181(Select Publisher,Survey,Source,Job_Title,job_Code,Job_Family,Job_Level,Base50,TCC50,CutType,CutId,Job_ID,Job_Type,Survey_Job_ID,SurveyTitle_ID,matches,country_code from #FilterResults where publisher='PayFactors'
182 and ((@Publishers='' and @Surveys='') or (CHARINDEX(','+Publisher+',',','+@Publishers+',')>0 and CHARINDEX(','+Survey+',',','+@Surveys+',')=0 and Publisher not in (Select s.Survey_Publisher from surveys s,companysurveys cs where cs.company_id=@Company_ID and s.survey_id=cs.survey_id and CHARINDEX(','+s.Survey_name+',',','+@Surveys+',')>0 )) or (CHARINDEX(','+Publisher+',',','+@Publishers+',')=0 and CHARINDEX(','+Survey+',',','+@Surveys+',')>0) or (CHARINDEX(','+Publisher+',',','+@Publishers+',')>0 and CHARINDEX(','+Survey+',',','+@Surveys+',')>0) )
183 and 0<case when @JobFamilies='' then 1 else CHARINDEX(','+Job_Family+',',','+@JobFamilies+',') end
184 and 0<case when @JobLevels='' then 1 else CHARINDEX(','+Job_Level+',',','+@JobLevels+',') end
185 --and (@JobFamilies='' or CHARINDEX(','+Job_Family+',',','+@JobFamilies+',')>0 or @JobLevels='' or CHARINDEX(','+Job_Level+',',','+@JobLevels+',')>0 )
186 --and (CHARINDEX(','+Publisher+',',','+@Publishers+',')>0 or CHARINDEX(','+Job_Family+',',','+@JobFamilies+',')>0 or CHARINDEX(','+Job_Level+',',','+@JobLevels+',')>0)
187 --and (@Surveys='' or CHARINDEX(','+Survey+',',','+@Surveys+',')>0)
188
189UNION ALL
190Select Publisher,Survey,Source,Job_Title,job_Code,Job_Family,Job_Level,Base50,TCC50,CutType,CutId,Job_ID,Job_Type,Survey_Job_ID,SurveyTitle_ID,matches,country_code from #FilterResults where publisher<>'PayFactors'
191 and ((@Publishers='' and @Surveys='') or (CHARINDEX(','+Publisher+',',','+@Publishers+',')>0 and CHARINDEX(','+Survey+',',','+@Surveys+',')=0 and Publisher not in (Select s.Survey_Publisher from surveys s,companysurveys cs where cs.company_id=@Company_ID and s.survey_id=cs.survey_id and CHARINDEX(','+s.Survey_name+',',','+@Surveys+',')>0 )) or (CHARINDEX(','+Publisher+',',','+@Publishers+',')=0 and CHARINDEX(','+Survey+',',','+@Surveys+',')>0) or (CHARINDEX(','+Publisher+',',','+@Publishers+',')>0 and CHARINDEX(','+Survey+',',','+@Surveys+',')>0) )
192 and 0<case when @JobFamilies='' then 1 else CHARINDEX(','+Job_Family+',',','+@JobFamilies+',') end
193 and 0<case when @JobLevels='' then 1 else CHARINDEX(','+Job_Level+',',','+@JobLevels+',') end
194)
195
196PRINT 'Pop Results'+convert(varchar(50),Datepart(Ss,getdate()))
197
198create table #ResultsData(Survey_job_id int,Country_Code varchar(255),combined_scope varchar(255),weightingtype varchar(50),Base50 float)
199create table #ResultsCountry(country varchar(255))
200if @Survey=1
201Begin
202
203 --RESULTSJOINEDTODATA
204
205
206
207 if @rate='Annual' and isNull(@MinRange,0)>0
208 set @MinRange=@MinRange*1000
209 if @rate='Annual' and isNull(@MaxRange,0)>0
210 set @MaxRange=@MaxRange*1000
211
212 insert into #ResultsData(Survey_job_id,Country_Code,combined_scope,weightingtype,Base50)
213 (Select surveydata.Survey_job_id, country_Name,CompanySurveyScopes.combined_scope,surveydata.weightingtype,dbo.convertrate(dbo.ConvertCurrency_New(surveydata.base50,surveydata.currency_code,@Currency,@Effective_Date),'Annual',@Rate) from #Results
214 join surveyjob on surveyjob.survey_job_id=#Results.survey_job_id
215 join surveydata on surveyjob.survey_job_id=surveydata.survey_job_id and (@ScopeSearch='' or surveydata.scope1+isnull(surveydata.scope2,'')+isnull(surveydata.scope3,'') like '%'+@ScopeSearch+'%') and (@WeightingType='' or @WeightingType=surveydata.WeightingType) and (isnull(@MinRange,'')='' or dbo.convertrate(dbo.ConvertCurrency_New(surveydata.base50,surveydata.currency_code,@Currency,@Effective_Date),'Annual',@Rate)>@MinRange) and (isnull(@MaxRange,'')='' or dbo.convertrate(dbo.ConvertCurrency_New(surveydata.base50,surveydata.currency_code,@Currency,@Effective_Date),'Annual',@Rate)<@MaxRange) and ( not exists (Select 1 from userpermissions up where up.user_id=@User_ID and up.DataType='Survey Data') or exists (Select 1 from userpermissions up where up.user_id=@User_ID and up.DataType='Survey Data' and DataValue=surveydata.country_code ))
216 join country on country.country_code=surveydata.country_code and (@Country='' or @Country='All' or @Country=country.Country_Name)
217 left outer join CompanySurveyScopes on CompanySurveyScopes.Company_ID=@Company_ID and CompanySurveyScopes.CompanyPayMarket_ID=@PayMarket_ID and CompanySurveyScopes.WeightingType = surveydata.WeightingType and CompanySurveyScopes.Scope1=surveydata.scope1 and isnull(CompanySurveyScopes.Scope2,'') = isnull(surveydata.Scope2,'') and isnull(CompanySurveyScopes.Scope3,'') = isnull(surveydata.Scope3,'')
218 where (@DefaultScope='' or @DefaultScope=CompanySurveyScopes.combined_scope or (@DefaultScope='All Default Scopes' and CompanySurveyScopes.combined_scope is not NULL))
219 )
220
221 CREATE INDEX IDX1_ResultsData ON #ResultsData (Survey_Job_ID)
222 CREATE INDEX IDX2_ResultsData ON #ResultsData (Country_Code)
223 CREATE INDEX IDX3_ResultsData ON #ResultsData (combined_scope)
224 CREATE INDEX IDX4_ResultsData ON #ResultsData (weightingtype)
225 CREATE INDEX IDX5_ResultsData ON #ResultsData (Base50)
226
227
228 insert into #ResultsCountry (country) (Select distinct country_code from #ResultsData)
229
230 if @Country=''
231 Begin
232 Select @DefaultCountry=max(Country_Name) from country c, companypaymarkets cp, #ResultsCountry f
233 where c.Country_Code = cp.Country_Code
234 and f.country=c.country_name
235 and cp.CompanyPayMarket_ID = @PayMarket_ID
236
237 if @DefaultCountry<>'' and @DefaultCountry is not NULL
238 Begin
239 Delete from #ResultsData where country_code<>@DefaultCountry
240 Delete from #Results where survey_job_id is null and Country_Code <> (Select country_code from country where country_name=@DefaultCountry)
241 Delete from #FilterResults where survey_job_id is null and Country_Code <> (Select country_code from country where country_name=@DefaultCountry)
242
243 End
244 End
245
246 if @Rate='Annual'
247 Update #ResultsData set Base50=Base50/1000
248
249
250 --Remove any jobs where there is no data available
251 delete from #Results where survey_job_id is not NULL and not exists (Select 1 from #ResultsData where #ResultsData.survey_job_id=#Results.survey_job_id)
252
253 PRINT 'Results Data'+convert(varchar(50),Datepart(Ss,getdate()))
254
255end
256
257----------------------------------------------------------------------------------------------------
258--Filters
259----------------------------------------------------------------------------------------------------
260--DefaultCountry in use
261set @Filters=@Filters+'{}DefaultCountry:[]||' +isnull(@DefaultCountry,'')
262
263--Return Selected Publishers
264if @Surveys<>''
265Begin
266 DECLARE data_cursor CURSOR FOR
267 Select STUFF((Select distinct ',' + Publisher from #Results where Publisher is not NULL FOR XML PATH('')), 1, 1, '') as List
268
269 OPEN data_cursor;
270 FETCH NEXT FROM data_cursor
271 INTO @ReturnText;
272
273 set @Filters=@Filters+'{}NewPublishers:[]'
274 WHILE @@FETCH_STATUS = 0
275 BEGIN
276 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
277 FETCH NEXT FROM data_cursor
278 INTO @ReturnText;
279 END
280
281 CLOSE data_cursor;
282 DEALLOCATE data_cursor;
283End
284
285--Job_Types
286DECLARE data_cursor CURSOR FOR
287Select case when 1=(Select max(1) from #Results where survey_job_id is NULL) then 1 else 0 end
288UNION ALL
289Select case when 1=(Select max(1) from #Results where survey_job_id is not NULL) then 1 else 0 end
290
291OPEN data_cursor;
292FETCH NEXT FROM data_cursor
293INTO @ReturnText;
294
295set @Filters=@Filters+'{}JobTypes:[]'
296WHILE @@FETCH_STATUS = 0
297BEGIN
298 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
299 FETCH NEXT FROM data_cursor
300 INTO @ReturnText;
301END
302
303CLOSE data_cursor;
304DEALLOCATE data_cursor;
305
306
307--Job_Family
308DECLARE data_cursor CURSOR FOR
309Select distinct job_family from #FilterResults where job_family is not NULL
310UNION
311Select distinct job_family from mdjobsFamilies where job_family is not null and not exists (Select 1 from #Results) and @SearchText=''
312order by job_family asc
313
314OPEN data_cursor;
315FETCH NEXT FROM data_cursor
316INTO @ReturnText;
317
318set @Filters=@Filters+'{}JobFamily:[]'
319WHILE @@FETCH_STATUS = 0
320BEGIN
321 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
322 FETCH NEXT FROM data_cursor
323 INTO @ReturnText;
324END
325
326CLOSE data_cursor;
327DEALLOCATE data_cursor;
328
329--Print 'Job Family'+convert(varchar(50),Datepart(Ss,getdate()))
330
331--Job_Level
332DECLARE data_cursor CURSOR FOR
333Select distinct job_level from #FilterResults where job_level is not NULL
334UNION
335Select distinct job_level from mdjobs where job_level is not null and not exists (Select 1 from #Results) and @SearchText=''
336order by job_level asc
337
338OPEN data_cursor;
339FETCH NEXT FROM data_cursor
340INTO @ReturnText;
341
342set @Filters=@Filters+'{}JobLevel:[]'
343WHILE @@FETCH_STATUS = 0
344BEGIN
345 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
346 FETCH NEXT FROM data_cursor
347 INTO @ReturnText;
348END
349
350CLOSE data_cursor;
351DEALLOCATE data_cursor;
352
353--Print 'Job Level'+convert(varchar(50),Datepart(Ss,getdate()))
354
355--Publisher
356DECLARE data_cursor CURSOR FOR
357Select distinct Publisher from #FilterResults where Publisher is not NULL
358Union
359Select distinct s.Survey_Publisher from surveys s,companysurveys cs where s.survey_id=cs.survey_id and cs.company_id=@Company_ID and 1=Case when @ShowAll='true' then 1 else 0 end
360order by Publisher asc
361
362OPEN data_cursor;
363FETCH NEXT FROM data_cursor
364INTO @ReturnText;
365
366set @Filters=@Filters+'{}Publisher:[]'
367WHILE @@FETCH_STATUS = 0
368BEGIN
369 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
370 FETCH NEXT FROM data_cursor
371 INTO @ReturnText;
372END
373
374CLOSE data_cursor;
375DEALLOCATE data_cursor;
376
377--Print 'Pubs'+convert(varchar(50),Datepart(Ss,getdate()))
378
379--Surveys
380DECLARE data_cursor CURSOR FOR
381Select distinct survey from #FilterResults where survey is not NULL-- and ((@Publishers='' and @Surveys='') or Publisher in (Select r.publisher from #Results r))
382and (@Publishers='' or CHARINDEX(Publisher,@Publishers)>0)
383Union
384Select distinct s.Survey_Name from surveys s,companysurveys cs where s.survey_id=cs.survey_id and cs.company_id=@Company_ID and 1=Case when @ShowAll='true' then 1 else 0 end
385order by survey asc
386
387OPEN data_cursor;
388FETCH NEXT FROM data_cursor
389INTO @ReturnText;
390
391set @Filters=@Filters+'{}Survey:[]'
392WHILE @@FETCH_STATUS = 0
393BEGIN
394 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
395 FETCH NEXT FROM data_cursor
396 INTO @ReturnText;
397END
398
399CLOSE data_cursor;
400DEALLOCATE data_cursor;
401
402--Range
403DECLARE data_cursor CURSOR FOR
404Select Range from (
405Select min(base50) as Range from (Select Base50 from #Results UNION ALL Select Base50 from #ResultsData) s where base50>0
406UNION ALL
407Select max(base50) as Range from (Select Base50 from #Results UNION ALL Select Base50 from #ResultsData) s where base50>0
408) ss where range is not NULL
409
410OPEN data_cursor;
411FETCH NEXT FROM data_cursor
412INTO @ReturnText;
413
414set @Filters=@Filters+'{}Range:[]'
415WHILE @@FETCH_STATUS = 0
416BEGIN
417 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
418 FETCH NEXT FROM data_cursor
419 INTO @ReturnText;
420END
421
422CLOSE data_cursor;
423DEALLOCATE data_cursor;
424
425--Years
426DECLARE data_cursor CURSOR FOR
427Select distinct left(s.effective_Date,4) from surveys s,surveyjob sj, #Results r where s.survey_id=sj.survey_id and sj.survey_job_id=r.survey_job_id
428UNION
429--Select distinct left(s.effective_Date,4) from companysurveys cs,surveys s,surveyjob sj, #Results r where @Year='' and cs.company_id=@Company_ID and cs.survey_id=s.survey_id and s.SurveyTitle_ID=r.surveyTitle_id and s.survey_id=sj.survey_id and sj.job_code=r.job_code
430--order by left(effective_Date,4) desc
431Select distinct left(s.effective_Date,4) from companysurveys cs,surveys s,surveyjob sj where cs.company_id=@Company_ID and cs.survey_id=s.survey_id and s.survey_id=sj.survey_id and exists (Select 1 from #Results r where s.SurveyTitle_ID=r.surveyTitle_id) and (@SearchText='' or (sj.job_title like '%'+@SearchText+'%' or sj.job_code like '%'+@SearchText+'%')) and (@SearchDescription='' or sj.job_description like '%'+@SearchDescription+'%')
432order by left(effective_Date,4) desc
433
434OPEN data_cursor;
435FETCH NEXT FROM data_cursor
436INTO @ReturnText;
437
438set @Filters=@Filters+'{}Year:[]'
439WHILE @@FETCH_STATUS = 0
440BEGIN
441 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
442 FETCH NEXT FROM data_cursor
443 INTO @ReturnText;
444END
445
446CLOSE data_cursor;
447DEALLOCATE data_cursor;
448
449--RESULTSJOINEDTODATA
450
451
452--Country
453DECLARE data_cursor CURSOR FOR
454Select distinct country from #ResultsCountry order by country asc
455
456OPEN data_cursor;
457FETCH NEXT FROM data_cursor
458INTO @ReturnText;
459
460set @Filters=@Filters+'{}Country:[]'
461WHILE @@FETCH_STATUS = 0
462BEGIN
463 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
464 FETCH NEXT FROM data_cursor
465 INTO @ReturnText;
466END
467
468CLOSE data_cursor;
469DEALLOCATE data_cursor;
470
471
472--DefaultScopes
473DECLARE data_cursor CURSOR FOR
474Select distinct combined_scope from #ResultsData where combined_scope is not NULL order by combined_scope asc
475
476OPEN data_cursor;
477FETCH NEXT FROM data_cursor
478INTO @ReturnText;
479
480--set @Filters=@Filters+''
481
482if @@FETCH_STATUS = 0 AND isnull(@DefaultScope,'') <> ''
483 set @Filters=@Filters+'{}DefaultScope:[]||All Default Scopes'
484
485WHILE @@FETCH_STATUS = 0
486BEGIN
487 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
488 FETCH NEXT FROM data_cursor
489 INTO @ReturnText;
490END
491
492CLOSE data_cursor;
493DEALLOCATE data_cursor;
494
495--WeightingType
496DECLARE data_cursor CURSOR FOR
497Select distinct WeightingType from #ResultsData where WeightingType is not NULL order by WeightingType asc
498
499OPEN data_cursor;
500FETCH NEXT FROM data_cursor
501INTO @ReturnText;
502
503set @Filters=@Filters+'{}WeightingType:[]'
504WHILE @@FETCH_STATUS = 0
505BEGIN
506 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
507 FETCH NEXT FROM data_cursor
508 INTO @ReturnText;
509END
510
511CLOSE data_cursor;
512DEALLOCATE data_cursor;
513
514--Count of results
515--WeightingType
516DECLARE data_cursor CURSOR FOR
517Select count(1) from #Results
518
519OPEN data_cursor;
520FETCH NEXT FROM data_cursor
521INTO @ReturnText;
522
523set @Filters=@Filters+'{}ResultsCount:[]'
524WHILE @@FETCH_STATUS = 0
525BEGIN
526 set @Filters=@Filters+'||'+isnull(@ReturnText,'')
527 FETCH NEXT FROM data_cursor
528 INTO @ReturnText;
529END
530
531CLOSE data_cursor;
532DEALLOCATE data_cursor;
533
534
535----------------------------------------------------------------------------------------------------
536--Results
537----------------------------------------------------------------------------------------------------
538DECLARE data_cursor CURSOR FOR
539Select top 300 Publisher,Source,Job_Title,job_Code,Job_Family,Job_Level,Base50,TCC50,CutType,CutId,Job_ID,Job_Type,Survey_Job_ID,matches,country_code from #Results order by Source,Job_Title
540
541OPEN data_cursor;
542FETCH NEXT FROM data_cursor
543INTO @Publisher,@Source,@Job_Title,@job_Code,@Job_Family,@Job_Level,@Base50,@TCC50,@CutType,@CutId,@Job_ID,@Job_Type,@Survey_Job_ID,@matches,@country_code;
544
545WHILE @@FETCH_STATUS = 0
546BEGIN
547
548 set @Results=@Results+'{}'+isnull(@Publisher,'')+'||'+isnull(@Source,'')+'||'+isnull(@Job_Title,'')+'||'+isnull(@job_Code,'')+'||'+isnull(@Job_Family,'')+'||'+isnull(@Job_Level,'')+'||'+isnull(convert(varchar(50),@Base50),'')+'||'+isnull(convert(varchar(50),@TCC50),'')+'||'+isnull(@CutType,'')+'||'+isnull(@CutId,'')+'||'+isnull(@Job_ID,'')+'||'+isnull(convert(varchar(50),@Job_Type),'')+'||'+isnull(convert(varchar(50),@Survey_Job_ID),'')+'||'+isnull(convert(varchar(50),@matches),'')+'||'+isnull(convert(varchar(50),@country_code),'')
549
550 FETCH NEXT FROM data_cursor
551 INTO @Publisher,@Source,@Job_Title,@job_Code,@Job_Family,@Job_Level,@Base50,@TCC50,@CutType,@CutId,@Job_ID,@Job_Type,@Survey_Job_ID,@matches,@country_code;
552END
553
554CLOSE data_cursor;
555DEALLOCATE data_cursor;
556
557Print 'DOne'+convert(varchar(50),Datepart(Ss,getdate()))
558---------------------------------------------------------------------------------------------------
559--Final return
560Select @Filters as Filters,@Results as Results
561
562
563--Select distinct dbo.ToJSON(Job_Level) from #Results
564
565--Select * from #Results order by Job_Title, Source