如何在SQL Server中根据条件在日期中增加一个月

Question

我正在尝试将引用id超过两次的租户的valid_till日期延长一个月。

refid, referrer_id, referrer_bonus_amount, referral_valid, valid_from, valid_till

1   2   2500    1   2015-07-05  2015-09-05
2   3   2500    1   2015-07-05  2015-09-05
3   5   1000    0   2015-12-13  2016-02-13
4   6   2500    0   2016-04-25  2016-06-24
5   10  1000    1   2015-07-01  2015-09-01
6   12  2500    1   2015-05-12  2015-07-12
7   13  2500    0   2015-08-05  2015-10-05
8   20  1000    1   2016-02-05  2016-04-05
9   2   2500    0   2015-08-12  2015-09-12
10  5   91000   1   2016-02-18  2016-04-18
11  20  1500    1   2016-06-19  2016-08-19
12  9   2500    0   2015-11-15  2016-01-15
13  13  91000   1   2016-02-01  2016-04-01
14  5   1000    1   2016-04-25  2016-06-24

Answer 1

对表(t)执行update操作，以便为那些在referrer_id中出现两次以上的refid添加1个月的valid_till日期(使用exists()和having count(*) > 2

update t
  set valid_till = dateadd(month,1,valid_till)
output inserted.*
from t
where exists (
  select 1
  from t as i
  where i.referrer_id = t.refid
  group by referrer_id
  having count(*) > 2
  )

rextester演示：http://rextester.com/WXZC31875

输出：

+-------+-------------+-----------------------+----------------+------------+------------+
| refid | referrer_id | referrer_bonus_amount | referral_valid | valid_from | valid_till |
+-------+-------------+-----------------------+----------------+------------+------------+
|     5 |          10 |                  1000 |              1 | 2015-07-01 | 2015-10-01 |
+-------+-------------+-----------------------+----------------+------------+------------+