Selecting the last 12 months dynamically

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Selecting the last 12 months dynamically

Author

Topic

craigwg
Posting Yak Master

154 Posts

Posted - 2010-04-16 : 16:28:23

Hi,

I am writing a stored procedure that is selecting some financial data. I am loading the data into a temp table that has two columns: "date" and "value".

The proc will look at all the data and pull the last 12 months. I can statically make that happen, but how do I do it so it detects the current month and only pulls the last 12?

Here is a sample of my data set:


date                    value
2010-12-01 00:00:00.000	Dec12|88237
2010-11-01 00:00:00.000	Nov11|89673
2010-10-01 00:00:00.000	Oct10|91656
2010-09-01 00:00:00.000	Sep09|87678
2010-08-01 00:00:00.000	Aug08|80548
2010-07-01 00:00:00.000	Jul07|83059
2010-06-01 00:00:00.000	Jun06|81143
2010-05-01 00:00:00.000	May05|77977
2010-04-01 00:00:00.000	Apr04|74494
2010-03-01 00:00:00.000	Mar03|76018
2010-02-01 00:00:00.000	Feb02|66660
2010-01-01 00:00:00.000	Jan01|68007
2009-12-01 00:00:00.000	Dec12|66600
2009-11-01 00:00:00.000	Nov11|71573
2009-10-01 00:00:00.000	Oct10|73617
2009-09-01 00:00:00.000	Sep09|72126
2009-08-01 00:00:00.000	Aug08|65799
2009-07-01 00:00:00.000	Jul07|71870
2009-06-01 00:00:00.000	Jun06|79905
2009-05-01 00:00:00.000	May05|69031
2009-04-01 00:00:00.000	Apr04|70899
2009-03-01 00:00:00.000	Mar03|81318
2009-02-01 00:00:00.000	Feb02|70820
2009-01-01 00:00:00.000	Jan01|76388

Craig Greenwood

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2010-04-16 : 16:54:25

Look up the dateadd function in BOL. Fairly simple but if you need more details, provide us with what you've tried to accomplish your results and we'll be glad to help further.

Terry

-- The problem with socialism is that you eventually run out of other people’s money. -- Margaret Thatcher

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 04:37:53

have a look at function below as an example

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

killtacularmania
Starting Member

22 Posts

Posted - 2010-04-19 : 10:13:41

this using just a where clause

Select * From FinancialTable
Where financialdate > CAST(CAST(YEAR(dateadd(month,-12,financialdate)) As varchar(4)) + '/' + CAST(MONTH(dateadd(month,-12,finanicaldate)) As Varchar(2)) + '/01' As Datetime)

I use this all the time, it puts together the current month and 01 as the day to give you the start of each month and then it will subtract 12 months.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-19 : 10:50:02

quote:
Originally posted by killtacularmania

this using just a where clause

Select * From FinancialTable
Where financialdate > CAST(CAST(YEAR(dateadd(month,-12,financialdate)) As varchar(4)) + '/' + CAST(MONTH(dateadd(month,-12,finanicaldate)) As Varchar(2)) + '/01' As Datetime)

I use this all the time, it puts together the current month and 01 as the day to give you the start of each month and then it will subtract 12 months.

no need of those convertions.you could simply do

Where financialdate >dateadd(mm,datediff(mm,0,financialdate)-12,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources