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)
 Loss Triangle - Starting Point
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

NickC
Yak Posting Veteran

United Kingdom
68 Posts

Posted - 01/10/2012 :  12:03:59  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/10/2012 :  12:20:31  Show Profile  Reply with Quote
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/

Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/10/2012 :  12:27:17  Show Profile  Reply with Quote
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/


Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1782 Posts

Posted - 01/10/2012 :  15:42:40  Show Profile  Reply with Quote
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

Edited by - Bustaz Kool on 01/10/2012 15:43:32
Go to Top of Page

NickC
Yak Posting Veteran

United Kingdom
68 Posts

Posted - 01/11/2012 :  04:29:56  Show Profile  Reply with Quote
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! :(
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 01/11/2012 :  05:54:11  Show Profile  Reply with Quote
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/

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.09 seconds. Powered By: Snitz Forums 2000