November 9, 2016 at 1:56 am
Hi Guys, I am working on an email alert to be sent to customers along with some information on monthly basis. The format of this mail should be like this:
Hi <customer name>,
Your purchase detail for the month is as follows:
Q1 Q2 Q3
Item_123 200 100
Item_234 550 10 500
Item_567 260 150
Here, details will be in tabular format and for every customer it will check for which item purchase was done in which quarter. Then number of quarters (or columns) should be created dynamically from min Q1 to max Q4 depending on the item's availability for that customer. Can anyone please suggest to create such dynamic tabular format data ? Assume All the data is available in one table only.
November 9, 2016 at 2:32 am
sqlenthu 89358 (11/9/2016)
Hi Guys, I am working on an email alert to be sent to customers along with some information on monthly basis. The format of this mail should be like this:Hi <customer name>,
Your purchase detail for the month is as follows:
Q1 Q2 Q3
Item_123 200 100
Item_234 550 10 500
Item_567 260 150
Here, details will be in tabular format and for every customer it will check for which item purchase was done in which quarter. Then number of quarters (or columns) should be created dynamically from min Q1 to max Q4 depending on the item's availability for that customer. Can anyone please suggest to create such dynamic tabular format data ? Assume All the data is available in one table only.
We will need a lot more information in order to assist you on this, can you elaborate please?
😎
November 9, 2016 at 3:57 am
You should start with a procedure to create the default tekst of the mail and insert the customer name dynamically. Basically the query will look like:
SELECT 'Dear '+name+', {add you message tekst here}'
FROM [customers]
WHERE customer_id IN ({values})
Next you can use it with the sp_send_dbmail procedure to build and send an email message. Look at the available parameters in the sp_send_dbmail procedure to include the output of your result SELECT statement.
With some more effort it is also possible to rebuild the output of the query to a HTML table. You can use this HTML as message in the sp_send_dbmail procedure. Here's an article describing this...[/url]
November 9, 2016 at 5:37 am
So I have a table as following information
CustID productID qty amt orderdate
1001 prod1 10 1500 01 Feb 2016
1001 prod2 20 500 02 Feb 2016
1001 prod1 5 750 10 Feb 2016
1001 prod1 2 300 12 Feb 2016
1001 prod2 5 125 20 Feb 2016
This is just some records for one customer. There are many other records.
Now I want mail to be generated like this:
<<Start of mail>>
Order Details for Customer 1001:
Prod1 Prod2
01 Feb 2016 02 Feb 2016
10 Feb 2016 20 Feb 2016
12 Feb 2016
<<End of mail>>
Here the columns Prod1, Prod2 etc should be dynamic based on how many distinct products a customer purchased in that month. And dates show which all dates the purchase was done.
Ignore my original requirement where I mentioned the amount in detail section. I actually need the dates.
November 9, 2016 at 6:08 am
This sounds like an IDEAL candidate for the "data driven subscriptions" feature available in the "enterprise" version of SQL Server. You can customize an SSRS report by adding a query that supplies the parameter values associated with each recipient, including their e-mail address and customer id value, and that will allow SSRS to automatically deliver the report to each recipient by e-mail, and perhaps even use Excel as the format in which to deliver it. If you don't happen to have the "enterprise" version of SQL Server, then this is not an option, but given the relative ease with which it can be used, I figured it was at least worth mentioning.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 9, 2016 at 11:52 pm
Thanks mgnson, but I want to do it using SQL programming. Is it possible ?
November 23, 2016 at 6:49 am
sqlenthu 89358 (11/9/2016)
Thanks mgnson, but I want to do it using SQL programming. Is it possible ?
Sorry for the delay in response. I've been totally swamped. Using SSRS (Reporting Services) does not mean you won't be using T-SQL. You'll need one query to generate the data that uses a parameter for the customer identifier, and a separate query to identify all the customers, their customer identifier, and their e-mail address. As this appears to be a fairly simple e-mail, you can set the format to PDF, and then it's a matter of putting together a fairly simple report that will get attached to the e-mail as a PDF file.
That said, you would still need to set up SQL Server to use e-mail, but you'll be doing that to set up the ability to use the sp_send_dbmail stored procedure, and I don't know that setting up either of those abilities is all that different.
If you don't have the Enterprise version of SQL Server, then the stored procedure to send e-mail is pretty much the only option, but it's actually more challenging, as you'll have to compose the e-mail using T-SQL. Not a pretty task, but certainly not impossible.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply