CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2005
Srijan
Srijan

Reputation: 23

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
set @samplesize=0

;WITH DataToInsert AS
(
    Select AgentID, Surveys, LOB,(case when day(getdate())<4 then 3 else (day(getdate())) - (Surveys*3) end) SampleSize from Current_Agent_SurveyCount_HSI Where surveys<8 
)

--Insert Into Survey_Source_New (LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp])  
--Select top 5 ss.LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp]  
--From Survey_source_Level1 ss
--inner join DataToInsert du on ss.AgentZID=du.agentID
--where flag is null and du.samplesize>6
--order by newid()

Insert Into Survey_Source_New (LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp])  
Select top (@Top) @Top=du.samplesize,ss.LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp]  
From Survey_source_Level1 ss
inner join DataToInsert du on ss.AgentZID=du.agentID
where flag is null and du.samplesize<7
order by newid()

The error that I get here is

Msg 4115, Level 15, State 1, Line 4
The reference to column "samplesize" is not allowed in the argument of the TOP clause. Only references to columns at an outer scope or standalone expressions and subqueries are allowed here.

Is there a workaround for this?

Any help is appreciated.

Thanks in advance.

Upvotes: 2

Views: 1322

Answers (2)

Srijan
Srijan

Reputation: 23

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,(case when day(getdate())<4 then 3 else (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
    inner join AgentsList AL on ss.AgentZID=AL.agentID
    where flag is null 
    GROUP BY 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 survey
Select LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],samplesize,    
             rank() over (partition by agentzid order by newid()) as seqnum    
      From AgentSurveys
      group by LOB, CenterName, CallDate, AgentZid, TN, Ticket, RecordingID, Cycle, [TimeStamp],samplesize      
      order by 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.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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

WITH DataToInsert AS
(
    Select AgentID, Surveys, LOB,(case when day(getdate())<4 then 3 else (day(getdate())) - (Surveys*3) end) SampleSize from Current_Agent_SurveyCount_HSI Where surveys<8 
)
Insert Into 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 (order by newid()) as seqnum
          From Survey_source_Level1 ss inner join
               DataToInsert du on ss.AgentZID=du.agentID
          where flag is null and 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.

Upvotes: 2

Related Questions