Sql - loop through two tables and sum a value based on condition

Question

I'm working on a project for a betting program (point based) and if need to create a stored procedure on sql server that does the following:

• Sum 3 points if the user bets on a team and gets the result right;
• Sum 1 point if the result is diferent from what the user guessed but still manages to be right on whether the team wins, loses or ties (i.e. User bets on Real Madrid - Barcelona; user guesses 2 - 1; result 1 - 0);
• If the user gets either wrong he doesn't get any points.

I've been searching forever on a way to do this but I can't figure out a way. It may well be a noobish question, be we all have to start somewhere. I thought about looping, which is theoretically what I need to do but I need to iterate through each row from the bets table and compare its values with the values of each row of the games table.

It'd be great if you could help me. Thanks.

EDIT: Oh, shoot. Sorry, I was about to add the tables and I forgot. Well, here they are:

-Bets table:

CREATE TABLE Bets(
   id_bet        NUMERIC(18,0) NOT NULL PRIMARY KEY 
  ,id_user       NUMERIC(18,0)
  ,id_game       NUMERIC(18,0)
  ,date          NUMERIC(8,0)
  ,time          NUMERIC(4,0)
  ,goals_home    NUMERIC(18,0)
  ,goals_visitor NUMERIC(18,0)
);
INSERT INTO Bets(id_bet,id_user,id_game,date,time,goals_home,goals_visitor) VALUES (1,1,4,20170614,1600,1,1);
INSERT INTO Bets(id_bet,id_user,id_game,date,time,goals_home,goals_visitor) VALUES (2,1,3,20170614,1600,1,1);
INSERT INTO Bets(id_bet,id_user,id_game,date,time,goals_home,goals_visitor) VALUES (3,7,3,20170614,1600,1,1);
INSERT INTO Bets(id_bet,id_user,id_game,date,time,goals_home,goals_visitor) VALUES (4,7,4,20170614,1600,1,1);

---

-Games table:

CREATE TABLE Games(
   id_game         NUMERIC(18,0) NOT NULL PRIMARY KEY 
  ,num_game        NUMERIC(18,0)
  ,id_club_home    NUMERIC(18,0)
  ,id_club_visitor NUMERIC(18,0)
  ,id_competition  NUMERIC(18,0)
  ,goals_home      NUMERIC(18,0)
  ,goals_visitor   NUMERIC(18,0)
  ,date            NUMERIC(8,0)
  ,time            NUMERIC(4,0)
);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (1,1,3,6,2,2,2,20170614,1700);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (3,2,4,3,2,1,3,20170614,1800);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (4,3,3,4,2,1,3,20170614,1800);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (5,4,6,3,2,2,3,20170614,1800);
INSERT INTO Games(id_game,num_game,id_club_home,id_club_visitor,id_competition,goals_home,goals_visitor,date,time) VALUES (6,5,4,6,2,NULL,NULL,20170614,1600);

---

-I've tried this (and other ways but didn't save them):

CREATE PROCEDURE [dbo].[Count_Points] @valor AS INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @a AS INT
    DECLARE @goals_home_bet AS NUMERIC(18, 0) = (
            SELECT Bets.goals_home
            FROM Bets
            INNER JOIN Games ON Bets.id_game = Games.id_game
            )
    DECLARE @goals_visitor_bet AS NUMERIC(18, 0) = (
            SELECT Bets.goals_visitor
            FROM Bets
            INNER JOIN Games ON Bets.id_game = Games.id_game
            )
    DECLARE @goals_home_games AS NUMERIC(18, 0) = (
            SELECT Games.goals_home
            FROM Bets
            INNER JOIN Games ON Bets.id_game = Games.id_game
            )
    DECLARE @goals_visitor_games AS NUMERIC(18, 0) = (
            SELECT Games.goals_visitor
            FROM Bets
            INNER JOIN Games ON Bets.id_game = Games.id_joid_gamego
            )

    SET @valor = 0
    SET @a = 1
    SET @valor = sum(@valor + 3)

    WHILE (
            (
                SELECT max(id_bet)
                FROM Bets
                ) > @a
            )
    BEGIN
        SET @a += 1
    END
