May 23, 2014 at 2:11 am
Hi guys,
First of all apologies for the vague title. Hopefully the next bit will be clearer!
I basically been asked to send off emails to our Suppliers when their order is due in 7 days. 1 Email for all order items outstanding with additional info added.
Using 4 tables to pull all this info out.
At the moment once I have completed the sql task of my package I get results such like...
email PO Company orderitem
test@abc.com P/049416 Testc 123456
test@abc.com P/049416 Testc 456132
johndoe@abc.com P/049416 johndoe Inc 465465
123@abc.com P/049418 walmart 123123
123@abc.com P/049419 tesco 135153
After the sql task I have a foreach loop, that takes the email address and adds it into the field of the 2send emails task" and the rest of the columns are added into the message source. Message send fine.
Problem is I don't want to send TestC two emails. I only want to send one.
So I'm kind of stuff as to where to go with this?
Any help would be appreciated and if you need additional info let me know. Thanks
May 23, 2014 at 2:20 am
1. Create a view for the data you need to work on.
2. Query DISTINCT customer email from the view
3. Loop on the customers
4. Query the view again filtering for current customer
5. Send email
-- Gianluca Sartori
May 23, 2014 at 2:32 am
Thanks for the quick reply.
Any resources to do this? At the moment I have pretty much been reading articles etc to get this far lol!
If you could point me in the right direction and I'll see how far I get. Want to do as much myself just for the experience but it has been very stop start
May 23, 2014 at 3:24 am
Personally I would group on customer, PO and email and concatenate the order IDs. This will get you one email to send at a customer with all of the orderIDs.
Concatenating Row Values in Transact-SQL[/url]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 23, 2014 at 4:30 am
You know I had Concatenated the values BUT, didn't think of grouping the other fields. This may work! I'll give you guys an update, appreciate the replys
Thanks
July 9, 2014 at 5:03 am
Hi guys,
Finally got back to this. So I amended my code but kind of ended back in the same place.
I get multiple rows for emails, despite only wanted one row. IF I remove the "group by o.id" the code falls over because needs to be an aggregate function or groupby.
Any ideas
Thanks
SELECT *
FROM
(
SELECT c.email,
o.id,
STUFF((SELECT ',' + status + ' ' + partid
FROM items
WHERE orderid = o.id
AND DATEDIFF(d, CURRENT_TIMESTAMP , duedate) = 8
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
AS list
FROM orders o
INNER JOIN traders t
ON o.traderid = t.id
INNER JOIN contactcommunications c
ON t.id = c.traderid
GROUP BY o.id, c.email
) AS y
WHERE y.list IS NOT NULL
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply