12 month rolling data - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

12 month rolling data

Author

Topic

sql1983
Starting Member

3 Posts

Posted - 2007-10-03 : 15:31:33

Hi All,
I wanna pull a 12 month rolling data and donno how to do that, can anybody help ?
Problem:
lets say if i run the query today i should get data between oct-3-2006 and oct-3-2007, my main colums is monthyear which is date datatype.
Appreciate your help.
Thanks
Chinna

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-03 : 15:37:46

moved from article discussion

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-10-03 : 15:41:35

Congrats sql1983. Over 5 minutes have past since your double post with out anyone saying anything about it. I think that's a new site record for SQLTeam. Good job...way to sneak one by. hehe

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-03 : 17:14:54

select * from table where column beetween 'oct-3-2006' and 'oct-3-2007'

=============================
http://www.sqlserverstudy.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-10-03 : 19:44:33

[code]SELECT *
FROM yourtable
WHERE monthyear >= DATEADD(MONTH, -12, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
AND monthyear < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 01:28:42

quote:
Originally posted by funketekun

select * from table where column beetween 'oct-3-2006' and 'oct-3-2007'

=============================
http://www.sqlserverstudy.com

You should always express dates in YYYYMMDD format

Run this and see what you get

select cast('oct-3-2006' as datetime)

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 01:31:47

quote:

Originally posted by khtan

SELECT *
FROM   yourtable
WHERE  monthyear >= DATEADD(MONTH, -12, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))
AND    monthyear <  DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

KH
[spoiler]Time is always against us[/spoiler]

or

Where monthyear >= DATEADD(MONTH, -12, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0))

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 01:55:01

SELECT * FROM YourTable WHERE MonthYear >= DATEADD(YEAR, -1, CURRENT_TIMESTAMP)

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 05:23:57

quote:
Originally posted by Peso

SELECT * FROM YourTable WHERE MonthYear >= DATEADD(YEAR, -1, CURRENT_TIMESTAMP)

E 12°55'05.25"
N 56°04'39.16"

It will work as OP used date datatype and will not work as expected if datatype is datetime

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 05:26:33

He wanted rolling last 12 months?
From todays' date and 12 months back? I think he wrote that in OP.

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-04 : 05:38:28

quote:
Originally posted by Peso

He wanted rolling last 12 months?
From todays' date and 12 months back? I think he wrote that in OP.

E 12°55'05.25"
N 56°04'39.16"

Yes it is. OP also specified date datatype is used. If datetime datatype is used, then your query may omit some records from the beginning date as it has time part also

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources