Biswa Biswa - 3 years ago 143
SQL Question

Returning an updated column value based on the conditions of another column value in SQL Server

I have a table

Referrals
with these columns:


  • ref_id

  • referrer_id(same as profile id)

  • referrer_bonus_amount

  • referral_valid

  • valid_from

  • valid_till
    .



I need to write code to extend the
valid_till
date for a month of tenants who have referred more than two times. The number of references made can calculated by the number of occurrences of a particular number (
profile_id
) in the column
referrer_id
(same as profile id). The below two pieces of code are doing their job individually but I don't know how to join them to get the desired result.

select DATEADD(mm, 1, valid_till)
from Referrals

select
[referrer_id(same as profile id)],
count([referrer_id(same as profile id)])
from referrals
group by [referrer_id(same as profile id)]
having count([referrer_id(same as profile id)]) > 2

Answer Source

You could use the in operator:

SELECT DATEADD(MM, 1, valid_till) 
FROM   referrals
WHERE  referrer_id IN (SELECT   referrer_id
                       FROM     referrals
                       GROUP BY referrer_id
                       HAVING   COUNT(*) > 2)

Or if you really need to update the table and not just query the added month, you could use the same idea in an update statement:

UPDATE referrals 
SET    valid_till = DATEADD(MM, 1, valid_till) 
WHERE  referrer_id IN (SELECT   referrer_id
                       FROM     referrals
                       GROUP BY referrer_id
                       HAVING   COUNT(*) > 2)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download