Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Create columns for last 5 claims 1

Status
Not open for further replies.

IanWaterman

Programmer
Jun 26, 2002
3,511
GB
I have a query which brings back the oldest claim from last 5 years

Code:
SELECT CP.CPI_CLIENT_REF, CPI_INCIDENT_DATE, 
  (CASE WHEN CPI_SETTLE_AMT < CPI_CLAIM_AMT  THEN CPI_CLAIM_AMT ELSE CPI_SETTLE_AMT END) CPI_CLAIM_AMT,
  CPI_STATUS, CPI_CLAIM_TYPE, CPI_DETAILS
  FROM CLMPREINCEPT CP
  INNER JOIN (
    SELECT CPI_CLIENT_REF, MIN(CPI_INCIDENT_DATE) FIRST_INCIDENT 
    FROM CLMPREINCEPT CM
    WHERE CPI_INCIDENT_DATE > SYSDATE-1825
    GROUP BY CPI_CLIENT_REF

They now want up to the last 5 claims in the last 5 years, there could of course be no claims or any number up to 5.

How do i modify the above query to produce 5 x 3 columns

Claim1Date, Claim1Amt, Claim1Type, Claim2Date....

Thank you

Ian
 
Ian,

I imagine output that would be rather difficult to read. Is the result set targetted for user display, or will the output go on to become input for some other system processing? Do you want some sort of field descriptors and group delimiters? Could you post a couple of lines of sample output that meets your need?

Thanks,

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”
 
Its for an Insurance Bordereau report.

I will use the out put for a Crystal report which in turn will generate an Excel output. And before you ask - No I can not go direct from SQL to Excel. The application only allows database access via Crystal Reports embedded within the application.

I need to generate a single dataset with a lot of other policy/premium information from other views and then combine into a single record within Crystal.

Claims data is a simple table

CPI_CLIENT_REF, CPI_INCIDENT_DATE, CPI_SETTLE_AMT, CPI_CLAIM_AMT, CPI_STATUS, CPI_CLAIM_TYPE, CPI_DETAILS

For a client there could be 0 or many claims in the last five years. So for a client it could be empty or have say 5 recordsets

Ineed to convert into a single recordsets per client with 15 columns, if a client only has 1 claim then the remaining columns would just be null.

I do not need to show CPI_Status or CPI-details, just need to show either settle_amt or claim_amt showing the largest value, filed is never null is populated with zero or a value.

Hope that is clear.

Ian






 
This is the sort of thing you that ranking functions were created for. You could do something like this:

Code:
SELECT
  CPI_CLIENT_REF, 
  CPI_INCIDENT_DATE, 
  CPI_CLAIM_AMT,
  CPI_STATUS, 
  CPI_CLAIM_TYPE, 
  CPI_DETAILS
FROM
(
SELECT CP.CPI_CLIENT_REF, CPI_INCIDENT_DATE, 
  (CASE WHEN CPI_SETTLE_AMT < CPI_CLAIM_AMT  THEN CPI_CLAIM_AMT ELSE CPI_SETTLE_AMT END) CPI_CLAIM_AMT,
  CPI_STATUS, CPI_CLAIM_TYPE, CPI_DETAILS,
  RANK() OVER PARTITION (BY CPI_CLIENT_REF ORDER BY INCIDENT_DATE DESC) AS RN
  FROM CLMPREINCEPT CP
  WHERE INCIDENT_DATE >= ADD_MONTHS(SYSDATE,60))
WHERE RN <= 5

For Oracle-related work, contact me through Linked-In.
 
Just noticed you wanted to pivot the results as well. You can do that based on the ranking number.

Code:
SELECT 
CPI_CLIENT_REF, 
  MAX(CASE WHEN RN=1 THEN CPI_INCIDENT_DATE END)) AS CPI_INCIDENT_DATE1, 
  MAX(CASE WHEN RN=1 THEN CPI_CLAIM_AMT END)) AS CLAIM_AMT1,
  MAX(CASE WHEN RN=1 THEN CPI_STATUS END) AS CPI_STATUS1, 
  MAX(CASE WHEN RN=1 THEN CPI_CLAIM_TYPE END) AS CPI_CLAIM_TYPE1, 
  MAX(CASE WHEN RN=1 THEN CPI_DETAILS END) AS CPI_DETAILS1,
   MAX(CASE WHEN RN=2 THEN CPI_INCIDENT_DATE END)) AS CPI_INCIDENT_DATE2, 
...
FROM
(SELECT
  CPI_CLIENT_REF, 
  CPI_INCIDENT_DATE, 
  CPI_CLAIM_AMT,
  CPI_STATUS, 
  CPI_CLAIM_TYPE, 
  CPI_DETAILS,
  RN
FROM
(
SELECT CP.CPI_CLIENT_REF, CPI_INCIDENT_DATE, 
  (CASE WHEN CPI_SETTLE_AMT < CPI_CLAIM_AMT  THEN CPI_CLAIM_AMT ELSE CPI_SETTLE_AMT END) CPI_CLAIM_AMT,
  CPI_STATUS, CPI_CLAIM_TYPE, CPI_DETAILS,
  RANK() OVER PARTITION (BY CPI_CLIENT_REF ORDER BY INCIDENT_DATE DESC) AS RN
  FROM CLMPREINCEPT CP
  WHERE INCIDENT_DATE >= ADD_MONTHS(SYSDATE,60))
WHERE RN <= 5)
GROUP BY CPI_CLIENT_REF



For Oracle-related work, contact me through Linked-In.
 
Dagon

Couple of misplace parentheses but your suggestion did the job perfectly.

Tank you

Ian
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top