how to extend date by specific condition

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.

3 Answers
3

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

thank you. i wanted to extend from valid_till . Thanks your solution worked. :)
– chahak joshi
yesterday

UPDATE [YourTable]
SET [valid_till] = DATEADD(MONTH, 1, [valid_till])
WHERE [ref_id] IN (
SELECT [ref_id]
FROM [YourTable]
GROUP BY [ref_id]
HAVING COUNT([ref_id]) > 1
)


I think you're seeking this method :

SELECT
ref_id
, referrer_id
, referrer_bonus_amount
, referral_valid
, valid_from
, DATEADD(MONTH, 1, valid_till) AS valid_till
FROM (
SELECT
ref_id
, referrer_id
, referrer_bonus_amount
, referral_valid
, valid_from
, valid_till
, ROW_NUMBER() OVER(PARTITION BY referrer_id ORDER BY referrer_id) AS RN
FROM @test
) D
WHERE
RN > 2


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.