pr2-eugin
Super Moderator
- Local time
- Today, 19:03
- Joined
- Nov 30, 2011
- Messages
- 8,494
I am working on an Insurance DB.. I have two tables tbl_Clients and tbl_PolicyDetails with a One-Many relationship.. tbl_Clients have the Clients basic info, Name, Tel, Address etc. The tbl_PolicyDetails have the details of the policy for the customer like Start date, policy term, amount they pay, policy status etc.
Policy Status can be any of the following Quote, Active, Suspended, Lapsed, Cancelled, Terminated..
One client can have many Policies.. When one policy finishes (like when cover upgraded/downgraded/any changed their insured property address etc.) we categorise them as Termination of old policy thus the client will have a New policy.. At any stage we only need the newest policy per client.. That can have any of the status as mentioned above..
My problem now is I need to list all Clients with policy status Active, Suspended, Lapsed, Cancelled..
This would work in an ideal environment.. However, Consider the following data..
tbl_Clients
tbl_PolicyDetails
When a Client wishes to Cancel a policy it is not Terminated, the policy status is just Cancelled.. Similar to Lapsed policy, if the policy hits 30 days of no pay then it is passed on to a Debt collection agency.. If in future the Client comes back a new policy is generated for both cases.. So in this case Client Lauren and Caryn will have been repeated twice, in the report I am trying to generate (based on the above query).. I do not wish to display the Cancelled policy if the customer has another policy which again can be Active, Suspended, Lapsed, Cancelled.. So the result I wish to have for the above data set would be..
Would appreciate some insight.. I am not sure how to apply this rule (in plain words)
Policy Status can be any of the following Quote, Active, Suspended, Lapsed, Cancelled, Terminated..
One client can have many Policies.. When one policy finishes (like when cover upgraded/downgraded/any changed their insured property address etc.) we categorise them as Termination of old policy thus the client will have a New policy.. At any stage we only need the newest policy per client.. That can have any of the status as mentioned above..
My problem now is I need to list all Clients with policy status Active, Suspended, Lapsed, Cancelled..
Code:
SELECT tbl_Clients.cli_ID, tbl_Clients.firstName, tbl_Clients.address, tbl_PolicyDetails.policyCover
FROM tbl_Clients INNER JOIN tbl_PolicyDetails ON tbl_Clients.cli_ID = tbl_PolicyDetails.cliID_FK
WHERE tbl_PolicyDetails.policyStatus = "Active" Or tbl_PolicyDetails.policyStatus = "Suspended" Or tbl_PolicyDetails.policyStatus = "Lapsed" Or tbl_PolicyDetails.policyStatus = "Cancelled";
tbl_Clients
Code:
cli_ID firstName address
1 Paul Bournemouth
2 Mark Bristol
3 Lauren London
4 Caryn Devon
Code:
pol_ID cliID_FK policyCover policyStatus
1 1 PDB Active
2 2 PD Terminated
3 2 PDB Active
4 3 PDB Cancelled
5 4 PDR Lapsed
6 4 PDE Active
7 3 PDBCH Suspended
Code:
cli_ID firstName address policyCover policyStatus
1 Paul Bournemouth PDB Active
2 Mark Bristol PDB Active
3 Lauren London PDBCH Suspended
4 Caryn Devon PDE Active
I have attached the Dummy version of the DB file..If a client has more than one policy, take only the last policy into the Query..