IanWaterman
Programmer
I have a query which brings back the oldest claim from last 5 years
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
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