how to extend date by specific condition

Question

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.

score 1 · Answer 1 · answered Jun 28 '18 at 21:09

1

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
    )

answered Jun 28 '18 at 21:09

Russell Fox

5,273
1
24
28

BlackMarker · Accepted Answer · 2018-06-29T14:13:34.747

0

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

edited Jun 29 '18 at 14:13

answered Jun 28 '18 at 21:04

BlackMarker

139
12

thank you. i wanted to extend from valid_till . Thanks your solution worked. :) – chahak joshi Jun 30 '18 at 09:42

score 0 · Answer 3 · answered Jun 28 '18 at 21:31

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

how to extend date by specific condition

3 Answers3