CODEWITHSUNDEEP
sql-server
Sourav Roy
Sourav Roy

Reputation: 407

How to increase the month by one in a date based on condition in SQL Server

I am trying to to extend the valid_till date for a month of tenants who have reference id more than two times.

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

Upvotes: 0

Views: 105

Answers (1)

SqlZim
SqlZim

Reputation: 38023

To update the table (t) to add 1 month to the valid_till date for those refid that appear in referrer_id more than two times using exists() with 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 demo: http://rextester.com/WXZC31875

output: 

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

Upvotes: 1

Related Questions