RANK Function and Offsetting the value

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 2008 Forums

Transact-SQL (2008)

RANK Function and Offsetting the value

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-29 : 11:00:50

Hello

I am writing a query in which I run the following line -

DENSE_RANK () OVER(Partition By rh.AccountingYear ORDER BY d.Quarterkey ASC) END

This ranks by Accounting year, and provides a ranking for each quarter, this produces the following results:

2009 1 Q4 2009
2009 2 Q1 2010
2009 3 Q2 2010
2009 4 Q3 2010
2009 5 Q4 2010
2009 6 Q1 2011
2009 7 Q2 2011
2009 8 Q3 2011
2009 9 Q4 2011
2010 1 Q1 2010
2010 2 Q2 2010
2010 3 Q3 2010
2010 4 Q4 2010
2010 5 Q1 2011
2010 6 Q2 2011
2010 7 Q3 2011
2010 8 Q4 2011
2011 1 Q1 2011
2011 2 Q2 2011
2011 3 Q3 2011
2011 4 Q4 2011

Now thats fine, for 2010, and 2011 however in 2009 we didn't start writing policies until the fourth quarter, so I need it to start from 4 for that quarter as its quarter 4 in 2009.

so the results should look like this

2009 4 Q4 2009
2009 5 Q1 2010
2009 6 Q2 2010
2009 7 Q3 2010
2009 8 Q4 2010
2009 9 Q1 2011
2009 10 Q2 2011
2009 11 Q3 2011
2009 12 Q4 2011
2010 1 Q1 2010
2010 2 Q2 2010
2010 3 Q3 2010
2010 4 Q4 2010
2010 5 Q1 2011
2010 6 Q2 2011
2010 7 Q3 2011
2010 8 Q4 2011
2011 1 Q1 2011
2011 2 Q2 2011
2011 3 Q3 2011
2011 4 Q4 2011

Simples, except it can't be hard coded as different risks started being writen at different times - so not so simple for me :(

Hoping its just a one line change or something , I did attempt a case statement after creating a left join to show min quarter however it failed miserably :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-29 : 11:33:41

[code]
SELECT Dr+(MAX(CASE WHEN DR=1 THEN REPLACE(QuarterKey,'Q','')*1 ELSE NULL END) OVER (PARTITION BY AccountingYear)-1) AS YourRankValue,...
FROM
(
SELECT
DENSE_RANK () OVER(Partition By rh.AccountingYear ORDER BY d.Quarterkey ASC) AS Dr,....
FROM...
...
)t
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-11-29 : 11:35:48

If it's not too late, I find triangle reports much easier to do if you just calculate how many calendar year periods away from the original accident year period you are, and then let the front end figure out what the headings should be. I do my triangles AYQxCYQ, and this way the front-end can turn it into AYYxCYY or AYYxCYQ or whatever.

Jim

Everyday I learn something that somebody else already knew

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-11-29 : 11:44:39

Hi Jim

This is first time I've ever done triangles, any guidence would be appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-11-29 : 12:13:12

Hopefully your underlying data has actual dates in it, an AccidentDate(when it happened) and an AccountingDate(the changes in value of the claim). You build out your triangles using a start date and an AsOf date. Your accident years quarters are determined by DATEDIFF(y,startdate,accidentdate) and your development periods are built using
DATEDIFF(q,accidentdate,accountingdate). So your table will look something like

ClaimNumber AccidentDate AccountingDate AYY CYQ
1 12/31/1999 12/31/1999 0 0
1 12/31/1999 03/31/2000 0 1
...
1 12/31/1999 10/31/2011 0 48

This should give you a good start. Once you get this you can worry about cumulating losses.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources