Get data from last 3 months

Question

Post reply

Get data from last 3 months

Marcus Farrugia

SSCarpal Tunnel

Points: 4718
More actions
January 4, 2010 at 8:31 am

#140535

Hi, I wrote a script for a report back in July looking for values in a table
going back three months. The report is coming up with blank values and I suspect that it's due to the new year. Here is the block of sql where I think it's failing, and I'm having a block myself trying to correct this. Basically, I want to get data from the last 3 previous months.
Thanks in advance for any help,
and
datepart(mm, DueDate)
between
datepart(mm, dateadd(mm, -3, getdate()))
and
datepart(mm, getdate())

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 1

What's happening with this query is that because it's a new year

the month value has reset to 1 so what i'm asking for is all data

with month part values between 10 and 1. i know this doesn't make sense and i'm not getting and data. How do I get a rolling 3 month dataset for jan, feb, march?

Thanks again.

LutzM SSC Guru Points: 107049 More actions · Answer 2

There are a couple issues with the way your query is written:

1) the query will not benefit from any index since you applied a function to the column you query against: datepart(mm, DueDate). This will slow down performance.

2) As you already found, you compare the number of a given month, causing the effect you described.

3) As long as you don't have data in there larger than today, it doesn't make much sens to use the BETWEEN clause.

What you could use is

WHERE DueDate >= dateadd(mm,datediff(mm,0,getdate())-3,0)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Marcus Farrugia SSCarpal Tunnel Points: 4718 More actions · Answer 3

Thanks lmu92...I appreciate the detailed explanation. I will have to research more regarding indexing. I've heard the term before but don't really understand the concept.

LutzM SSC Guru Points: 107049 More actions · Answer 4

Basically, to explain the concept of an index the easiest way I've seen so far is to compare an indexed table with a phone book, where the table index equals the phone book structure: It's very fast to find a phone number if you know the city and the last name. But other than the phone book you can add multiple indexes to a table. To stay with the picture: you could add an index on phone numbers. So you don't have to read the whole book if you'd like to find the person that belongs to a known phone number.

Once you figured the basics about indexing one great resource ist this link [/url] (GilaMonsters index blog).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Also, when using BETWEEN you should know that the first value must be less than or equal to the second.

select

*

from

(select 0 as Num union all

select 1 union all

select 2 union all

select 3 union all

select 4) dt

where

dt.Num between 1 and 3;

returns 1, 2, 3; while