Trailing Twelve Month - 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 2012 Forums

Analysis Server and Reporting Services (2012)

Trailing Twelve Month

Author

Topic

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-01-13 : 01:08:42

Hi,
Need help in generating report for Trailing Twelve Month (TTM)of data.
Any help with SQL queries would be useful. (In detail please).
Any links if known would also be helpful.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-13 : 05:58:47

without seeing how your tables are etc its hard to suggest complete solution
In general you need a filter like below


WHERE Datefield > = DATEADD(mm,DATEDIFF(mm,0,GETDATE())-12,0)
AND Datefield < DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)

this will give last 12 months data without including current month

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-01-13 : 23:49:49

Thank you for the reply.
So how about if I want to add a parameter for year and a month and then when someone selects the month and year, and then it retrieves the past 12 months of data.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-01-14 : 02:12:08

[code]declare @year int,
@month int

select @year = 2014,
@month = 1

select dateadd(month, @month - 13, dateadd(year, @year - 1900, 0)),
dateadd(month, @month - 1, dateadd(year, @year - 1900, 0))[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-14 : 04:35:53

quote:
Originally posted by maunishq

Thank you for the reply.
So how about if I want to add a parameter for year and a month and then when someone selects the month and year, and then it retrieves the past 12 months of data.


WHERE Datefield > = DATEADD(mm,(@Year-1900)*12+@Month-13,0)
AND Datefield < DATEADD(mm,(@Year-1900)*12+@Month-1,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

maunishq
Yak Posting Veteran

71 Posts

Posted - 2014-01-19 : 02:51:06

Thank you guys for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-19 : 02:52:18

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources