SQL for obtaining data any previous month

Question

Hi im still fairly new to SQL script and i have written some below to fetch what i hope will be ALL of the previous months data when run at any given date, regardless of the number of days in a month

The table date format is 2013-05-10 00:00:00.000

im just after an expert eye to look at the script and advise if i have got it right or wrong and where if i have please:

SELECT 
[Circuit Number]
,[Request Number]
,[RequestDate]
FROM [QuoteBase].[dbo].[Requests]
WHERE RequestType LIKE 'Cancel%' 
AND DATEPART(mm, [RequestDate]) = DATEPART(mm, DATEADD(mm, -1, getdate()))
AND DATEPART(yyyy, [RequestDate]) = DATEPART(yyyy, DATEADD(mm, -1, getdate()))

Since the dates are converted to dateparts, it will not be able to use any indexes, resulting in a full table scan. — andersh, Jun 28 '13 at 10:41

score 1 · Answer 1 · edited May 23 '17 at 10:32

SELECT 
    [Circuit Number]
   ,[Request Number]
   ,[RequestDate]
FROM
   [QuoteBase].[dbo].[Requests]
WHERE
   RequestType LIKE 'Cancel%' 
   AND
   [RequestDate] >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0);  --year
   AND
   [RequestDate] >= DATEADD(mm, DATEDIFF(mm, 32, GETDATE()), 0);   -- start last month
   AND
   [RequestDate] < DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0);   -- start this month

This uses the technique fronhere to find start of month: Need to calculate by rounded time or date figure in sql server

Also, don't use function on predicates. See number 2 here: http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/

In this case, an index like this will be useful

CREATE INDEX IX_Whatever ON
         QuoteBase (RequestDate, RequestType)
            INCLUDE ([Circuit Number], [Request Number])

Edper · Accepted Answer · 2013-06-28T12:36:08.610

1

I am no SQL expert but try this:

SELECT [Circuit Number]
,[Request Number]
,[RequestDate]
FROM Requests
WHERE RequestType LIKE 'Cancel%'
AND DATEDIFF(mm,Requestdate , GETDATE())=1
AND DATEDIFF(yy,Requestdate, GETDATE())=0

See my Fiddle Demo

Explanation:

DATEDIFF(mm,Requestdate , GETDATE())=1 (Only Request previous Month of the Current Date)

Next condition:

DATEDIFF(yy,Requestdate, GETDATE())=0 (Only in same year as Current Date)

However, if you want just the previous month regardless if it is the same year as Current Date or not then you could remove the second condition, like:

SELECT [Circuit Number]
,[Request Number]
,[RequestDate]
FROM Requests
WHERE RequestType LIKE 'Cancel%'
AND DATEDIFF(mm,Requestdate , GETDATE())=1

See the Demo for this.

For example if the CurrentDate is 2013-01-19 then any December 2012 request will be included despite that it is not of the same year but is obviously of previous month.

edited Jun 28 '13 at 12:36

answered Jun 28 '13 at 11:24

Edper

9,144
1
27
46

yes i believe that is what i want IE Always the previous months data whether we are in january (we get Decmbers data from the previous year) or july (we get Junes data). – MoiD101 Jun 28 '13 at 12:32
@MoiD101 then try my 2nd query. – Edper Jun 28 '13 at 12:33
With a predicate on a function, no index can be used. See my link about SQL programming errors – gbn Jun 28 '13 at 12:51
@gbn thank you for your comment and the reference sir, it's worth the read. But I think the OP just wants a simple and straightforward solution that works for him. – Edper Jun 28 '13 at 13:55

SQL for obtaining data any previous month

2 Answers2

Linked