Динамический раздел SQL в функции подсчета

Question

У меня есть хранимая процедура, которая используется со службами отчетов, и в отчете есть несколько пользовательских фильтров, которые передают значения в хранимую процедуру. Из-за этих параметров изменяется счетчик отображаемых данных, поэтому я хочу использовать их в разделе для изменения счетчика. Я попробовал следующее, которое не работает.

РЕДАКТИРОВАТЬ:

Подсчет производится по ss_number, но не по @SearchBy. Так что если у меня есть

SS#       |    Name    |    City  |  Amount

123456789 | Mike Smith | Trenton | 100.00

123456789 | Mike Smith | Trenton | 200.00

123456789 | Mike Smith | Jackson | 100.00

Мой счет равен 3, хотя @SearchBy = City и я фильтрую Трентона.

CASE WHEN @SearchBy = 'Product Name' THEN count(ss_number) OVER (PARTITION BY ss_number, @SearchBy) 

Сейчас я использую оператор case, но он значительно замедлил мой запрос.

Вот код без оператора case, пытающегося использовать переменную в моем разделе.

CREATE PROCEDURE [dbo].[sp_My_sp]
@SearchBy VARCHAR(MAX), 
@SearchString VARCHAR(MAX),
@SearchNum Int,
@ClaimDate Datetime2

AS
WITH MyCTE AS
(   
SELECT val.Claim_date
, val.Claim_Status
, val.Status_Desc
, ES_Claim_Status
, val.ss_number
, val.name_field1
, val.street_add1
, val.street_add2
, val.city
, val.state
, val.zip_code_pre
, val.reference_number
, val.Game_Name 
, val.val_agent
, val.home_number
, val.work_phone
, val.county_desc
, o.agent_num
, count(ss_number) OVER (PARTITION BY ss_number, @SearchBy) as count
, prize_amount
FROM Sec_Claims val left outer join vw_owners_concat_agent_num o
ON val.SS_NUMBER = convert(varchar(15),o.SS_NO)
where convert(numeric,prize_amount) >= 600)
SELECT Claim_date
, CASE WHEN agent_num IS NULL THEN 'NO' ELSE 'YES' END as "IsRetailer"
, Claim_Status
, Status_Desc
, ES_Claim_Status
, ss_number
, name_field1
, street_add1
, street_add2
, city
, state
, zip_code_pre
, reference_number
, Game_Name 
, val_agent
, home_number
, work_phone
, county_desc
, agent_num
, count
, ROW_NUMBER() OVER(PARTITION BY Name_Field1 ORDER BY Name_Field1) As RowNumber
, convert(decimal(10,2),prize_amount) as prize_amount
, sum(Convert(decimal(9,2),prize_amount)) OVER (PARTITION BY ss_number, Name_Field1) AS prizesum
FROM MyCTE 
WHERE 
    (CASE
        WHEN @SearchBy = 'Agent Number' THEN agent_num
        WHEN @SearchBy = 'SS#' THEN SS_NUMBER
        WHEN @SearchBy = 'Name' THEN Name_Field1
        WHEN @SearchBy = 'Address' THEN STREET_ADD1
        WHEN @SearchBy = 'City' THEN City
        WHEN @SearchBy = 'Claim#' THEN convert(varchar(max),REFERENCE_NUMBER)
        WHEN @SearchBy = 'Validating Retailer' THEN convert(varchar(max),VAL_AGENT)
        WHEN @SearchBy = 'County' THEN COUNTY_DESC
        WHEN @SearchBy = 'Home Phone' THEN convert(varchar(max),HOME_NUMBER)
        WHEN @SearchBy = 'Work Phone' THEN convert(varchar(max),WORK_PHONE)
        WHEN @SearchBy = 'Game Name' THEN GAME_NAME
    END 
    like (@SearchString))
    and
    count>= @SearchNum 
    and
    claim_date > @ClaimDate 
ORDER BY ss_number

Answer 1

Попробуйте использовать "Case" внутри оператора Over:

Declare @SearchBy varchar(255)
SET @SearchBy = 'Product Name'

Select count(ss_number)
            OVER (PARTITION BY ss_number,
                               Case @SearchBy
                                    When 'Product Name' then [Name]
                                    When 'City Name'    then [City]
                                    When 'Product Name' then cast([Amount] as varchar(255))
                               END) t     
    from (VALUES (123456789,'Mike Smith','Trenton',100.00)
                ,(123456789,'Mike Smith','Trenton',200.00)
                ,(123456789,'Mike Smith','Jackson',100.00)
         ) as t([ss_number],Name,City,Amount)

Answer 2

Судя по тому, что вы хотите, вам сначала понадобится динамический SQL. С этим, как говорится, вы можете использовать row_number(), Я до сих пор не понимаю ваш дополнительный раздел, но вы можете просто добавить его в пример ниже. Вы можете запустить это, и он распечатает команду, которая будет выполнена.

--create procedure yourProc(
--                          @SearchBY varchar(64)           --Column to search / filter by
--                          ,@SearchString varchar(256)     --Values to limit column above by
--                          ,@Count int                     --limit the count per each SSN
--                          ,@Date datetime)                
--as

--begin

declare     @SearchBY varchar(64)       = 'City'            --Column to search / filter by
declare     @SearchString varchar(256)  = 'Trenton'         --Values to limit column above by
declare     @Count int                  = 3                 --limit the count per each SSN
declare     @Date datetime              = '12/25/2017'

declare @SQL nvarchar(max) = 

'with cte as(
    select
        ss_number
        ,Name
        ,City
        ,Amount
        ,row_number() over (partition by ss_number order by (select 1)) as RN
    from
        YourTable
    where
        SomeDateColumn >= ''' + convert(varchar(10),@Date,112) +
        ''' and ' + @SearchBY + ' = ''' + @SearchString +
        ''')
 select *
 from cte
 where ss_number in 
 (select distinct ss_number from cte where RN >= ' + cast(@Count as varchar(16)) + ')
 '

print(@SQL)
--exec(@SQL)
--end

Вот рабочий пример: http://rextester.com/BPWOXH7777

Answer 3

Ваш запрос замедляется, потому что, как вы можете себе представить, оператор case должен оцениваться для каждой строки, находящейся в операторе Select.
Это означает, что если ваш запрос загружает много строк, вы кладете много накладных расходов.

Я вижу два разных подхода к решению вашей проблемы. Я не люблю ни одного из них, но они работают:)

ПЕРВЫЕ РЕШЕНИЯ
используйте IF ... ELSE, чтобы охватить все возможные входные данные. Это означает, что вам придется много раз писать один и тот же запрос, чтобы выделить его в одной строке кода.
Это означает, что код становится избыточным и в основном не обслуживаемым

ВТОРОЕ РЕШЕНИЕ
Создайте динамический запрос.
Вы можете написать что-то вроде этого

DECLARE @myQuery nvarchar(max)
SET @myQuery = 'Select '
if @SearchBy = 'Product Name'
  SET @myQuery= @myQuery + 'count(ss_number) OVER (PARTITION BY ss_number, @SearchBy)'
ELSE
  .... --put here your second case
END

SET @myQuery= @myQuery + 'FROM ... WHERE ...'
EXECUTE spexecute_sql @myQuery