Q:

Does this call for a cursor or a set-based approach?

I'm having to write up a query so we can try to get an automated monthly report going. The basic idea of the query is that first we grab a list of all master ids connected to our associated companies. Example resultset:
abc123
def456
ghi789

From that list of Master IDs, we need to grab a list of all customer emails that occur twice or more in the records of that company, on the basis that this means the email address might be misattributed or otherwise invalid. This query should return a list of email addresses coupled with how often they occur. Example resultset:
ACME Agency abc123 fakeemail@fake.com   2
ACME Agency abc123 thisisonlyatest@info.com 94
ACME Agency abc123 notarealemail@none.com   5
ACME Agency abc123 helloworld@dummy.com 2
ACME Agency abc123 info@info.net 2

From there, we need to get a transaction history associated with each email address - if all goes well, we should get a number of rows equal to the totals given in the previous query - so, 105 rows total in this case. Then we append those rows to a temp table that will eventually include the entire resultset for every master row.
When dealing with this kind of situation, my first impulse is to go to a while loop or a cursor, since that's the kind of logic I'm most familiar with. My research said that, in general, set-based approaches are the way to go. However, it also said that when cursors are necessary, it's generally in cases where you have to iterate through every row and do something. 
My question is, is that the case here? Am I envisioning the problem correctly where cursors - double nested cursors, even - are a necessary evil, or is there a set-based approach to this case? 
I can edit to include the query if need-be, but it's a bit of a beast at this point. I'd like to get its runtime speed improved, but we've got a few thousand Master IDs to loop through - about 5k, if I'm filtering this right. 

A:

Eventually got a senior developer who showed me how to do this in a non-cursor based fashion. 
My first plan was to do a query with a cursor to look through the list of master IDs we had to reference. 
DECLARE @agency_number NVARCHAR(500)

DECLARE db_cursor CURSOR FOR
SELECT Master_Agency_Number
FROM temptablelist

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @agency_number

WHILE @@FETCH_STATUS = 0
BEGIN

Once I was in a loop, I would pull the relevant email addresses into a temp table based on the agency number, and then pull another set of data based on the email table, and finally append that resultset to a final temptable that would eventually hold the entire resultset encompassing all the master agency IDs. It did the job better than some of my initial attempts - we got the right data in 45 minutes. But for what we needed, we needed it faster. 
Another developer finally showed me another way to do it. 
We loaded the master agency id along with some other information we needed into a temp table rather than using it to start a cursor. 
INSERT 
emaillist SELECT dm.[Master_Agency_Number]
,vwp.[Email_Address]
,count(*) as TotalOccurrence
FROM   table1 vwP
INNER JOIN table2 dm
ON vwP.AgencyNum = dm.Agency_Number
WHERE
   --filter for active policies
   AND vwP.CurrentTypeAct <> 'Canceled'
   AND vwP.Email_Address is not NULL
   group by dm.[Master_Agency_Number], vwp.[Email_Address]
   HAVING count(*) >= 2
   order by dm.[Master_Agency_Number] asc, count(*) desc

From there, we wrote up another query that did an inner join with this temp table to make sure we were only pulling for the relevant agencies, and then used an IN clause to make sure we were pulling from the right email addresses. 
SELECT DISTINCT
    [all the column names]
FROM table1 vwP
LEFT JOIN table2 stgA
    ON vwP.AgencyNum = stgA.Agency_Number
INNER JOIN table3 dm
    ON vwP.AgencyNum = dm.Agency_Number
INNER JOIN emaillist 
    ON emaillist.Master_Agency_Number = dm.Master_Agency_Number
WHERE
--filter for active policies
AND vwP.CurrentTypeAct <> 'Canceled'
and stgA.Email_Address is not null 
and vwp.[Email_Address] is not null
AND vwp.[Email_Address] IN (SELECT Email_Address FROM emaillist WHERE 
dm.Master_Agency_Number = emaillist.Master_Agency_Number)
ORDER BY
dm.[Master_Agency_Number], vwp.[Email_Address]

Using the INNER JOIN and the IN cut the runtime down to 45 seconds. So if you're stuck on using a cursor, try combining the two to see if that helps you parse down the data in the way you need!

