Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Dynamic parameters in data driven subscription Expand / Collapse
Author
Message
Posted Thursday, January 2, 2014 12:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:13 AM
Points: 26, Visits: 41
I have a requirement that I want to make monthly subscription where for each month a mail should be sent to the recipients
taking the last month as a parameter.

For eg:If I create subscription on 2nd of every month and the current month is jan. Mail should be sent for the month of Dec which is previous month. Like that this has to be repeated for every month.


Please help me with the SQL syntax..
Post #1526985
Posted Friday, January 3, 2014 2:27 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, June 3, 2014 4:20 AM
Points: 69, Visits: 363
Hi
[Code = "sql"]
DECLARE @dayofmonth int
DECLARE @dateprmonth datetime

/* stores one day before day of the month */
SET @dayofmonth = datepart( dd,dateadd (mm,-1 ,getdate()) ) -1

/* stores the previous month date from current date*/
SET @dateprmonth = dateadd(m,-1,getdate())

SELECT dateAdd(dd,0,DateDiff(dd,0,@dateprmonth-@dayofmonth)) AS [FIRST_DATE_OF_PREVIOUS_MONTH],
dateAdd(dd,0,DateDiff(dd,0,dateAdd(MM,1,(@dateprmonth-@dayofmonth )))) -1 AS [LAST_DATE_OF_PREVIOUS_MONTH]

[/Code]

above script to get first and last day of the previous month, if you run your script for between those two dates and set up a report to report run on 2nd of every month.

done and dusted
Post #1527397
Posted Friday, January 3, 2014 4:18 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 23, 2014 3:13 AM
Points: 26, Visits: 41
Thank you very much
Post #1527446
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse