Skip to content Skip to sidebar Skip to footer

Select Top N Dynamically With N From The Same Select Statement

I have a procedure that tries to insert samples in a table for each Agent, the # of samples for each agent varies based on some calculation Declare @samplesize int Declare @Top int

Solution 1:

You can use row_number() to do essentially the same thing:

WITH DataToInsert AS
(
    Select AgentID, Surveys, LOB,(casewhenday(getdate())<4then3else (day(getdate())) - (Surveys*3) end) SampleSize from Current_Agent_SurveyCount_HSI Where surveys<8 
)
InsertInto Survey_Source_New (LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp])  
    select LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp]
    from (Select ss.LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],
                 row_number() over (orderby newid()) as seqnum
          From Survey_source_Level1 ss innerjoin
               DataToInsert du on ss.AgentZID=du.agentID
          where flag isnulland du.samplesize<7
         ) t
    where seqnum <= du.sample_size

You might be able to simplify this a bit, but I don't know if flag is coming from du or ss.

Solution 2:

I have an added scenario over the same question. I made some changes to the existing solution, the scenario is : I get a list of Agents everyday and have to send each Agent a set of random Surveys, the count depends on some calculation for each agent.

--Get Agent List
;WITH AgentsList AS    
(    
    Select AgentID, Surveys, LOB,(casewhenday(getdate())<4then3else (day(getdate())) - (Surveys*3) end) SampleSize     
    from Current_Agent_SurveyCount_FIOS Where surveys<8     
)
--Get All the Surveys for each Agent
, AgentSurveys AS
(
    select ss.LOB, ss.CenterName, ss.CallDate, ss.AgentZid, ss.TN, ss.Ticket, ss.RecordingID, ss.Cycle, ss.[TimeStamp],ss.Flag,AL.samplesize 
    from Survey_Source_Level1_Sri ss
    innerjoin AgentsList AL on ss.AgentZID=AL.agentID
    where flag isnullGROUPBY ss.LOB, ss.CenterName, ss.CallDate, ss.AgentZid, ss.TN, ss.Ticket, ss.RecordingID, ss.Cycle, ss.[TimeStamp],ss.Flag,AL.samplesize
)
--Mark random ranking for each surveySelect LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],samplesize,    
             rank() over (partitionby agentzid orderby newid()) as seqnum    
      From AgentSurveys
      groupby LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],samplesize      
      orderby agentzid,seqnum,tn

Now from the last query i get a table like

enter image description here

I require the N number of rows from each group where N comes from SampleSize column.

Post a Comment for "Select Top N Dynamically With N From The Same Select Statement"