-2

How can I send a report to each seller about their sales? I can already generate the report, but I cannot send it individually, this is an example of my data

enter image description here

this is my script, i hope someone can help me please:

    /*Declare variables*/
declare @tableHTML varchar(max)
declare @mail varchar(300);
--declare @note nvarchar(500);


--Setting greetings
set @tableHTML = '<html><body><table><tr><td><p><font color="#000080" size="3" face="Verdana">Hello!</font></p><p style="margin-left:30px;"><font size="3" face="Verdana">Prueba envio de reporte:</font></p></td></tr>';
--Setting table header
set @tableHTML=@tableHTML
+'<tr><td><table border="1" style="border:1px solid #d5d5d5;border-collapse:collapse;border-spacing:0;margin-left:30px;margin-top:20px;"><tr style="height:25px;background-color: rgb(219, 240, 251);">
<th style="width:200px;">tdCodVendedor</th>
<th style="width:200px;">tdEMail</th>
<th style="width:200px;">tdCodCli</th>
<th style="width:200px;">tdCliente</th>
<th style="width:200px;">tdPO</th>
<th style="width:200px;">tdFactura</th>
<th style="width:200px;">tdMoneda</th>
<th style="width:200px;">tdVencimiento</th>
<th style="width:200px;">tdFecha_Fact</th>
<th style="width:200px;">tdCondiciones</th>
<th style="width:200px;">tdVendedor</th>
<th style="width:200px;">tdActual</th>
<th style="width:200px;">td1a30</th>
<th style="width:200px;">td31a60</th>
<th style="width:200px;">td61a90</th>
<th style="width:200px;">td91a120</th>
<th style="width:200px;">tdMas120</th></tr>';

--Enable cursors
declare c cursor for

--Query results

