Help with SQL query (add 5% to users with conditions)

Question

Hi everyone,

I’m having some difficulties with a query which purpose is to give users with more than one thread (called CS) in current year a 5% point “raise”. My relational schema looks like this:

Thread = (**threadid**, threadname, threadLocation)

threadoffering = (threadid, season, year, user)

user = (**name**, points)

Then, what I need is to check:

WHERE thread.threadid = threadoffering.threadid AND where threadoffering.year AND threadoffering.season = currentDate AND where threadoffering.User > 1

then GIVE 5 % raise TO user.points

I hope it is explained thoroughly but otherwise here it is in short text:

Give a 5 % “point raise” to all users who has more than 1 thread in threadLocation CS in the current year and season (always dynamic, so for example now is year = 2010 and season is = spring).

I am looking forward to your answer

Sincerely, Emil

Answer 1

This should work:

SELECT
    u.name,
    CASE WHEN (COUNT(*) > 1)
        THEN MIN(u.points) * 1.05
        ELSE MIN(u.points)
    END AS points
FROM
    threadoffering to inner join user u
        on to.user = u.name
WHERE
    to.year = @targetYear and to.season = @targetSeason
GROUP BY
    u.name