query to extend the valid_till date for a month of tenants who have referred more than two times
sample data:
ref_id referrer_id referrer_bonus_amount referral_valid valid_from valid_till
263 5 2500 1 2015-07-05 2015-09-05
264 3 2500 1 2015-07-05 2015-09-05
265 5 1000 0 2015-12-13 2016-02-13
266 6 2500 0 2016-04-25 2016-06-24
267 10 1000 1 2015-07-01 2015-09-01
268 5 2500 1 2015-05-12 2015-07-12
269 10 2500 0 2015-08-05 2015-10-05
what I want is to increase valid_till date of referrer_id who has referred more than twice. To get who has referred more than 2 times I'm using this code:
> select referrer_id
> from Referrals group by referrer_id having count(referrer_id)>2
but how to add one month for referrer_id >2 in valid_till column. I believe dateadd is to be used but i couldnt figure out how.
Try this
> UPDATE TableName
set valid_till = DateAdd(month,1,valid_from)AS valid_till
where referrer_id in (select referrer_id
from Referrals group by referrer_id having count(referrer_id)>2)
in this case, I'm assuming valid_from is the date you want to add a month to since you did not specify