select
a.tdEMail
--,a.note
,@tableHTML+'<tr><td align="center">'+tdCliente+'</td>'
+'<td align="center">'+tdCodVendedor+'</td>'
+'<td align="center">'+tdEMail+'</td>'
+'<td align="center">'+tdCodCli+'</td>'
+'<td align="center">'+tdCliente+'</td>'
+'<td align="center">'+tdPO+'</td>'
+'<td align="center">'+tdFactura+'</td>'
+'<td align="center">'+tdMoneda+'</td>'
--+'<td align="center">'+tdVencimiento+'</td>'
--+'<td align="center">'+tdFecha_Fact+'</td>'
+'<td align="center">'+tdCondiciones+'</td>'
+'<td align="center">'+tdVendedor+'</td>'
+'<td align="center">'+tdActual+'</td>'
+'<td align="center">'+td1a30+'</td>'
+'<td align="center">'+td31a60+'</td>'
+'<td align="center">'+td61a90+'</td>'
+'<td align="center">'+td91a120+'</td>'
+'<td align="center">'+tdMas120+'</td></tr>'
from
(
SELECT --top 10
    tdCodVendedor= v.slsperid,
    tdEMail = v.EMailAddr,
    tdCodCli=Rtrim(d.CustID),
    tdCliente = Rtrim(d.CustID)+' '+Rtrim(c.Name),
    --tdPO = SOShipHeader.CustOrdNbr,
    tdFactura = d.RefNbr, 
    tdMoneda = d.CuryID, 
    --tdVencimiento = d.DueDate, 
    --tdFecha_Fact = d.DocDate, 
    tdCondiciones =  ISNULL(t.Descr, ''),
    tdVendedor = Rtrim(c.SlsperId)+' '+Rtrim(v.Name),
    --Actual
    tdActual =  

              CASE WHEN d.DocType NOT IN ('CM', 'PA', 'PP') AND CONVERT(VarChar(50), GETDATE(), 102) <= d.DueDate 
                                  OR d.DocType IN ('CM', 'PA', 'PP') AND (CONVERT(VarChar(50), GETDATE(), 102)<=d.DocDate OR ARSetup.S4Future09=0) 
                            THEN  '$' + CONVERT(VARCHAR(50), (CAST(d.DocBal AS money)*CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END), 1)
                         ELSE  '$0.00'
                    END,
    --1 a 30                
    --'Past00(1 a 30)' = 
    td1a30=

            CASE WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') 
                                                      THEN d.DocDate 
                                                    ELSE d.DueDate 
                                               END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays00 AND 
                     DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') 
                                                      THEN d.DocDate  
                                                    ELSE d.DueDate 
                                               END, CONVERT(VarChar(50), GETDATE(), 102)) >= 1 AND
                                 (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) 
                              THEN  '$' + CONVERT(VARCHAR(50), (CAST(d.DocBal AS money)*CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END), 1)
                    ELSE '$0.00' END,
    --31 a 60
    --'Past01 (31 a 60)' = 
    td31a60=
    CASE
        WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays01 AND--menor 60 
            DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays00 AND--mayor 30
                        (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) 
                        THEN  '$' + CONVERT(VARCHAR(50), (CAST(d.DocBal AS money)*CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END), 1)
        ELSE '$0.00' END,
    --61 a 90
    --'Past02 (61 a 90)' = 
    td61a90=
    CASE
        WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= s.AgeDays02 AND --menor 90
            DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays01 AND --mayor 60
                        (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) 
                        THEN  '$' + CONVERT(VARCHAR(50), (CAST(d.DocBal AS money)*CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END), 1)
    --91 a 120
        ELSE '$0.00' END,
    --'Over02 (91 a 120)' = 
    td91a120=
    CASE
        WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) <= 120 AND --menor 120
        DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > s.AgeDays02 AND --mayor 90
                (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) 
                THEN  '$' + CONVERT(VARCHAR(50), (CAST(d.DocBal AS money)*CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END), 1)
        ELSE '$0.00' END, 
    --mas de 120
    --'Over03 (mas 120)' = 
    tdMas120=
    CASE
        WHEN DATEDIFF(Day, CASE WHEN d.DocType IN ('CM', 'PA', 'PP') THEN d.DocDate ELSE d.DueDate END, CONVERT(VarChar(50), GETDATE(), 102)) > 120 AND 
                (d.DocType NOT IN ('CM', 'PA', 'PP') OR ARSetup.S4Future09=1) 
                THEN  '$' + CONVERT(VARCHAR(50), (CAST(d.DocBal AS money)*CASE WHEN d.DocType IN ('IN','DM','FI','NC','AD') THEN 1 ELSE -1 END), 1)
        ELSE '$0.00' END 
    --




  FROM   

ARDoc d 
left JOIN SOShipHeader ON D.RefNbr=SOShipHeader.InvcNbr
                           --  ON d.CpnyID=y.CpnyID 
                     INNER JOIN AR_Balances b
                             ON b.CustID=d.CustID 
                     INNER JOIN Customer c 
                             ON c.CustID=d.CustID 
                     inner join Salesperson v
                             on v.SlsperId=c.SlsperId
                     INNER JOIN (SELECT StmtCycleID, AgeDays00 = CONVERT(INT,AgeDays00), 
                                        AgeDays01 = CONVERT(INT,AgeDays01), AgeDays02 = CONVERT(INT,AgeDays02) 
                                   FROM ARStmt) s 
                             ON s.StmtCycleID=c.StmtCycleID 
                      LEFT JOIN Terms t 
                             ON d.Terms <> '' AND t.TermsID=d.Terms 
                     CROSS JOIN ARSetup (NOLOCK)
 WHERE d.Rlsed=1 AND d.DocBal<>0
 order by v.SlsperId
) a

open c

fetch next from c into 
@mail,
@tableHTML;

while @@FETCH_STATUS = 0

begin

EXEC msdb.dbo.sp_send_dbmail
@profile_name= 'Mail'  --Defined sql server Name of mailbox account
,@recipients=@mail
,@subject='Prueba de envio Automatizado Antiguedad por vendedor (NO HACER CASO)'
,@body= @tableHTML
,@body_format='HTML'
fetch next from c into 
@mail
--,@note
,@tableHTML;
end
close c;
deallocate c;

closed as unclear what you're asking by MDCCL, John Eisbrener, Marco, John aka hot2use, Laurenz Albe Sep 12 at 12:17

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • So what exactly do you want? From browsing quickly through your code, it looks like you have everything you need. – Yannick Liekens Sep 11 at 11:47
  • Hi Yannick, thanks for your response, i need that the salesperson just receive his customer information, not all. thanks – Manuel Vela Sep 11 at 14:39
  • When you run the script, the recipient receives only one line per invoice, and receives an infinity of emails, one for each invoice and not all of your invoices – Manuel Vela Sep 11 at 14:44
  • Forget about email for a moment. Your code sends one email for each row of your query. Adjust your query so that it returns in one row exactly what you want to be sent in one email, then use that query in the procedure. – mustaccio Sep 11 at 17:50
  • Hi mustachio, indeed the query sends an email for each line, and I don't know how to group it so that it sends to each seller the corresponding part – Manuel Vela Sep 12 at 16:01
0

This appears to be standard control-break logic where you want to check to see when the control column (in this case, SalesId) changes and send the email per control column.

I've mocked up some demo data in a table variable. This would simulate the data coming out of your complex query. In the sample data, change the string YourEmail@test.com to a real email address before testing. I actually used my email address for all of the rows in my testing to make sure I got all of the emails.

--demo setup
DECLARE @SalesData TABLE (
    SalesId INT
    ,SalesEmail VARCHAR(100)
    ,SalesClient varchar(100)
    ,SalesAmt1 DECIMAL(11, 2)
    ,SalesAmt2 DECIMAL(11, 2)
    );

--change YourEmail@test.com to a real email address to test
insert into @SalesData(SalesId,SalesEmail,SalesClient,SalesAmt1,SalesAmt2) values
(1,'YourEmail@test.com','Customer1',100.00,110.00),
(1,'YourEmail@test.com','Customer2',100.00,110.00),
(2,'YourEmail@test.com','Customer3',200.00,210.00),
(2,'YourEmail@test.com','Customer4',200.00,210.00),
(3,'YourEmail@test.com','Customer5',300.00,310.00),
(3,'YourEmail@test.com','Customer6',300.00,310.00);

Here's is the sample control-break logic that uses a cursor to read the data from the @SalesData table. I've put some comments in the code in an effort to explain how the control-break logic works. In my example, 3 separate emails should come out. Each email would be for a given customer.

Note: you may have to clean up the HTML format to your desired specifications.

--solution
Declare @SalesId INT
    ,@SalesEmail VARCHAR(100)
    ,@SalesClient varchar(100)
    ,@SalesAmt1 DECIMAL(11, 2)
    ,@SalesAmt2 DECIMAL(11, 2);

declare @previousSalesId int;   --used for control break check 
declare @previousSalesEmail varchar(100);   
declare @tableHTML varchar(max);
declare @TableHTMLHeader varchar(max);
SET @TableHTMLHeader = '<html><body><table><tr><td><p><font color="#000080" size="3" face="Verdana">Hello!</font></p><p style="margin-left:30px;"><font size="3" face="Verdana">Sales Report:</font></p></td></tr>';
SET @TableHTMLHeader = @TableHTMLHeader
+'<tr><td><table border="1" style="border:1px solid #d5d5d5;border-collapse:collapse;border-spacing:0;margin-left:30px;margin-top:20px;"><tr style="height:25px;background-color: rgb(219, 240, 251);">
<th style="width:200px;">tdSalesId</th>
<th style="width:200px;">tdEMail</th>
<th style="width:200px;">tdClient</th>
<th style="width:200px;">tdAmt1</th>
<th style="width:200px;">tdAmt2</th></tr>';

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT SalesId
    ,SalesEmail
    ,SalesClient
    ,SalesAmt1
    ,SalesAmt2
FROM @SalesData
ORDER BY SalesId;

