Insurance Triangulation Report

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)

Insurance Triangulation Report

Author

Topic

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-12-29 : 11:43:51

Hello

I am writing a query for a triangulation report.

Triangultion reports look similar to this, basically the values are premium and they are aggregated from the previous quarter for that Accounting Yr (AY). 2008 starts from Q1 as its the first Accounting Year we have, then 2009 starts from Q5 as its the second year and 2009 would start from Q9 and so fourth

AY Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10

2008 10 20 30 30 40 40 50 60 70 90

2009 10 30 50 80 90 100

2010 5 10

I have the accounting year in a table along with the accounting period they related to so you might get a premium for 2008 in Q2 2009 etc.

I managed to get the Quarter rankings using this line which someone kindly helped me with on this forum. Rank Value is jus a number assigned to the quarter so Q5 would be 5

Rank Value =DATEDIFF(q,gsc.AccountingYear+'01'+'01',Convert(Datetime,gsc.AccountingPeriod+'01',112))

The problem I have though is there isn't always a premium for each Quarter so for example in 2008 Q6 there might not be a premium, my report at the moment is coming up as 0 however all I want ti to do is take the previous amount and put it in there so at moment my report looks like

AY Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10

2008 10 20 30 30 40 0 50 60 70 90

2009 10 30 50 80 90 100

2010 5 10

but I need it to look like

AY Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10

2008 10 20 30 30 40 40 50 60 70 90

2009 10 30 50 80 90 100

2010 5 10

The running total has been calculated using this NetSignedRunningTotal = (SELECT SUM(NetSignedGBP) FROM #RunningTotal WHERE AccountingYear = rt.AccountingYear AND RankValue <= rt.RankValue)

I'm not sure how I can get it to work as it should - if anyone has any ideas it would be greatly appreciated.

No worries if not and if you need any more info plz let me know :)

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-12-29 : 12:01:36

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

NickC
Yak Posting Veteran

68 Posts

Posted - 2011-12-30 : 04:11:31

Hi Jim

I am still new so struggling

so I have my data table

and it has

Accounting Year ,Accounting Period, Display Quarter (e.g. Q4 2011), QuarterKey (e.g. 20114), Premium.

I've then used DateDiff statement to take Accounting Year, and Accounting Period to create a triangulation quarter as shown above, what would I join your table onto? the triangulation quarter?

Sorry real newbie!

Also will this fix the running total issue I have where if a premium hasn't been posted for a quarter it will jus take the previous quarters running total rather than 0?

Thanks

NickC
Yak Posting Veteran

68 Posts

Posted - 2012-01-06 : 10:37:55

Anyone able to help ? sorry for bumping - hope thats ok :/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources