Compare two tables from different dates

[japhias]

Dear all,

as a first step I want to compare to exports from different dates, one initial export and one latest. As a secound step it would be nice, if the user is able to compare exports from different dates, but first lets focus on the first step:

I have two tables (always with the same columns, with changing  number of rows) exported on 2 different dates (initial and latest).  I did  a Concatenate between this two tables. I have key that will be the same in the table versions. 

I want to visualize:

- what records/rows were deleted from initial to latest version
- what records were created in the latest version
- what attributes have changed from initial to latest version

How can I do that?

[Marcoimp]

Hi Japhias,
The question is not so clear (for me).

Do you need to count id/rows or to know wich id/rows are in first, last condition?

Can you provide an example of resulting table?

[japhias]

Hi,

thank you for your response. I try to explain a little bit more with an example:

[Marcoimp]

Ok!
I think you can do something like this:

init_tab:
load ID, Date as Date1, Weight as Weight1 From InitialTable;

outer join
load ID, Date as Date2, Weight as Weight2 from LatestTable;

FinalTab:
Load
   ID,
   if(isnull(Weight2),'deleted', if(isnull(Weight1),'new','')) as Status,
   if(Weight1<>Weight2,'Changed','')) as WeightStatus
Resident init_tab;

drop table init_tab;

Maybe you have to add some "IF" for Date1 and Date2 so you can see what value is the most recent... or something like this... Hope can help!