Sql - loop through two tables and sum value based on condition
I am working on a project for a betting program (point based) and if you need to create a stored procedure in sql server that does the following:
- Total 3 points if the user places bets in a team and gets the result correctly;
- The sum is 1 point if the result differs from what the user guessed, but may still be right, whether the team wins, loses or ties (ie custom bets on Real Madrid - Barcelona, user guesses 2 - 1, result ten);
- If the user is wrong, he does not receive any points.
I've always looked for a way to do this, but I can't seem to find a way. This may be a noobish question, we should all start somewhere. I was thinking about a loop, in theory what I need to do, but I need to iterate over each row from the betting table and compare its values with the values of each row of the games table.
It would be great if you can help me. Thank.
EDIT: Oh shoot. Sorry I was about to add 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 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 works (hence referring only to value 3), but apparently I only get value 3. In other ways I also get 3 or null, even.
EDIT 2: My solution (maybe 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
source to share
Go from bit
before int
for your own purposes, 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
This will return you the amount of points for all bets broken by the user.
You can make it more efficient, but the main idea here is that you don't need loops or stored procedures to do what you want to do here.
Edit: You mentioned that you want to follow the procedure as it is easier for you to use it. Here's one that will get total scores for one 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 one that will get the total scores for each user for all known users:
create procedure spGetUsersTotalPoints
as
begin
select id_user, TotalPoints
from vUserTotalPoints
end
Again, if you get batch errors, just run each statement one at a time instead of relying on the keyword go
. Some SQL interfaces don't like this.
source to share