how to insert based on the date
Posted
by
Gaolai Peng
on Stack Overflow
See other posts from Stack Overflow
or by Gaolai Peng
Published on 2012-09-29T03:33:18Z
Indexed on
2012/09/29
3:37 UTC
Read the original article
Hit count: 235
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?
© Stack Overflow or respective owner