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.
| Author |
Topic |
|
NickC
Yak Posting Veteran
68 Posts |
 Posted - 2013-05-16 : 09:18:48
|
| 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 etcit gets complicated though as triangle months dont stop at 12, they keep going so11,12,13 will be q1, 14,15,16 will be q2 etcIn 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-16 : 11:13:10
|
Just do an integer division and take modulus, as in((TriangleMonth+1)/3)%3 as Quarter |
 |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-05-16 : 12:14:45
|
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. |
|
|
|
|
|
|
|
|
Calculating Development Quarters