I've a table (processed_amounts) which shows 'amount', 'date_processed', and the 'agent' which processed the amount. I need a query which will pull through a specified number of processed entries, for instance, 50 processed entries, for a specified period, for instance, the previous month, for a list of several agents. Is there a way of writing this all in one query, as I'll be running this monthly? So say I needed a report of 50 entries each for 10 different agents, which were processed during the month of May 2018, could I fit this into one query?
So, for example, the table looks below;
Date_Processed │ Amount │ Agent
01/05/2018 │ £35:90 │ tbrown
25/05/2018 │ £79:00 │ asmith
My query would need to pull through 50 entries for both tbrown and asmith for May 2018.
Many thanks, for your help
If I understand correctly, you can use row_number():
select pa.*
from (select pa.*,
row_number() over (partition by agent order by ?) as seqnum
from processed_amounts pa
where agent in ( . . . ) and -- list of agents here
date_processed >= '2018-05-01' and
date_processed < '2018-06-01'
) pa
where seqnum <= 50;
The ? is for the column that determines which 50 you want -- the first in the month, the last, arbitrary 50, random 50 and so on. If you don't care which 50, you can just put in agent for an arbitrary 50 rows.