SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 Trailing Twelve Month
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

maunishq
Yak Posting Veteran

Canada
66 Posts

Posted - 01/13/2014 :  01:08:42  Show Profile  Reply with Quote
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

India
52249 Posts

Posted - 01/13/2014 :  05:58:47  Show Profile  Reply with Quote
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
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
66 Posts

Posted - 01/13/2014 :  23:49:49  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17440 Posts

Posted - 01/14/2014 :  02:12:08  Show Profile  Reply with Quote
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))



KH
Time is always against us

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/14/2014 :  04:35:53  Show Profile  Reply with Quote
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
Go to Top of Page

maunishq
Yak Posting Veteran

Canada
66 Posts

Posted - 01/19/2014 :  02:51:06  Show Profile  Reply with Quote
Thank you guys for the help.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 01/19/2014 :  02:52:18  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000