x

Query to select records for a previous month

I need to write a report that lists all employee expenses for the previous month. The report will be scheduled to run on the first of the month. I am using the following query. It works except expenses for December. When report will kick off on January 1, it will be blank. December expenses will be skipped

 SELECT  
  employeeid 
 ,employeeName
 , finalApproval
 
 FROM dbo.ExpenseExport
 WHERE 
     DATEPART(month, expensedate)  = (DATEPART(month, GETDATE()) - 1)            
     DATEPART(year, expensedate l) = DATEPART(year, DATEADD(m, -1GETDATE()))


What do I need to do to get December expnses comes January 1.

more ▼

asked Mar 19, 2012 at 05:25 PM in Default

avatar image

ileshak
0 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

It looks like it would be safer to use a couple of variables in your report, one of them equal to the 1st of previous month and the other equal to the 3 milliseconds before the 1st of current month. This way you will capture all records which have expensedate any time last month and you will not depend on any datepart which leads to the problem when you run your report on January 1st because the year parts do not match. Here is the script:

 declare @startDate datetime; -- 1st day of previous month
 declare @endDate datetime; -- 3 milliseconds before current month starts
       
 select
     @startDate = dateadd(month, datediff(month, 0, getdate()) - 1, 0),
     @endDate = dateadd(millisecond, -3, dateadd(month, 1, @startdate));
 
 -- your report query
 select
     employeeid, employeeName, finalApproval
     from dbo.ExpenseExport 
     where expensedate between @startDate and @endDate;

You can test this script for January 1st report by replacing the getdate() with the datetime value corresponding to any day in January of this year, such as January 1st. If you replace the desired datetime value using single quotes then 'YYYYMMDD' format is the best one to use because it does not depend on your servers dateformat settings. So for January 1st of this year you could use '20120101'. Here is the test showing that the values are correct:

 declare @startDate datetime; -- 1st day of previous month
 declare @endDate datetime; -- 3 milliseconds before current month starts
       
 select
     @startDate = dateadd(month, datediff(month, 0, '20120101') - 1, 0),
     @endDate = dateadd(millisecond, -3, dateadd(month, 1, @startdate));
     
 select @startDate StartDate, @endDate EndDate;
 
 -- the above script returns:
 StartDate               EndDate
 ----------------------- -----------------------
 2011-12-01 00:00:00.000 2011-12-31 23:59:59.997

Oleg

more ▼

answered Mar 19, 2012 at 05:42 PM

avatar image

Oleg
17.6k 3 7 28

(comments are locked)
10|1200 characters needed characters left

It may also help to use a calculation that gets a full date based on the current date.

SELECT CONVERT(DATETIME, DATEDIFF(d, 0, DATEADD(dd, - (DAY(DATEADD(mm, 1, GETDATE()))-1), DATEADD(mm, -1, GETDATE())))) LastFirst, CONVERT(DATETIME, DATEDIFF(d, 0, DATEADD(dd, - (DAY(DATEADD(mm, 1, GETDATE()))-1), DATEADD(mm, 0, GETDATE())))) ThisFirst

This will give you the first of last month and the first of this month.

more ▼

answered Mar 19, 2012 at 06:20 PM

avatar image

dvroman
1.1k 1 4 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x417

asked: Mar 19, 2012 at 05:25 PM

Seen: 5608 times

Last Updated: Mar 19, 2012 at 06:20 PM

Copyright 2016 Redgate Software. Privacy Policy