Loop the same query for every row of another table

Question

I'm trying to obtain a loop result for each row in a different table. I need to know which product line, each sales agent, sell in a month.

I've the query that show me how to know the total of product lines. I need only to insert inside it, the "CODAGENT" in the where condition, looping tHrough each agent in the Agents TABLE. So to make an example not in sql language.

-----THIS IS NOT A USEFUL CODE, IT'S ONLY FOR UNDERSTANDING----
For Each Row in Agents TAble Do
Set #CODAGENT = Row 1,2,3,....
Select
-routine for selecting what i need with inside WHERE CODAGENT =  #CODAGENT 
Next Row
-----THIS IS NOT A USEFUL CODE, IT'S ONLY FOR UNDERSTANDING----

I need to obtain this result but in separate table for each agent

    SELECT                  
                           TABCATEGORIE.DESCRIZIONE, 
                            TABCATEGORIESTAT.DESCRIZIONE, 
                             LEFT(SUM(TOTNETTORIGA),LEN(SUM(TOTNETTORIGA))-2),
                            TABGRUPPI.DESCRIZIONE,
                            ANAGRAFICAAGENTI.CODAGENTE,
                            ANAGRAFICAAGENTI.DSCAGENTE  
FROM dbo.TESTEDOCUMENTI 
INNER JOIN dbo.RIGHEDOCUMENTI ON PROGRESSIVO=IDTESTA
INNER JOIN dbo.ANAGRAFICAARTICOLI
ON CODART=ANAGRAFICAARTICOLI.CODICE
INNER JOIN dbo.TABCATEGORIE ON CATEGORIA=TABCATEGORIE.CODICE 
INNER JOIN dbo.TABCATEGORIESTAT ON CODCATEGORIASTAT=TABCATEGORIESTAT.CODICE 
INNER JOIN dbo.TABGRUPPI ON GRUPPO=TABGRUPPI.CODICE 
INNER JOIN dbo.ANAGRAFICAAGENTI ON ANAGRAFICAAGENTI.CODAGENTE=CODAGENTE1
WHERE dbo.TESTEDOCUMENTI.DOCCHIUSO = '0' AND dbo.TESTEDOCUMENTI.TIPODOC = 'FVC' AND dbo.TESTEDOCUMENTI.DATADOC BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0) AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-2, -1)
GROUP BY GRUPPO,CATEGORIA,CODCATEGORIASTAT,TABCATEGORIE.DESCRIZIONE,TABCATEGORIESTAT.DESCRIZIONE,TABGRUPPI.DESCRIZIONE,ANAGRAFICAAGENTI.CODAGENTE,ANAGRAFICAAGENTI.DSCAGENTE 
ORDER BY CODAGENTE DESC

It's Italian, I don't know if you understand

Sample Data

ANAGRAFICAAGENTI
CODAGENTE | DSCAGENTE
A1        | Agent Name

then there is the result of the query. So The result is 

CATEGORY    | CATEGORY2     |TOTNETTORIGA| GRUPPI           | CODAGENTE | DSCAGENTE
------------+---------------+------------+------------------+-----------+----------
TAVOLI      | TAVOLI        | 22571.36   | PRODOTTO FINITO  | A    77   | name
PENSILI     | PENSILI       | 1319.12    | PRODOTTO FINITO  | A    77   | name
LAVATOIO    | LAVATOIO      | 7411.08    | PRODOTTO FINITO  | A    77   | name
LAVATOIO    | MACELLERIA    | 505.00     | PRODOTTO FINITO  | A    77   | name
MACELLERIA  | MACELLERIA    | 3762.00    | PRODOTTO FINITO  | A    77   | name
LINEA PESCE | LINEA PESCE   | 3824.00    | PRODOTTO FINITO  | A    77   | name
TAVOLI      | TAVOLI        | 1073.60    | PRODOTTO FINITO  | A    76   | name1
PENSILI     | PENSILI       | 262.80     | PRODOTTO FINITO  | A    76   | name1

Reached This New Step to achieve my goal but still doesn't work. This is my query:

---CREO IL CURSORE C PER CALCOLARE GLI AGENTI---
DECLARE c CURSOR FOR
SELECT DISTINCT
    ANAGRAFICAAGENTI.CODAGENTE
FROM  dbo.ANAGRAFICAAGENTI
----DICHIARO LA VARIABILE PER AGENTE
DECLARE @AgentID VARCHAR(4)
----PRENDI IL PRIMO AGENTE E METTILO NELLA VARIABILE----
OPEN c
FETCH NEXT FROM c INTO @AgentID

WHILE @@FETCH_STATUS = 0
    BEGIN
         SELECT 

                           ANAGRAFICAAGENTI.DSCAGENTE,
                             ANAGRAFICAAGENTI.CODAGENTE,
                               TABCATEGORIE.DESCRIZIONE, 
                            TABCATEGORIESTAT.DESCRIZIONE, 
                             LEFT(SUM(TOTNETTORIGA),LEN(SUM(TOTNETTORIGA))-2),
                            TABGRUPPI.DESCRIZIONE

FROM dbo.TESTEDOCUMENTI 
INNER JOIN dbo.RIGHEDOCUMENTI ON PROGRESSIVO=IDTESTA
INNER JOIN dbo.ANAGRAFICAARTICOLI
ON CODART=ANAGRAFICAARTICOLI.CODICE
INNER JOIN dbo.TABCATEGORIE ON CATEGORIA=TABCATEGORIE.CODICE 
INNER JOIN dbo.TABCATEGORIESTAT ON CODCATEGORIASTAT=TABCATEGORIESTAT.CODICE 
INNER JOIN dbo.TABGRUPPI ON GRUPPO=TABGRUPPI.CODICE 
LEFT JOIN dbo.ANAGRAFICAAGENTI ON ANAGRAFICAAGENTI.CODAGENTE=CODAGENTE1
WHERE ANAGRAFICAAGENTI.CODAGENTE =  @AgentID AND dbo.TESTEDOCUMENTI.DOCCHIUSO = '0' AND dbo.TESTEDOCUMENTI.TIPODOC = 'FVC' AND dbo.TESTEDOCUMENTI.DATADOC BETWEEN DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-2, 0) AND DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-2, -1)
GROUP BY GRUPPO,CATEGORIA,CODCATEGORIASTAT,TABCATEGORIE.DESCRIZIONE,TABCATEGORIESTAT.DESCRIZIONE,TABGRUPPI.DESCRIZIONE,ANAGRAFICAAGENTI.CODAGENTE,ANAGRAFICAAGENTI.DSCAGENTE 
ORDER BY CODAGENTE DESC,SUM(TOTNETTORIGA) desc
--PRENDI IL PROSSIMO AGENTE---
FETCH NEXT FROM c INTO @AgentID
END
--PULISCI---
CLOSE c
DEALLOCATE c

This make me have different table, I think, one for each agent in the table. But They are all empty. If I remove ANAGRAFICAAGENTI.CODAGENTE = @AgentID in the WHERE condition I obtain the same table but with equal result for each one. They are all equal.

Answer 1

SQL works best with set based operations, but one way to use a loop for your case is using a CURSOR with FETCH. You'll need to research these and apply it properly to your data, especially since you said "obtain this result but in separate table". I don't know if you want to insert into a new table or just return the results in separate window panes. This will get you started

--declare a cursor which will be the ID's of your agents. You can use what ever you want to limit your data off of
DECLARE c CURSOR FOR
SELECT DISTINCT
    CODAGENTE
FROM ANAGRAFICAAGENTI

DECLARE @AgentID VARCHAR(4)

--get the first agent id and place it into a variable
OPEN c
FETCH NEXT FROM c INTO @AgentID


--for each agent id, select some data where the agent id equals the current agent id in the cursor
WHILE @@FETCH_STATUS = 0
    BEGIN
         SELECT 
             --put your code here for selecting, inserting into a table, etc
         WHERE ANAGRAFICAAGENTI.CODAGENTE = @AgentID --or what ever is appropiate

        --get the next agent
        FETCH NEXT FROM c INTO @AgentID
    END
--clean up
CLOSE c
DEALLOCATE c

And for bonus points, I would suggest you begin using table aliases in your code. It will make it a lot more legible for you, and others, in the future.

