July 15, 2015 at 8:50 am
Hi all,
I have the following SP below and I need to know if there is a way to have the results emailed in either a .csv or excel file.
Any help would be great.
USE [Thomas]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[tho_daily_tracking_sp]
AS
select
cust_code,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
(SHIPPED * PRICE)
else 0 end) as TenTenSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as TenTenGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
then
(SHIPPED * PRICE)
else 0 end) as CYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as CYTDGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') AND
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1) )))
then
(SHIPPED * PRICE)
else 0 end) as PYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1 ))))
THEN
SHIPPED * (PRICE - COST)
else 0 end) as PYTDGP
from orders_ALL ords
JOIN ORD_LIST AS OL ON OL.ORDER_NO = ORDS.ORDER_NO AND EXT = ORDER_EXT
join inv_master as im on im.part_no = ol.part_no
--JOIN ARMASTER_ALL AS AR ON CUST_CODE = CUSTOMER_CODE AND ADDRESS_TYPE = 0
where ORDS.status not in ('V') AND
YEAR(date_entered) > YEAR(GETDATE()) - 2
and im.status = 'P' and cust_code <> '80990000'
GROUP BY cust_code
ORDER BY cust_code
July 15, 2015 at 8:55 am
Yes, this can be done. Give me about 5 minutes and I'll post the solution.
July 15, 2015 at 9:07 am
Okay, first you need to set up DBMail (if you haven't already).
Then you need to add some stuff to your proc.
ALTER PROCEDURE [dbo].[tho_daily_tracking_sp]
@emails VARCHAR(xxxx) = NULL
--You need this as an input parameter for your semi-colon delimited email string
--NOTE: Do NOT end the string with a semi-colon
AS
DECLARE @profile VARCHAR(50) = 'MyDBMailProfile',
@MySQLCmd VARCHAR(XXXX),
@filename VARCHAR(50) = 'MyFileName'; --You can add a date here too if you want
IF (@emails IS NULL)
SET @emails = 'MyDefaultEmailList';
--Make sure to escape your quotes below as in the first group of lines.
--I did not do it for all your code
SET @MySQLCmd = 'select cust_code,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = ''C'') and substring (user_def_fld1, 1, 4) in (''1010'')
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> ''C'') and substring (user_def_fld1, 1, 4) in (''1010'')
then
(SHIPPED * PRICE)
else 0 end) as TenTenSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C') and substring (user_def_fld1, 1, 4) in ('1010')
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C') and substring (user_def_fld1, 1, 4) in ('1010')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as TenTenGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
then
(SHIPPED * PRICE)
else 0 end) as CYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) and (type = 'C')
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) and (type <> 'C')
THEN
SHIPPED * (PRICE - COST)
else 0 end) as CYTDGP,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') AND
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then
(price * (cr_shipped * -1))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1) )))
then
(SHIPPED * PRICE)
else 0 end) as PYTDSales,
SUM (case when year (date_entered) = YEAR(GETDATE()) -1 and (type = 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1))))
then
((cr_shipped * -1) * (PRICE - COST))
when year (date_entered) = YEAR(GETDATE()) -1 and (type <> 'C') and
((month (date_entered) < month (getdate())) OR ((month (date_entered) = month (getdate()) and (day (date_entered) <= DAY (GETDATE())-1 ))))
THEN
SHIPPED * (PRICE - COST)
else 0 end) as PYTDGP
from orders_ALL ords
JOIN ORD_LIST AS OL ON OL.ORDER_NO = ORDS.ORDER_NO AND EXT = ORDER_EXT
join inv_master as im on im.part_no = ol.part_no
--JOIN ARMASTER_ALL AS AR ON CUST_CODE = CUSTOMER_CODE AND ADDRESS_TYPE = 0
where ORDS.status not in ('V') AND
YEAR(date_entered) > YEAR(GETDATE()) - 2
and im.status = 'P' and cust_code <> '80990000'
GROUP BY cust_code
ORDER BY cust_code';
--Add IF statement to verify your query is returning at least 1 record
--this way you don't send an empty file
IF (MyQueryHasRecords) --This is psuedocode
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = @profile, @recipients = @emails, @body = 'My message',
@query = @MySQLCmd,
@subject = 'My Subject',
@attach_query_result_as_file = 1,
@query_attachment_filename = @filename;
END
GO
July 15, 2015 at 9:16 am
Brandie,
Thank you for that. I will have to try to set up DBmail first. I don't believe we have that set yet. A lot of reports and just run and copy and paste which is why I am trying to get things automated.
Side question: Is it possible to run the original SP and just have the results dumped into a network folder as a .csv or excel instead of emailing it out? This would most likely be a faster solution then trying to set up DBMail.
July 15, 2015 at 10:47 am
jonathanm 4432 (7/15/2015)
Side question: Is it possible to run the original SP and just have the results dumped into a network folder as a .csv or excel instead of emailing it out? This would most likely be a faster solution then trying to set up DBMail.
Yes. There are multiple solutions. I would advise using SSIS. But you could shortcut the learning curve by using the Import/Export Wizard with a query source and then save the package somewhere so it can later be used in a job.
July 15, 2015 at 1:42 pm
jonathanm 4432 (7/15/2015)
Brandie,Thank you for that. I will have to try to set up DBmail first. I don't believe we have that set yet. A lot of reports and just run and copy and paste which is why I am trying to get things automated.
Side question: Is it possible to run the original SP and just have the results dumped into a network folder as a .csv or excel instead of emailing it out? This would most likely be a faster solution then trying to set up DBMail.
If you happen to have the Enterprise version of SQL Server, Report Services has a neat feature called data-driven subscriptions, where you can set up a query that returns a list of recipients, and then e-mail the same report to each and every one (it will need to be able to connect to an e-mail server (probably SMTP-based)), or even customize the report to each recipient. This might be overkill, but it is fairly easy to set up once the e-mail server is available.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply