I have a table table1 (account, last_contact_date, insert_date), account and last_contact_date are primary keys. The insert_date is set with the time the recored being added by calling getdate(). I also have a temporary table #temp(account, last_contact_date) which I use to update the table1.
Here are sample data:
table1
account last_contact_date insert_date
1 2012-09-01 2012-09-28
2 2012-09-01 2012-09-28
3 2012-09-01 2012-09-28
#temp
account last_contact_date
1 2012-09-27
2 2012-09-27
3 2012-08-01
The result table depends on the inserting date. If the date is 2012-09-28, the result will be
table1
account last_contact_date insert_date
1 2012-09-27 2012-09-28
2 2012-09-27 2012-09-28
3 2012-09-01 2012-09-28
If the date is 2012-09-29, the result will be
table1
account last_contact_date insert_date
1 2012-09-01 2012-09-28
2 2012-09-01 2012-09-28
3 2012-09-01 2012-09-28
1 2012-09-27 2012-09-29
2 2012-09-27 2012-09-29
Basically the rule is
(1) if the inserting date is the same day, i will pick the lastest last_contact_date, otherwise,
(2) if the last_contact_date is later than the current last_contact_date, I will insert a new one.
How do I write a query for this insert?
