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