Loss Triangle - Starting Point

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)

Loss Triangle - Starting Point

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-01-10 : 12:03:59

Hello

I am trying to develop a loss triangle report which is insurance specific, I've asked a few times but had a few replies. JimF kindly helped me although I'm not sure how I'd fit it into my data, or even how to use it (I'm new to this)

This was Jims reply which gave me an idea.....
------------
I use this to generate all the AYQ and CYQ combinations. You then left join this to your data table.

DECLARE @totalQtrs int
DECLARE @StartDate datetime
DECLARE @AsOfDate datetime

SET @StartDate = '20010101'
SET @AsOfDate = '20111231'
SET @totalQtrs = datediff(q,@startdate,@asofdate)

SELECT *
FROM
(
select [AYQ] = number,a.CYQ
from master..spt_values
CROSS JOIN
(select [CYQ] = number from master..spt_values where type = 'P' and number <= @totalQtrs) a
where type = 'P' and number <= @totalQtrs
and a.CYQ + number <= @totalQtrs
) cal

Jim

Everyday I learn something that somebody else already knew

---

My idea is that I create a table similar to Jims which shows all possible dates up to X date ( I dont mind what date just after 2015 or something I guess)

but Id need it to look like this (this only goes up to 2011)....

I'd like it f theres a way to automatically produce this similar to Jims query - if not I'll do it manually, my plan is then to left join data on to that using the rank value and accounting year - any help would be awesome :)

Accounting Period Quarter Rank
2008 Q1 2008 1
Q2 2008 2
Q3 2008 3
Q4 2008 4
Q1 2009 4
Q2 2009 5
Q3 2009 6
Q4 2009 7
Q1 2010 8
Q2 2010 9
Q3 2010 10
Q4 2010 11
Q1 2011 12
2009 Q1 2009 1
Q2 2009 2
Q3 2009 3
Q4 2009 4
Q1 2010 5
Q2 2010 6
Q3 2010 7
Q4 2010 8
Q1 2011 9
2010 Q1 2010 1
Q2 2010 2
Q3 2010 3
Q4 2010 4
Q1 2011 5
2011 Q1 2011 1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-10 : 12:20:31

this is one way to generate all days between two dates automatically

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-01-10 : 12:27:17

what the hell is a Los Triangle?

2 shots of tequila and a cervaza?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2012-01-10 : 15:42:40

quote:
what the hell is a Los Triangle?

2 shots of tequila and a cervaza?

That's a Lost Fin de Semana!

=================================================
Men shout to avoid listening to one another. -Miguel de Unamuno

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-01-11 : 04:29:56

Visakh, Will it generate it as above though? Its not so much dates I need more of the triangle of quarters as shown above - although formatting is off so I'll try again

AccPrd Quarter RANK

2008 Q1 2008 1
2008 Q2 2008 2
2008 Q3 2008 3
2008 Q4 2008 4
2008 Q1 2009 5
2008 Q2 2009 6
2008 Q3 2009 7
2008 Q4 2009 8
2008 Q1 2010 9
2008 Q2 2010 10
2008 Q3 2010 11
2008 Q4 2010 12
2008 Q1 2011 13
2008 Q2 2011 14
2008 Q3 2011 15
2008 Q4 2011 16
2008 Q1 2012 17

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

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

2011 Q1 2011 1
2011 Q2 2011 2
2011 Q3 2011 3
2011 Q4 2011 4
2011 Q1 2012 5

2012 Q1 2012 1

I want to keep it flat, but if you were to have the accouing period as columsn you'd see it looking like a triangle hence why its called loss triangle

essentially you have a claim come in for an acconting period and costs can be incurred in any quarter for that claim.

This example goes up to this quarter but as stated before I need it generated for however long I can.

Visakh I think you approach is on the right way of what I want to achieve

X002548, I think I need some tequilla after being given this... never before have I found nothing on the internet regarding this for something which is "used everywhere in insurance and if you dont know this you dont know MI" - quote my manager! :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-11 : 05:54:11

you just need to apply date functions like YEAR,MONTH(),DATEPART() etc to get relevant parts for date in the function i gave

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources