May 19, 2011 at 12:46 pm
I need to get random samples for auditing our customer service agents. Specifically, I need a query to get 10 samples per agent per week. I did some searching here, and found some useful info about NEWID(). Here is the query I have so far. This only shows me a total of 10 cases. What I need is 10 cases per agent. Is there a way to do that?
thank you!
declare @DateFrom as datetime
declare @DateTo as datetime
set @DateFrom = dateadd(week, datediff(week, 0, getdate()) - 1, -1) -- first day of previoius week
set @DateTo = dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0,getdate()), -1)) -- last day of previous week
select top 10 CaseNumber, AgentName
fromdbo.CaseSummary
whereCaseDate >= @DateFrom
andCaseDate <= @DateTo
order by NEWID()
May 19, 2011 at 1:17 pm
Probably this?
declare @DateFrom as datetime
declare @DateTo as datetime
set @DateFrom = dateadd(week, datediff(week, 0, getdate()) - 1, -1) -- first day of previoius week
set @DateTo = dateadd(ms,-3,DATEADD(wk, DATEDIFF(wk,0,getdate()), -1)) -- last day of previous week
SELECT OuterTable.CaseNumber, OuterTable.AgentName
FROM dbo.CaseSummary OuterTable
CROSS APPLY
( SELECT TOP 10 CaseNumber, AgentName
FROM dbo.CaseSummary InnerTable
WHERE CaseDate >= @DateFrom
AND CaseDate <= @DateTo
AND OuterTable.CaseNumber = InnerTable.CaseNumber
AND OuterTable.Agent = InnerTable.Agent
ORDER BY NEWID()
) CrsApp
May 20, 2011 at 5:11 am
Thanks for the suggestion ColdCoffee. I tried that, but the query returned way more than 10 results for each agent. I will tinker around with that query, and see if I can get it to work.
If anyone else has a suggestion, I would appreciate it 🙂
May 20, 2011 at 5:19 am
Something like this?
WITH RandimisedResults AS (
select CaseNumber, AgentName,
rn = ROW_NUMBER() OVER (PARTITION BY AgentName ORDER BY NEWID())
from dbo.CaseSummary
where CaseDate >= @DateFrom
and CaseDate <= @DateTo
) SELECT * FROM RandimisedResults WHERE rn < 11
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 20, 2011 at 5:29 am
Yes! That works.... thanks Chris
May 20, 2011 at 5:32 am
You're welcome, thanks for the feedback.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply