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

Filed under:
|
|

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

Related posts about sql-server

Related posts about insert