March 23, 2016 at 8:47 am
Hi All,
Hoping you can help as my search as been abit fruitless so far!
So I have an sp which pulls titles based on a wildcard search of key words/titles.
I need to just pull out the top 10 for each of the search terms that the user has input. I have tried several things with rank and row_number in the query but these didn't work.
So the sp is this:
USE [DB]
GO
/****** Object: StoredProcedure [dbo].[rpt_TOP_10_JOB_TITLES_POSTED_SALES_PERF] Script Date: 23/03/2016 14:31:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[rpt_TOP_10_JOB_TITLES_POSTED_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_TOP_10_JOB_TITLES_POSTED_SALES_PERF] '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'
SET @END_DATE = @END_YEAR + right ('0' +@END_MONTH, 2) + '01'
SET @END_DATE = Dateadd(m,1,@END_DATE)*/
set @START_DATE = try_parse(@START_YEAR + '-' + @START_MONTH + '- 01 00:00:00' as datetime);
set @END_DATE = try_parse(@END_YEAR + '-' + @END_MONTH + '- 01 00:00:00' as datetime);
set @END_DATE = DateAdd(m,1,@END_DATE)
;
select count(distinct v.REF) as [No of Refs],
v.TITLE as [Title],
s.Item
from Ref V
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.TITLE like '%' + s.Item + '%'
------------------
where v.CREATE_DATE >= cast(@START_DATE as datetime)
and v.CREATE_DATE < cast(@END_DATE as datetime)
group by s.Item, v.TITLE
order by s.item, [No of Refs] desc
option(recompile)
end
The results look like this (just adding a small cross section):
No of Refs Title Item
16Marketing Officer Office
16Employment Engagement OfficerOffice
15Customer Service Officer Office
73Commercial Property SolicitorProperty
39Property Manager Property
36Residential Property SolicitorProperty
What I want is to get the top 10 of each of the item(s) that are entered in by the user to appear in the table.
I have tried matching the parameter to the items but to no availa so any help is appreciated.
March 23, 2016 at 9:03 am
Without CREATE TABLE statements for the tables and INSERT statements for sample data, and with no posted exact required output, you are leaving a lot to the imagination.
However, if I understand the requirement correctly, I think you'll need something like:
SELECT Name, the, columns
FROM dbo.DelimitedSplit8k(@TITLES, ',') AS s
CROSS APPLY
(SELECT TOP(10) More, columns, to, name
FROM dbo.Ref AS v
WHERE v.Title LIKE '%' + s.Item + '%'
ORDER BY Whatever);
March 23, 2016 at 10:00 am
Hugo, my apologies as it was an SSRS question I didn't post more but you are right best to put everything on here.
Actually what you gave me worked perfectly, so really appreciated. I have used it in Dev so far, so will look at prod next.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply