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 2008 Forums
 Transact-SQL (2008)
 Calculating Development Quarters
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NickC
Yak Posting Veteran

United Kingdom
68 Posts

Posted - 05/16/2013 :  09:18:48  Show Profile  Reply with Quote
I've been askde for the actuaries to change my triangle quarters

so instead of 1,2,3 beign Q1 it will be 2,3,4 being QI, 5,6,7 being Q2 8,9,10, BEING Q3 etc

it gets complicated though as triangle months dont stop at 12, they keep going so

11,12,13 will be q1, 14,15,16 will be q2 etc

In excel they use CEILING(TriangleMonth/3,1) but SQL doesnt have a significance step for its Ceiling function.

I cant seem to work out what maths I can use, if it was just 1-12 I'd use a case statement but thats not possible.

any ideas? :/ I've googled ceiling function and tride peoples ideas of creating a function to do it, but it doesnt seem to give same result as excel.

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 05/16/2013 :  11:13:10  Show Profile  Reply with Quote
Just do an integer division and take modulus, as in
((TriangleMonth+1)/3)%3 as Quarter
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3744 Posts

Posted - 05/16/2013 :  12:14:45  Show Profile  Reply with Quote
As I was eating lunch and thinking about it (yes, I do think about SQL while eating lunch :) I realized what I posted abover is not right - it returns 0 instead of 3 for the 3rd group. So change it to
1+((TriangleMonth-2)/3)%3
I aliased them to Quarter in my previous query. Of course, they are not quarters in the conventional sense, per your description of it - I hope that is what you want.
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.03 seconds. Powered By: Snitz Forums 2000