I work on a Data Warehouse team for an insurance company.  I want to
create what's called a "Loss Triange" using SQL.  I'd like to find
some sample SQL code for creating these triangles.   I'd like to
include measures such as "Claim Counts" per policy year, "Medical
Paid", "Indemnity Paid", and other financial measures.  Any way you
can help find some samples?

---

Request for Question Clarification by hammer-ga on 18 Dec 2002 11:38 PST

A loss triangle is simply a kind of crosstab query. Are you looking
for general information regarding how to create a crosstab query using
SQL? If so, we'll need to know which database program you are using.

---

Clarification of Question by shipman-ga on 18 Dec 2002 12:43 PST

Hi.  Yes.  A Loss Triangle is simply a cross tab, but I'm
unfortunately not as skilled as I'd like to be at creating this.  I've
tried several ways, but, as of yet, haven't come up with anything.

We are using Oracle 8.

Here's a Rough view of how I would be pulling the data -(our table
structure)

Claim Table
-----------
Claim #
Date Claim Open
CLaim Status
Policy Number

Policy 
------
Policy Number
Start Date, End Date


Payments
--------
Claim Num
Payment Amount
Payment Type. 


So, when I have the triangle done, I'd like to be able to 

         Year 1        Year 2    Year 3   Year 4........
1995
1996
1997
1998
1999
2000

In my Crosstabs, I'll have values like:  Claims, Open Claims, Closed
Claims, Med Paid, Total Paid, etc etc.
Maybe we could start with a rough clarification of how to do Claims
and then All Payments.

---

Request for Question Clarification by hammer-ga on 18 Dec 2002 13:02 PST

It looks like you have the Year as both your rows and your columns.
Normally, a loss triangle would look more like this:

          Claims     Open Claims     Closed Claims
1995
1996
1997
1998
1999
2000

Could you confirm that what I show above is what you have in mind?

---

Clarification of Question by shipman-ga on 18 Dec 2002 13:50 PST

Actually, we might be talking about different things then.  I'm used
to a loss triangle looking like this:
           Year1    YEar2    Year3   
1999         25       30       30      
2000         20       22       
2001         10        
2002          

Where the left column reflects the Policy years.  The "Years" columns
reflect the claims opened during the policy year (Year 1), the policy
year + 1 (Year 2) , the policy year + 2 (Year3)
That's how you get the get the triangle shape 
In this case, 1999 is our starting point.  In 99, 25 claims were
opened that fell in the 1999 policy year.  In 2000 (Year 2), 5 more
claim were opened that actually fall into the 99 policy year (i.e.,
the accident/incident was in 99 but not reported until after the end
of the policy year.  By year 3, (2001), there were no new claims being
reported for that year.
This is perhaps better explained with Dollars.  We might pay $100,000
for 1999 claims during 1999, but one might pay another $150,000 for
1999 claims in 2000 (perhaps it was a lawsuit that's been settled in
2000), then we might pay $50,000 more in 2001.

Here's an example of dollars:  
           Year1 (1999)Year2 (2000)Year3 (2001)
1999      100,000      250,000     300,0000
2000      125,000      225,000     0
2001      100,000      0
2002      0 (year hasn't ended yet)

DOes this make more sense?

---

Request for Question Clarification by hammer-ga on 19 Dec 2002 04:17 PST

OK, I see where you're going now. I'll see if I can find an example of
how Oracle SQL creates a crosstab.

---

Request for Question Clarification by hammer-ga on 19 Dec 2002 05:52 PST

Shipman,

My apologies, but I'm going to have to back off this question. I don't
own a copy of Oracle and the building of crosstab queries in SQL is
complex enough that I'd be uncomfortable posting an answer that I
can't test for accuracy. Hopefully, one of the Oracle-enabled
Researchers will pick this up for you.

- Hammer

---

Clarification of Question by shipman-ga on 19 Dec 2002 07:01 PST

Ok.  Fair enough.  
Thanks for trying.