SQL Dynamic partition in count function

Question

I have a stored procedure that is being used with reporting services and the report has a few user filters that are passing values into the stored procedure. Because of these parameters the count for what is displayed changes so I want to use them in the partition to change the count. I tried the following which does not work.

EDIT:

The count is done on ss_number but not on @SearchBy. So if I have

SS#       |    Name    |    City  |  Amount

123456789 | Mike Smith | Trenton | 100.00

123456789 | Mike Smith | Trenton | 200.00

123456789 | Mike Smith | Jackson | 100.00

My count is 3 even though @SearchBy = City and I am filtering on Trenton.

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

Right now I am using a case statement but it has slowed my query down considerably.

Here is the code without the case statement attempting to use the variable in my partition.

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

your query is slowing down because, as you can imagine, the "case" statement needs to be evaluated for every row, being in a Select statement.
This means that if your query is loading many rows you put a lot of overhead.

I see two different approaches to solve your issue. I don't love any of them but they works :)

FIRST SOLUTION
use a IF ... ELSE to cover all you possible inputs. This means that you'll have to write many times the same query just to differentiate it in one line of code.
This means code becomes rendundant and basically not maintaniable

SECOND SOLUTION
Build a dynamic query.
You can write something like this

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

Answer 2

Try use "Case" inside Over statement:

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 3

Judging by what you want, you are first going to need Dynamic SQL. With that being said, you can use row_number(). I still don't understand your extra partition by, but you can just add it to the example below. You can run this, and it will print out the command that is going to be executed.

--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

Here is a working example: http://rextester.com/BPWOXH7777