sql - Comparing rows in a table against latest (by date) row in another and inserting or updating based on condition -

i have 1 table (approx 800k rows), 'a' sake of post has id , statusid. table 'b', stores duration (fromdate , todate) of row in a maintaining single status - updated once day.

a: id, statusid,  b: idfroma, fromdate, todate, statusid 

for instance if have row: (id: 123, statusid: 1) holds onto statusid 2 days , on 3rd day changes (id: 123, statusid: 2) table b used record change.

i need write query i'm not sure how compare current statusid of each row in a of latest row same id in b - , depending on either insert new row or extend todate (by in case day) - pointers help

you can use trigger on tablea keep tableb in sync.

if object_id('tablea') not null     drop table tablea create table tablea (     id int,     statusid int ) if object_id('tableb') not null     drop table tableb create table tableb (     id int,     fromdate datetime,     todate datetime,     statusid int ) go  create trigger tg_tablea_statudid on tablea after insert,update begin     -- update existing values     update b set         todate = getdate()     tableb b         inner join inserted             on a.id = b.id     b.todate null         , b.statusid <> a.statusid      -- create new values     insert tableb          select id, getdate(), null, statusid         inserted          not exists (             select *             tableb             id = a.id                 , statusid = a.statusid         )  end go  insert tablea values (123, 1) update tablea set statusid = 2 id = 123  select * tablea select * tableb 

tablea after testing:

id          statusid ----------- ----------- 123         2 

tableb after testing

id          fromdate                todate                  statusid ----------- ----------------------- ----------------------- ----------- 123         2015-06-19 11:12:42.940 2015-06-19 11:12:42.973 1 123         2015-06-19 11:12:42.993 null                    2