END

It's just for testing to see if it worked (hence referencing the value 3 only), but apparently I get a value of 3 only. The other ways I'd also get 3 or null, even.

EDIT 2: My solution (probably not the most efficient, but still):

CREATE PROCEDURE [dbo].[Calculate_Points]
        @control as int output,
        @points as int output,
        @nickname varchar(50)

AS
BEGIN

    SET NOCOUNT ON;

    declare @cont numeric(18,0)=1
    declare @cont1 numeric(18,0)
    declare @id_user numeric(18,0)=(select num_user from Users where nickname=@nickname)
    declare @id_game numeric(18,0)
    declare @goals_home_bet numeric(18,0)
    declare @goals_visitor_bet numeric(18,0)
    declare @goals_home_game numeric(18,0)
    declare @goals_visitor_game numeric(18,0)
    set @points=0


    while(@cont<=(select max(num_bet) from Bets))
    begin
        if exists (select id_user from Bets where @id_user=id_user and num_bet=@cont)
        begin
            set @id_game=(select id_game from Bets where num_bet=@cont)
            set @goals_home_bet=(select goals_home from Bets where num_bet=@cont)
            set @goals_visitor_bet=(select goals_visitor from Bets where num_bet=@cont)
            set @cont1=1
            while(@cont1<(select max(id_game) from Games))
            begin
                if(@id_game=(select id_game from Games where id_game =@cont1))
                begin
                    set @goals_home_game=(select goals_home from Games where num_game=@cont1)
                    set @goals_visitor_game=(select goals_visitor from Games where num_game=@cont1)

                    if(@goals_home_bet=@golos_casa_jogo and @golos_fora_aposta=@golos_fora_jogo)
                        set @pontos+=3
                    else if((@goals_home_bet>@goals_visitor_bet and @goals_home_game>@goals_visitor_game) or (@goals_home_bet<@goals_visitor_bet and @goals_home_game<@goals_visitor_bet) or (@goals_home_bet=@goals_visitor_bet and @goals_home_game=@goals_visitor_bet))
                        set @points+=1
                end

                set @cont1+=1
            end
        end
        set @cont+=1
    end

END

Answer 1

Switch from bit to int for your goals and then try something like this:

create view vUserPointsPerGame
as
select
    id_user, bets.id_game,
    case 
        -- you could also just compare the goal differences if you want
        when bets.goals_home = games.goals_home and bets.goals_visitor = games.goals_visitor then 3
        when (bets.goals_home >= bets.goals_visitor and games.goals_home >= games.goals_visitor) or (bets.goals_home < bets.goals_visitor and games.goals_home < games.goals_visitor) then 1        
        else 0
    end as Points
from
    bets
    join games on bets.id_game = games.id_game

go

create view vUserTotalPoints
as
    select id_user, sum(points) as TotalPoints 
    from vUserPointsPerGame 
    group by id_user
go

select * from vUserTotalPoints

That will return you a sum of points across all bets broken down by user.

You can make this more efficient, but the main idea here is that you don't need loops or stored procedures to do what you want to accomplish here.

Edit: You mentioned you wanted a procedure since it's easier for you to consume. Here's one that will get the total points for a single user:

create procedure spGetUserTotalPoints
(
    @iUserId int,
    @oTotalPoints int output
)
as
begin
    select @oTotalPoints = TotalPoints
    from vUserTotalPoints
    where id_user = @iUserId
end

And here's another that will get the total points for each user for all known users:

create procedure spGetUsersTotalPoints
as
begin
    select id_user, TotalPoints
    from vUserTotalPoints
end

Again, if you're getting errors about batches, just run each statement one at a time instead of relying on the go keyword. Some SQL front-ends don't like that.