How To Compare Two Tables And Find The Gap?

Hi,

We have inventory table which is updated everyday.
I need to find the gap between updated table vs history (which means yesterday's data).
what i did is I just copy the inventory everyday before it is updated.
So, whenver inventory table is updated I want to know the gap between these two (history vs updated) tables.

The structure of two tables are same and here are columns:
date_key (fk)
store_key(fk)
item_key(fk)
begin_inventory_dollars
ending_inventory_dollars
begin_inventory_units
ending_inventory_units

I want to compare each row and if the value is changed I want to have a gap (updated value - history value) and if data is new then just want to add.

Here is tsql: however when I compare to gap between table from excel spreadsheet and this query, this query does not return a right value. (Some are correct but some don't)




Code Snippet
select a.date_date_key,
sum(a.Beg_Inventory_Dollars- (case when b.beg_inventory_dollars is NULL then 0 else b.Beg_Inventory_Dollars end)),

sum(a.Beg_Inventory_Units-(case when b.beg_Inventory_Units is NULL then 0 else b.beg_Inventory_Units end)) ,

sum(a.Ending_Inventory_Dollars- (case when b.Ending_Inventory_Dollars is NULL then 0 else b.Ending_Inventory_Dollars end)),

Sum(a.Ending_Inventory_Units-(case when b.Ending_Inventory_units is NULL then 0 else b.Ending_Inventory_Units end))

from UPDATED TABLE a

left outer join HISTORY TABLE b

on b.item_key = a.item_key and

b.store_key =a.store_key and

b.date_key =a.date_key

group by a.fisc_date_key







Did I miss something in here?
Any kind of advice would be grealy appreciated.
Thanks.