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 - 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 intDECLARE @StartDate datetimeDECLARE @AsOfDate datetimeSET @StartDate = '20010101'SET @AsOfDate = '20111231' SET @totalQtrs = datediff(q,@startdate,@asofdate)SELECT *FROM(select [AYQ] = number,a.CYQ from master..spt_valuesCROSS JOIN(select [CYQ] = number from master..spt_values where type = 'P' and number <= @totalQtrs) awhere type = 'P' and number <= @totalQtrs and a.CYQ + number <= @totalQtrs) calJimEveryday 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 Rank2008 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 122009 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 92010 Q1 2010 1 Q2 2010 2 Q3 2010 3 Q4 2010 4 Q1 2011 52011 Q1 2011 1 |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
X002548
Not Just a Number
15586 Posts |
|
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 againAccPrd Quarter RANK 2008 Q1 2008 12008 Q2 2008 22008 Q3 2008 32008 Q4 2008 42008 Q1 2009 52008 Q2 2009 62008 Q3 2009 72008 Q4 2009 82008 Q1 2010 92008 Q2 2010 102008 Q3 2010 112008 Q4 2010 122008 Q1 2011 132008 Q2 2011 142008 Q3 2011 152008 Q4 2011 16 2008 Q1 2012 172009 Q1 2009 12009 Q2 2009 22009 Q3 2009 32009 Q4 2009 42009 Q1 2010 52009 Q2 2010 62009 Q3 2010 72009 Q4 2010 82009 Q1 2011 92009 Q2 2011 102009 Q3 2011 112009 Q4 2011 12 2009 Q1 2012 132010 Q1 2010 12010 Q2 2010 22010 Q3 2010 32010 Q4 2010 42010 Q1 2011 52010 Q2 2011 62010 Q3 2011 72010 Q4 2011 8 2010 Q1 2012 92011 Q1 2011 12011 Q2 2011 22011 Q3 2011 32011 Q4 2011 4 2011 Q1 2012 52012 Q1 2012 1I 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 triangleessentially 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 achieveX002548, 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|