This is the script of my tables
Create Table IM
(
ID int idenetity (1,1)
,Code varchar(100)
,CurrentMrp float
,CurrentCP float
,lastMrp float
,lastCp float
, effective_st_dt date
,effective_end_dt date
)
Create table IME
(
Code varchar(100)
,CurrentMrp float
,CurrentCP float
,Effective_st_dt date
,Effective_end_dt date
)
insert into IM (code,currentMrp,currentCp,lastMRP,lastCP,effective_st_dt,effective_end_dt)
Select 'CA123',10.12,5.0,8.20,4,'2014-05-01','2014-05-31'
union
Select 'CA123',15.0,5.0,10.12,8.20,'2014-06-01','2014-08-31'
union
Select 'CA121',50.0,15.0,45.0,25.0,'2014-04-01','2014-05-31'
union
Select 'CA121',75.0,25.0,50.0,15.0,'2014-06-01','2014-06-30'
union
Select 'CA131',53.0,12.0,35.0,10.0,'2014-05-01','2014-05-31'
union
Select 'CA131',60.0,15.0,53.0,12.0,'2014-06-01','2014-08-31'
Insert into IME (code,effective_st_dt,effective_end_dt)
Select ('CA123',20.0,5.0,'2014-06-01','2014-08-31')
union
Select ('CA123',25.0,6.0,'2014-06-20','2014-08-31')
union
Select ('CA123',35.0,7.0,'2014-07-15','2015-03-31')
I have 3 scenarios to implment.
Case 1- When IM.Code=IME.Code and IM.Effective_st_dt=IME.effective_st_dt and IM.Effective_end_dt =IME.Effective-end_date
Action-
In this case i need to update the IM with currentMrp and currentCp.
Case 2- When IM.Code=IME.Code and IM.Effective_st_dt!=IME.effective_st_dt and IM.Effective_end_dt =IME.Effective-end_date
Action-
1.)Pick the recent Effective_st_dt record from IM and Update the Effective_end_date with (IME.Effective_st_dt)-1
2.)then Insert a new record from IME which has new effective_st_dt also this record lastMRP and lastcp is the currentmrp and Currentcp of the previous record which is updated in 1
Case 3- When IM.Code=IME.Code and IM.Effective_st_dt!=IME.effective_st_dt and IM.Effective_end_dt !=IME.Effective-end_date
Action-
1.)Pick the recent Effective_st_dt record from IM and Update the Effective_end_date with (IME.Effective_st_dt)-1
2.)then Insert a new record from IME which has new effective_st_dt also this record lastMRP and lastcp is the currentmrp and Currentcp of the previous record which is updated in 1