OPEN _CURSOR

--initial fetch of data
FETCH NEXT
FROM _CURSOR
INTO @SalesId
    ,@SalesEmail
    ,@SalesClient
    ,@SalesAmt1
    ,@SalesAmt2;

SET @previousSalesId = @SalesId         --save the SalesId for control break check later
SET @previousSalesEmail = @SalesEmail   --save the SalesEmail for 
SET @tableHTML = @TableHTMLHeader

while @@FETCH_STATUS = 0
begin
    IF @previousSalesId <> @SalesId     --when SalesId changes, send the accumulated tableHTNL data 
    BEGIN
        SET @tableHTML = @tableHTML + '</table></body></html>'  --close up the tags

        EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile' 
            ,@recipients = @previousSalesEmail
            ,@subject = 'Sales Report'
            ,@body = @tableHTML
            ,@body_format = 'HTML'

        SET @tableHTML = @TableHTMLHeader   --re-initialize tableHTML with a fresh HTML header
    END

    --generate HTML table rows for each cursor row
    set @tableHTML = 
    @tableHTML+'<tr><td align="center">'+convert(varchar(10),@SalesId)+'</td>'
    +'<td align="center">'+@SalesEmail+'</td>'
    +'<td align="center">'+@SalesClient+'</td>'
    +'<td align="center">'+Convert(varchar(10),@SalesAmt1)+'</td>'
    +'<td align="center">'+Convert(varchar(10),@SalesAmt2)+'</td></tr>'

    --keep track of the SalesId and SalesEmail
    SET @previousSalesId = @SalesId
    SET @previousSalesEmail = @SalesEmail

    FETCH NEXT
    FROM _CURSOR
    INTO @SalesId
        ,@SalesEmail
        ,@SalesClient
        ,@SalesAmt1
        ,@SalesAmt2;
end

--prepare to send final email
SET @tableHTML = @tableHTML + '</table>'

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile' --Defined sql server Name of mailbox account
    ,@recipients = @previousSalesEmail
    ,@subject = 'Sales Report'
    ,@body = @tableHTML
    ,@body_format = 'HTML'

close _CURSOR
DEALLOCATE _CURSOR
  • Hi Scott, Thank you very much for your help, I comment that the query perfectly groups sales by seller, That's how I need it. Effectively 3 emails are sent, the problem is that it sends the 3 emails to the first recipient and I require that each be sent according to your seller number. could you help me solve it please, the truth is that I do not have much knowledge in SQL and the query I managed to assemble according to many examples that I have seen in this excellent forum, thanks – Manuel Vela Sep 12 at 15:13
  • @ManuelVela - in my sample example of 6 rows, I have SalesId 1,2 and 3. If you take that sample data and change the SalesEmail address for EACH of the rows for the same SalesId, 3 emails should come out EACH going to that SalesId email. Example, change the email address for the 2 rows having SalesId 1 to a valid email(test1@test.com), the email address for the 2 rows having SalesId 2 to a different valid email address(test2@test.com), the email address for the 2 rows that having SalesId 3 to a different valid email address (test3@test.com). 3 emails should come out going to 3 different users – Scott Hodgin Sep 12 at 17:06
  • @ScottHodgin- I already did what you told me by changing the (SalesId) 1,2 and 3 emails to valid emails, but when running the query, the 3 emails go to the mail of (SalesId) 1. (SalesId) 2 and 3 do not arrive, could you please help me. Thank you – Manuel Vela Sep 13 at 18:20
  • I'm out of ideas - when I run my example on my system, I get 3 separate emails. If you update your question and provide your latest code attempts, perhaps we can spot the flaw in your logic. I'm headed out for the day, but might revisit on Monday. – Scott Hodgin Sep 13 at 18:23
  • thanks in advance, yes i get 3 separate emails, but all of them arrive to the first email inserted in your code. i need to send them to each email. i really aprreciate your help – Manuel Vela Sep 13 at 21:11

Not the answer you're looking for? Browse other questions tagged or ask your own question.