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.

Explain using tables what you are trying to do,there could be a way with out loops — TheGameiswar, Aug 02 '16 at 10:12
You can use SQL cursor as shown in tutorial http://www.kodyaz.com/articles/cursor.aspx . But be aware that cursors are the last way for solving a problem on SQL Server for performance considerations — Eralper, Aug 02 '16 at 10:22
I need to obtain this result but in separate folder for each agent — BigBlack, Aug 02 '16 at 11:01
Please read [this](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) for some tips on improving your question. — HABO, Aug 02 '16 at 12:14
Cross Join doesn't give me the result to have different table. I need a table for each agent, as If i made different query one after another and then executing I obtain different result. All equal queries but with only a where condition changed, the Agent that must be 'A1' Then 'A4' Then 'A14' and so on. Numbers are not sequential but random, so I can't add 1 each cycle. — BigBlack, Aug 02 '16 at 14:35

score 1 · Accepted Answer · edited May 23 '17 at 11:51

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

Initially it doesn't work. Then I remove the end under where, and it create a table, I think, for each agent, but tables are all empty. If I remove the WHERE ANAGRAFICAAGENTI.CODAGENTE = @AgentID condition and leave all the other, I've all table with equal results --- If I leave all like your I've this error Msg 156, Level 15, State 1, Line 39 Incorrect syntax near the keyword 'CLOSE'. — BigBlack, Aug 03 '16 at 11:10
Tryed also with a sample version of the script with only `SELECT codagente1, datadoc,tipodoc,codclifor FROM INOXDGI.dbo.TESTEDOCUMENTI LEFT JOIN dbo.ANAGRAFICAAGENTI ON CODAGENTE=CODAGENTE1 WHERE TESTEDOCUMENTI.CODAGENTE1 = @AgentID` — BigBlack, Aug 03 '16 at 11:36
@BigBlack i accidentally left an extra `END` in there. I fixed it. It will run and i provided a sample. — S3S, Aug 03 '16 at 13:09
the problem was the VARCHAR 4.. It needed more chars. I've increased to 10 and now it work. I'm trying to set an HTML format to the table to send it via mail. The html code where I need to put? Beetween Begin and Select? — BigBlack, Aug 03 '16 at 15:24
@BigBlack i used 4 just for test data. Sorry. In regards to your HTML question, you should open another question for this. Feel free to link this question to that question but since it's a completely different topic (HTML) you will want to tag the question with HTML and SQL Server so more users will see it. — S3S, Aug 03 '16 at 15:26
Ok I found the solution, but I've still one trouble with this code. It create beetween table 2 blank table that create some trouble exporting data. How can I tell the script to ignore those blank table. I don't know why they are generated.. — BigBlack, Aug 03 '16 at 16:03
The table would have been created, or returned "blank", if there were no results for the select with the given AgentID. To get around this you need to not pass in AgentIDs where there won't be any results returned. So, in the `DECLARE c CURSOR FOR SELECT DISTINCT CODAGENTE FROM ANAGRAFICAAGENTI` you'll need to inner join to `TESTEDOCUMENTI` or do some other logic that if the agent isn't in the table, they are ignored and therefore not fetched in the cursor. — S3S, Aug 03 '16 at 16:17
Yes I found that I need to put the same where condition for agent cursor and the principal query. Thank you! — BigBlack, Aug 04 '16 at 09:56

zajonc · Answer 2 · 2016-08-02T19:21:31.167

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.

Thank you zajonc but this is to have most agent for month and I like that, but what I need now is the below solution of scsimon. Thank you Anywhere! — BigBlack, Aug 03 '16 at 11:02

Loop the same query for every row of another table

2 Answers2