Click here to Skip to main content
15,897,273 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Comments
OriginalGriff 28-Jun-14 4:22am    
And?
If you are waiting for permission, then consider it given.
If you want help, then explain what you have tried, and where you are stuck.

Because at the moment, it looks like you have copy'n'pasted your homework...and we don't do that for you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900