I have following 4 tables
PREMIUM:
LOAD
CLA_CODE,
PRD_CODE,
POLICY_NO,
POL_PERIOD_FROM,
POL_PERIOD_TO,
BCOD,
TRN_DATE AS RISK_DATE,
PREMIUM
FROM
[..\..\QVD\FACT_Table\Sales.qvd]
(qvd)
RI_PREMIUM:
POLICY_NO,
TRA_BRANCH AS BCOD,
TRA_POLICY_NO AS POLICY_NO,
TRN_DATE AS RISK_DATE,
PREMIUM AS RI_PREMIUM,
COMMISSION AS RI_COMM,
TRA_VALID_FR_DT AS POL_PERIOD_FROM,
TRA_VALID_TO_DT AS POL_PERIOD_TO
FROM
RI_PREMIUM.QVD
(qvd);
CLAIM_PAID:
CLAIM_NO
POLICY_NO,
BCOD,
PRD_CODE,
PERIOD_FROM AS POL_PERIOD_FROM,
PERIOD_TO AS POL_PERIOD_TO ,
CLAIM_PAID_AMOUNT
FROM
[..\..\QVD\FACT_Table\Claims.qvd]
(qVd)
RECOVERY:
POLICYNO as POLICY_NO,
CLAIMNO as CLAIM_NO,
PAYAMT,
RECAMT AS RECV_AMOUNT,
PRD AS PRD_CODE,
BCOD
FROM
RI_RECOVERY.QVD
(qvd)
these four table relates data for insurance
Premium= data for policy premium figures
RI_Premium= Reinsurance data for policy premium
Claims_paid= Insurance claims paid
Recovery =Recovery for claim paid
I want to relate above 4 tables on the following basis
RI_Premium against Premium
Recovery against Claims_paid
Finally I want to create a report as follows
Bcod, Policy No, Premium, RI_premium,CLAIM_PAID_AMOUNT,RECV_AMOUNT RELATING TO POLICY PERIOD
pLS HELP ME WITH DATA MODEL FOR THIS
