CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012
npeach
npeach

Reputation: 17

BEGIN and END Syntax Error in SQL IF Statement (SQL Server 2012)

I've been asked to create a score from a comparison of the current week to the average of the past four weeks. (Note 2/2/2018 is used because the test data I have is stale)

The code was working fine until I had to wrap BEGIN and END around my statement execution to add in for updating the scoring table. I started getting the error notice on END and the ELSE that followed it. SQL Server 2012 only responds with a "Syntax error". I've googled around for code examples but everything I found shows to do it the way I have pasted below. I'm at a lost at this point of what could be causing it. Could anyone assist?

Declare @HighScore as float(2) 
Set @HighScore = 35

IF (Select COUNT (Date) FROM data WHERE DATEDIFF(d,Date,'2/2/2018') <= 6 AND Category='High')
>
(Select COUNT (Date)/4 FROM data WHERE DATEDIFF(d,Date,'2/2/2018')>=7 AND DATEDIFF(d,Date,'2/2/2018')<35 AND Category='High')
BEGIN
    SET @HighScore = @HighScore - 5 
    INSERT INTO Scoring ('2/2/2018', 'High', @HIGHSCORE, 1)
END
ELSE 
    (Select COUNT (Date) FROM data WHERE DATEDIFF(d,Date,'2/2/2018')<=6 AND Category='High')
<
(Select COUNT (Date)/4 FROM data WHERE DATEDIFF(d,Date,'2/2/2018')>=7 AND DATEDIFF(d,Date,'2/2/2018')<35 AND Category='High')
BEGIN
    SET @HighScore = @HighScore + 5
    INSERT INTO Scoring ('2/2/2018', 'High', @HIGHSCORE, 1)
END
ELSE 
BEGIN
    SET @HighScore = @HighScore + 0

    INSERT INTO Scoring ('2/2/2018', 'High', @HIGHSCORE, 1)
END

Upvotes: 1

Views: 1004

Answers (1)

digital.aaron
digital.aaron

Reputation: 5707

You're missing an IF after the first ELSE (since you're doing another comparison). Also, you're missing the VALUES keyword in your INSERT statements.

EDIT: As Zohar points out in the comment below, you should really specify the column list of the target table, before the VALUES clause. We can't see what these are from the OP's original question, so I have used my own best guesses to fill them in.

Correcting for the errors and formatting code, I come up with:

DECLARE @HighScore AS FLOAT(2);

SET @HighScore = 35;

IF
    (
        SELECT COUNT(Date)FROM data WHERE DATEDIFF(d, Date, '2/2/2018') <= 6 AND Category = 'High'
    ) >
    (
        SELECT COUNT(Date) / 4
        FROM data
        WHERE DATEDIFF(d, Date, '2/2/2018') >= 7
            AND DATEDIFF(d, Date, '2/2/2018') < 35
            AND Category = 'High'
    )
BEGIN
    SET @HighScore = @HighScore - 5;

    INSERT INTO Scoring (Date, Category, Score, PlayerID) 
    VALUES ('2/2/2018', 'High', @HighScore, 1);
END;
ELSE IF
    (
        SELECT COUNT(Date)FROM data WHERE DATEDIFF(d, Date, '2/2/2018') <= 6 AND Category = 'High'
    ) <
    (
        SELECT COUNT(Date) / 4
        FROM data
        WHERE DATEDIFF(d, Date, '2/2/2018') >= 7
            AND DATEDIFF(d, Date, '2/2/2018') < 35
            AND Category = 'High'
    )
BEGIN
    SET @HighScore = @HighScore + 5;

    INSERT INTO Scoring (Date, Category, Score, PlayerID)
    VALUES ('2/2/2018', 'High', @HighScore, 1);
END;
ELSE
BEGIN
    SET @HighScore = @HighScore + 0;

    INSERT INTO Scoring (Date, Category, Score, PlayerID)
    VALUES ('2/2/2018', 'High', @HighScore, 1);
END;

Upvotes: 4

Related Questions