SQL Challenge

["Neill Harper" <neill.harper@xxxxxxxx>]

Hi Guys

 

Not sure if there is a way to do this, but if there is someone on here will
know how!!

 

If I have 2 files, File A and File B that have the same record format as
follows:

 

File A

Field A (Key)

Field B  (Key)

Field C

Field D

Field E

 

File B

Field A (Key)

Field B  (Key)

Field C

Field D

Field E

 

 

File A is a new version of File B, i.e. at one point it contained exactly
the same records as File B. However over time File A has had records
inserted, deleted and updated. 

 

Can anybody think of a way using sql that gives me a third file FILE C, that
has the exact same format as FILE A and FILE B, except for one extra field
called Record Status. So that file C contains all of the original records
from FILE B that are no longer in FILE A (where record status = D, Deleted),
all of the new records in FILE A (where record status = I, Inserted) , all
of the Records that are in FILE A that have been changed from FILE B (where
record status = U, Updated) and finally all records in FILEA that have not
changed from FILE B( where status = X, unchanged)

 

Example

 

File A


Field A

Field B

Field C

Field D

Field E


2

1

Text12

Text22

Changed


3

1

Text123

Text23

Text334


4

1

Text1234

Text24

Text335


5

1

New

New

New

 

File B


Field A

Field B

Field C

Field D

Field E


1

1

Text1

Text2

Text3


2

1

Text12

Text22

Text33


3

1

Text123

Text23

Text334


4

1

Text1234

Text24

Text335

 

File C


Field A

Field B

Field C

Field D

Field E

Record Status


1

1

Text1

Text2

Text3

D


2

1

Text12

Text22

Changed

U


3

1

Text123

Text23

Text334

X


4

1

Text1234

Text24

Text335

X


5

1

New

New

New

I

 

Any help with this will be useful, thanks

 

Neill