March 16, 2016 at 4:39 am
I have put this here as although it is an SSRS report I am building I am starting with the query of course :).
So, I have got a query that is like this currently:
select count(distinct v.ref) as [No of Refs], count(distinct app.id) as [No of Apps],
case when v.SERVICE_CATEGORY like 'L%' then 'Standard Ref' when v.SERVICE_CATEGORY like 'S%' then 'Branded Ref' else 'Bespoke' end as [Ref Type],
case when v.title like '%.Net Developer%' then '.Net Developer'
when v.title like '%1st Line Support Engineer%' then '1st Line Support Engineer'
when v.title like '%2nd Line Support Engineer%' then '2nd Line Support Engineer'
when v.title like '%3rd Line Support Engineer%' then '3rd Line Support Engineer'
when v.title like '%Account Manager%' then 'Account Manager'
end as [Job Types]
from ref v
inner join app app on app.REF = v.REF
where (v.title like '%.Net Developer%'
or v.title like '%.Net Developer%'
or v.title like '%1st Line Support Engineer%'
or v.title like '%2nd Line Support Engineer%'
or v.title like '%3rd Line Support Engineer%'
or v.title like '%Account Manager%')
and v.CREATE_DATE >='20160201'
and v.CREATE_DATE < '20160301'
and v.country = 'UK'
group by
case when v.title like '%.Net Developer%' then '.Net Developer'
when v.title like '%1st Line Support Engineer%' then '1st Line Support Engineer'
when v.title like '%2nd Line Support Engineer%' then '2nd Line Support Engineer'
when v.title like '%3rd Line Support Engineer%' then '3rd Line Support Engineer'
when v.title like '%Account Manager%' then 'Account Manager'
end,
case when v.SERVICE_CATEGORY like 'L%' then 'Standard Ref' when v.SERVICE_CATEGORY like 'S%' then 'Branded Ref' else 'Bespoke Ref' end
order by [Job Types]
So as you can see the client has given us some searches for some titles and we fuzzy search/wildcard them as they need any title that has this wording in it.
So I want to translate this to an sp where the wildcards are a parameter.
I have done a query before where I have added a like '%' + @Word + '%' but how would I do multiple selections?
I want to limit them to 10 items only at one time, someone sent in over 300 the other day!
Thanks for any help on this.
March 16, 2016 at 7:27 am
For multiple selections, you could use the DelimitedSplit8K to split the items. You can find it in here, along with the explanation to use it: http://www.sqlservercentral.com/articles/Tally+Table/72993/
Here's an untested example on how it would look in your query. The variable declaration and assignment is meant to represent a parameter.
DECLARE @Titles varchar(8000);
SET @Titles = '.Net Developer,1st Line Support Engineer,2nd Line Support Engineer,3rd Line Support Engineer,Account Manager';
select count(distinct v.ref) as [No of Refs],
count(distinct app.id) as [No of Apps],
case when v.SERVICE_CATEGORY like 'L%' then 'Standard Ref'
when v.SERVICE_CATEGORY like 'S%' then 'Branded Ref'
else 'Bespoke' end as [Ref Type],
s.Item as [Job Types]
from ref v
inner join app app on app.REF = v.REF
-- Here's the magic
inner join dbo.DelimitedSplit8k(@Titles, ',') s ON v.title like '%' + s.Item + '%'
------------------
where v.CREATE_DATE >='20160201'
and v.CREATE_DATE < '20160301'
and v.country = 'UK'
group by s.Item,
case when v.SERVICE_CATEGORY like 'L%' then 'Standard Ref'
when v.SERVICE_CATEGORY like 'S%' then 'Branded Ref'
else 'Bespoke Ref' end
order by [Job Types];
March 16, 2016 at 8:43 am
Hi Luis,
Thank you am going to have a good read of that article, put it in Dev first (because I am not crazy) and test it fully.
Really appreciated, learn something new every day
March 16, 2016 at 9:14 am
That's amazing just read the article, tested it in Dev WOAH! Thank you SO much saved me a lot of rubbish coding with CASE statements.
March 16, 2016 at 10:12 am
You're welcome. Thank you for your feedback.
March 16, 2016 at 9:39 pm
Kazmerelda (3/16/2016)
Hi Luis,Thank you am going to have a good read of that article, put it in Dev first (because I am not crazy) and test it fully.
Really appreciated, learn something new every day
Now THAT's a pleasant change from the norm. Very smart the way you're doing that.
And thanks for the feedback on the article.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 16, 2016 at 9:41 pm
Luis Cazares (3/16/2016)
For multiple selections, you could use the DelimitedSplit8K to split the items. You can find it in here, along with the explanation to use it: http://www.sqlservercentral.com/articles/Tally+Table/72993/Here's an untested example on how it would look in your query. The variable declaration and assignment is meant to represent a parameter.
DECLARE @Titles varchar(8000);
SET @Titles = '.Net Developer,1st Line Support Engineer,2nd Line Support Engineer,3rd Line Support Engineer,Account Manager';
select count(distinct v.ref) as [No of Refs],
count(distinct app.id) as [No of Apps],
case when v.SERVICE_CATEGORY like 'L%' then 'Standard Ref'
when v.SERVICE_CATEGORY like 'S%' then 'Branded Ref'
else 'Bespoke' end as [Ref Type],
s.Item as [Job Types]
from ref v
inner join app app on app.REF = v.REF
-- Here's the magic
inner join dbo.DelimitedSplit8k(@Titles, ',') s ON v.title like '%' + s.Item + '%'
------------------
where v.CREATE_DATE >='20160201'
and v.CREATE_DATE < '20160301'
and v.country = 'UK'
group by s.Item,
case when v.SERVICE_CATEGORY like 'L%' then 'Standard Ref'
when v.SERVICE_CATEGORY like 'S%' then 'Branded Ref'
else 'Bespoke Ref' end
order by [Job Types];
Awesome example, Luis.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 21, 2016 at 3:21 am
Just coming back to this, I am now trying to use this in SSRS and it keeps throwing up an
conversion failed when converting date and/or time from character string
error.
I am assuming that it is something o do with the function rather than the query that I have, and that I might well need to add something to indicate datetime as I have assumed that since it's not in there it needs to be told to be the same as the sp? Sorry to ask again, I have tried a couple of things but as I am fairly new to functions I really appreciate any pointers.
Works perfectly as an sp, fails in SSRS.
My brain, forgive me today must have checked out for Easter already!
SP is:
CREATE Procedure [dbo].[rpt_APV_BY_JOB_TITLE_SALES_PERF]
(
@CLUSTER_CODE as nvarchar(3),
@START_MONTH as nvarchar(2),
@START_YEAR as nvarchar(4),
@END_MONTH as nvarchar(2),
@END_YEAR as nvarchar(4),
@TITLES as varchar(8000)
)
as
begin
-- exec [dbo].[rpt_APV_BY_JOB_TITLE] 'SUK','1','2013','12','2013
set nocount on
declare @START_DATE AS datetime
declare @END_DATE AS datetime
/*SET @START_DATE = @START_YEAR + right('0' + @START_MONTH, 2) + '01 00:00:00'
SET @END_DATE = @END_YEAR + right ('0' +@END_MONTH, 2) + '01 00:00:00'
SET @END_DATE = Dateadd(m,1,@END_DATE)*/
set @START_DATE = @START_YEAR + '-' + @START_MONTH + '- 01 00:00:00'
set @END_DATE = @END_YEAR + '-' + @END_MONTH + '- 01 00:00:00'
set @END_DATE = DateAdd(m,1,@END_DATE)
;
select count(distinct V.REF) as [Number Of Refs],
count(distinct APP.APP_ID) as [Number of Apps],
case when v.SERVICE_SUB_CATEGORY like 'L%' then 'Standard'
when v.SERVICE_SUB_CATEGORY like 'S%' then 'Branded'
else 'Bespoke' end as [Ref Type],
s.Item as [Job Title],
coalesce(count(distinct app.APP_ID),0)/cast(count(distinct v.REF)as float) as [APV]
from REF V
inner join APP APP with (NOLOCK) on APP.REF = V.REF
inner join SITE_CLUSTER_X scx with (NOLOCK) on V.AGENCY_CC = scx.SITE_CODE and scx.CLUSTER_CODE = @cluster_code
-- Here's the magic
inner join dbo.DelimitedSplit8k(@TITLES, ',') s ON V.JOB_TITLE like '%' + s.Item + '%'
------------------
where v.CREATE_DATE >= @START_DATE
and v.CREATE_DATE < @END_DATE
group by s.Item,
case when v.SERVICE_SUB_CATEGORY like 'L%' then 'Standard'
when v.SERVICE_sub_CATEGORY like 'S%' then 'Branded'
else 'Bespoke' end
order by [Job Title]
option(recompile)
end
March 21, 2016 at 8:18 am
The error indicates problems when creating your dates. Try this formulas instead.
set @START_DATE = DATEADD(MM, @START_MONTH - 1, @START_YEAR);
set @END_DATE = DATEADD(MM, CAST(@END_MONTH AS int), @END_YEAR);
Review the parameters you're sending to be sure that you're sending the correct values.
March 23, 2016 at 8:22 am
Making sure I reply back to all threads I get advice on, it was date related but thankfully solved :). Thanks for your help it has been so useful. Feel like I am improving hugely at the moment.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply