CODEWITHSUNDEEP
sqlsql-serversql-server-2008
MoiD101
MoiD101

Reputation: 195

SQL for obtaining data any previous month

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()))

Upvotes: 1

Views: 4990

Answers (2)

Edper
Edper

Reputation: 9322

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.

Upvotes: 1

gbn
gbn

Reputation: 432180

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])

Upvotes: 1

Related Questions