https://technet.microsoft.com/en-us/library/ms187455(v=sql.105).aspx

SQL Table Aliases - Good or Bad?

SAMPLE SET

Click HERE to run the code below...

IF OBJECT_ID('tempdb..#agents') IS NOT NULL DROP TABLE #agents
IF OBJECT_ID('tempdb..#items') IS NOT NULL DROP TABLE #items

create table #agents (AgentID varchar(2), name varchar(50))
insert into #agents values
('A1','Julius Cesar'),
('B2','Albert Einstien')


create table #items (AgentID varchar(2), ItemID int, ItemName varchar(50))
insert into #items (AgentID, ItemID, ItemName) values
('A1',1,'Apple'),
('A1',2,'Pear'),
('A1',3,'Watermelon'),
('A1',4,'Grape'),
('B2',5,'Car'),
('B2',6,'Truck'),
('B2',7,'Van')

DECLARE c CURSOR FOR
SELECT DISTINCT
    AgentID
FROM #agents

DECLARE @AgentID VARCHAR(4)

--get the first agent id and place it into a variable
OPEN c
FETCH NEXT FROM c INTO @AgentID

--for each agent id, select some data where the agent id equals the current agent id in the cursor
WHILE @@FETCH_STATUS = 0
    BEGIN
         SELECT 
            AgentID, ItemID, ItemName
         FROM #items 
         WHERE AgentID = @AgentID --or what ever is appropiate

        --get the next agent
        FETCH NEXT FROM c INTO @AgentID
    END

--clean up
CLOSE c
DEALLOCATE c

Answer 2

If I unserstand your problem correctly then I think that you can use a cross apply operator.

To show what I mean lets look for some sample data. At the begining I declare three temporary tables for agents, products and transactions like this

declare @Agents table (AgentId int, AgentName nvarchar(max));
declare @Products table (ProductId int, ProductName nvarchar(max), Price money);
declare @Transactions table (TransactionId int, TransactionDate date, AgentId int, ProductId int, Quantity int); 

Then I fill that tables with some sample data like this

insert into @Agents
values (1, N'Agnet1'), (2, N'Agent2'), (3, N'Agent3'), (4, N'Agent4');

insert into @Products
values (1, N'Product1', 100), (2, N'Product2', 150.50), (3, N'Product3', 200), (4, N'Product4', 50.23);

insert into @Transactions
values  (1, '20160604', 1, 1, 5), (2, '20160704', 2, 1, 10), (3,'20160612', 2, 1, 15), (4, '20160604', 1, 2, 7), 
        (5, '20160720', 3, 4, 1), (6, '20160604', 2, 4, 3), (7, '20160730', 4, 3, 8), (8, '20160612', 2, 3, 13), 
        (9, '20160708', 4, 2, 6), (10, '20160705', 1, 3, 1), (11, '20160616', 4, 2, 17), (12, '20160709', 2, 3, 13);

And with that sample data I've prepared this query to get a statistics for each agent

declare @year int;
declare @month int;

select a.AgentName,
trans.ProductName,
trans.Amount
from @Agents as a
cross apply
(   select t.ProductId,
    max(p.ProductName) as ProductName,
    sum(t.Quantity * p.Price) as Amount
    from @Transactions as t 
    inner join @Products as p on p.ProductId = t.ProductId
    where t.AgentId = a.AgentId
    and year(t.TransactionDate) = @year
    and month(t.TransactionDate) = @month
    group by t.ProductId) as trans

For the @year = 2016 and @month = 6 on the output you can see this

AgentName   ProductName     Amount
Agnet1      Product1        500,00
Agnet1      Product2        1053,50
Agent2      Product1        1500,00
Agent2      Product3        2600,00
Agent2      Product4        150,69
Agent4      Product2        2558,50

And for the @year = 2016 and @month = 7 you will se this

AgentName   ProductName     Amount
Agnet1      Product3        200,00
Agent2      Product1        1000,00
Agent2      Product3        2600,00
Agent3      Product4        50,23
Agent4      Product2        903,00
Agent4      Product3        1600,00

As you can see I'm asking for every agent form @Agents table and for each of them I execute additional query that retrive the data about product and total amount from @Transactions table.