DSXchange

Hi friends, this is deesh, actually i am retrieving data from xml files, in that one between start date and End date need one day gap. can you people tell me how to update the one difference.

Example:-

If the Customer-Contract-Number exists in the LSA database then
If the LSA-Policy-Start-Date <> Customer-Contract-EffDt or the LSA-Policy-End-Date <> Customer-Contract-TermDt then create a new block of timelined data in the LSA database for Adoption policies, and update previous timeline end date to be one day less than the new timeline start date.

Example:
LSA Adoption Policy Database Current Record

------------------------------------ Pol_Num---- Start Date-----Code End Date
-------------------------------------12345678----03/01/2008-----12/31/9999

Contract input
-------------------------------------12345678----03/01/2008-----03/31/2009
-------------------------------------12345678----04/01/2009-----12/31/9999

LSA Policy Database after update
-------------------------------------12345678----03/01/2008-----03/31/2009
-------------------------------------12345678----04/01/2009-----12/31/9999

This is a rather complex problem because you have not stated business rules for when the contract and policy time ranges don't perfectly overlap.

If your desire is to simply "cut" the policy into ranges of the intersection of policies and contracts, then a simple join is required via pol_num where the policy.start_date between contract.start_date and contract.end_date. Use the policy.start_date and then use the lesser date of policy.end_date or contract.end_date for each returned row.