SQL Server Agent - Run Query at the end of each month with date variable

Question

I am running SQL Server 2008 R2, and I need to create a new job that will basically run a query at the start of each month (at the 1st of each month at 01:00 AM).

Here is the query:

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]
SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice]
WHERE 
     bg_reported_date between '2012-12-01 00:00:00.000' AND '2012-12-31 23:59:59.999' 
     AND bg_id NOT IN (SELECT bg_id FROM [SupportTracker].[dbo].[DashboardRecords])
ORDER BY bg_reported_date ASC

My problem is at the WHERE clause. These two dates have to change every month.

• If we are on the 01-01-2013 01:00:00:00, I need these 2 dates to be:
  2012-12-01 00:00:00.000 and 2012-12-31 23:59:59.999

• If we are on the 01-02-2013 01:00:00:00, I need these 2 dates to be:
  2013-01-01 00:00:00.000 and 2013-01-31 23:59:59.999.

Basically I need to capture parts of the database and save it in the table, as it was at the end of each month...

Thanks

Answer 1

You don't want to use BETWEEN to satisfy this query for the reasons I outline in this blog post:

http://sqlblog.com/blogs/aaron_bertrand/archive/2011/10/19/what-do-between-and-the-devil-have-in-common.aspx

Instead you want an open-ended date range. Begins on the first of the month in question, and is less than the next month. To determine the first of the month given any date, you can do this:

SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0);

And to get first of the the following month, just add one:

SELECT DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, GETDATE()), 0);

Of course if you want last month only on the first of the current month, and this month after the first, then you can say:

SELECT DATEADD(MONTH, 1+DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE())), 0);

So for your query, if you always want it relative to the current month:

...
FROM SupportTracker.dbo.ViewIssueListwBugTypeNDevice AS v
WHERE NOT EXISTS 
(
  SELECT 1 FROM SupportTracker.dbo.DashboardRecords
    WHERE bg_id = v.bg_id
)
AND v.bg_reported_date >= DATEADD(MONTH, 0, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE()))
AND v.bg_reported_date < DATEADD(MONTH, 1, DATEDIFF(MONTH, 0, DATEADD(DAY, -1, GETDATE()));

Or if you want to take a date as an optional parameter, you don't need to subtract a day, just pass in any date within the month you actually want (and if you pass in NULL, it will still fall back to the above formula):

  @Month DATE = NULL
...

  SET @Month = DATEADD(MONTH, 0, DATEDIFF(MONTH, 0, COALESCE(@Month, 
    DATEADD(DAY, -1, GETDATE())));  

...
FROM SupportTracker.dbo.ViewIssueListwBugTypeNDevice AS v
WHERE NOT EXISTS 
(
  SELECT 1 FROM SupportTracker.dbo.DashboardRecords
    WHERE bg_id = v.bg_id
)
AND v.bg_reported_date >= @Month
AND v.bg_reported_date < DATEADD(MONTH, 1, @Month);

Answer 2

To find the first and last days of the month:

DECLARE @MonthStart DATETIME
DECLARE @Date  DATETIME
DECLARE @next DATETIME 
-- create the required date. because today is the 21st i subtracted month. 
-- since you're running the job on the first day of the new month, 
-- you may want to change MONTH to DAY. however, if the job fails that change
-- that may cause you some problems
SET @Date = DATEADD(MONTH,-1,DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) 

-- get the first day of the month of the date specified
SET @MonthStart = DATEADD(DAY, 1, @Date - DAY(@Date) + 1) -1

-- get the first day of next month
SET @Next = DATEADD(MONTH,1,@MonthStart)

-- prove to yourself that these dates are correct (sanity check)
SELECT @MonthStart, @next

I've laid it out like this for you so that you can follow the logic, you can edit to your needs.

As an aside, the NOT IN convention is somewhat of an antipattern and some experts recommend against using BETWEEN.

LEFT JOIN

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]
SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice] b
LEFT JOIN [SupportTracker].[dbo].[DashboardRecords] d
    ON b.bg_id = d.bg_id
WHERE b.bg_reported_date >= @MonthStart and b.bg_reported_date < @Next
    AND d.[bg_short_desc] IS NULL

(this assumes [bg_short_desc] is a non-nullable field in the [DashboardRecords] table.

EVEN BETTER would be

NOT EXISTS

INSERT INTO [SupportTracker].[dbo].[DashboardRecords]

SELECT [bg_id]
      ,[bg_short_desc]
      ,[bg_reported_date]
      ,[bg_status_updated_date]
      ,[us_firstname]
      ,[us_lastname]
      ,[LastUpdatedUserFirstname]
      ,[LastUpdatedUserLastname]
      ,[st_name]
      ,[pr_name]
      ,[ct_name]
      ,[pj_name]
      ,[AssignedUserFirstname]
      ,[AssignedUserLastname]
      ,[bg_project]
      ,[no_of_hours]
      ,[BugType]
      ,[SubType]
      ,[Device]
      ,[pj_parent_id]
FROM [SupportTracker].[dbo].[ViewIssueListwBugTypeNDevice] b
WHERE b.bg_reported_date >= @MonthStart and b.bg_reported_date < @Next
    AND NOT EXISTS (SELECT 1 FROM [SupportTracker].[dbo].[DashboardRecords] d
        WHERE b.bg_id = d.bg_id);

Also, why are you ordering your inserts? Ordering would be more useful when you're selecting from the DashboardRecords table, right?