CREATE TABLE engversion (
	sono varchar (6) NOT NULL ,
	bomversion int NOT NULL ,
	aguid varchar (50) NULL ,
	createdate smalldatetime NOT NULL CONSTRAINT DF_engversion_createdate DEFAULT (getdate()),
	CONSTRAINT PK_engversion PRIMARY KEY  CLUSTERED 
	(
		sono,
		bomversion
	)
)


CREATE TABLE engboms (
	sono varchar (6) NOT NULL ,
	externalballoon int NOT NULL ,
	process varchar (50) NULL ,
	qty decimal(12, 3) NULL ,
	partno varchar (255) NULL ,
	partnodesc varchar (255) NULL ,
	needdate smalldatetime NULL ,
	aguid varchar (50) NOT NULL ,
	CONSTRAINT PK_engboms PRIMARY KEY  CLUSTERED 
	(
		sono,
		externalballoon,
		aguid
	)
)







CREATE  PROC tool_Get_Bom_Versions_for_Diff
(@leftversion int = 1, @rightversion int = 1, @sono varchar(6) = 'C9999')

AS SET NOCOUNT ON


-- DECLARE @leftversion int
-- DECLARE @rightversion int
-- DECLARE @sono varchar(6)
-- SET @leftversion = 10
-- SET @rightversion = 11
-- SET @sono = 'C2729'



SELECT
externalballoon,
CASE WHEN laguid is null OR raguid is null THEN 1
ELSE 0 
END
as deleteaddflag,
CASE WHEN lprocess <> rprocess THEN 1
ELSE 0
END
as processmodified,
CASE WHEN lqty <> rqty THEN 1
ELSE 0
END as qtymodified,
CASE WHEN lpartno <> rpartno THEN 1
ELSE 0
END as partnomodified,
CASE WHEN lpartnodesc <> rpartnodesc THEN 1
ELSE 0
END as partnodescmodified,
CASE WHEN lneeddate <> rneeddate THEN 1
ELSE 0
END as needdatemodified,
lprocess, lqty, lpartno, lpartnodesc, CONVERT(char(10),lneeddate, 101) as lneeddate, laguid,
rprocess, rqty, rpartno, rpartnodesc, convert(char(10),rneeddate, 101) as rneeddate, raguid
FROM
(
	SELECT Z.externalballoon,
	Y.process as lprocess, Y.qty as lqty, Y.partno as lpartno, Y.partnodesc as lpartnodesc, Y.needdate as lneeddate, Y.aguid as laguid,
	X.process as rprocess, X.qty as rqty, X.partno as rpartno, X.partnodesc as rpartnodesc, X.needdate as rneeddate, X.aguid as raguid
	 FROM
	(
		SELECT B.externalballoon from engversion A 
		INNER JOIN engboms B
		ON A.aguid = B.aguid
		WHERE B.sono = @sono
		AND
		A.bomversion = @leftversion
		
		UNION
		
		SELECT B.externalballoon from engversion A 
		INNER JOIN engboms B
		ON A.aguid = B.aguid
		WHERE B.sono = @sono
		AND
		A.bomversion = @rightversion
	)
	Z
	LEFT JOIN
	(
		SELECT B.externalballoon, B.process, B.qty, B.partno, B.partnodesc, B.needdate, B.aguid from engversion A 
		INNER JOIN engboms B
		ON A.aguid = B.aguid
		WHERE B.sono = @sono
		AND
		A.bomversion = @leftversion
	)
	Y
	ON
	Z.externalballoon = Y.externalballoon
	LEFT JOIN
	(
		SELECT B.externalballoon, B.process, B.qty, B.partno, B.partnodesc, B.needdate, B.aguid from engversion A 
		INNER JOIN engboms B
		ON A.aguid = B.aguid
		WHERE B.sono = @sono
		AND
		A.bomversion = @rightversion
	)
	X
	ON
	Z.externalballoon = X.externalballoon
)
J