carl Brooks carl Brooks - 5 months ago 7
SQL Question

NULL values for when trying to perform an UPDATE

Trying to complete a lookup table and keep receiving null values when I want to update the fixtures table. I am assuming that since I tried to change the logic from hard code to lookup table, it isn't working.

Below displays the four tables (from top to bottom):
- Score
- ScoreReference
- Fixture
- TeamWeighting

enter image description here

I want to try and determine the difference in teamWeighting between both sides for each fixture and based on the weighting, it determines the score to update the 'Fixture' table by selecting a score that matches the goal difference based on the difference of 'TeamWeighting'.

As you can see it displays NULL and I am not sure why this is.

Below is the code of the query that I know is causing the problem:

;WITH cte AS
(
SELECT f.FixtureID,
ht.FinalTeamWeight - at.FinalTeamWeight AS TeamScore
FROM dbo.Fixture f
OUTER APPLY
(
SELECT tw.FinalTeamWeight
FROM dbo.TeamWeighting tw
WHERE tw.TeamID = f.HomeTeamID
) ht --HomeTeam
OUTER APPLY
(
SELECT tw.FinalTeamWeight
FROM dbo.TeamWeighting tw
WHERE tw.TeamID = f.AwayTeamID
) at --AwayTeam
WHERE f.HomeScore IS NULL AND f.FixtureDate < GETDATE()
)


UPDATE f
SET f.HomeScore = s.HomeScore,
f.AwayScore = s.AwayScore
FROM dbo.Fixture f
INNER JOIN
(
SELECT FixtureID,
TeamScore,
(
SELECT TOP (1)
CASE
WHEN c.TeamScore BETWEEN HomeWeighting AND AwayWeighting
THEN (SELECT TOP 1 ScoreID FROM dbo.Score WHERE HomeScore > AwayScore ORDER BY NEWID())
WHEN c.TeamScore BETWEEN AwayWeighting AND HomeWeighting
THEN (SELECT TOP 1 ScoreID FROM dbo.Score WHERE AwayScore > HomeScore ORDER BY NEWID())
END AS ScoreID
FROM dbo.Score s
LEFT JOIN ScoreReference sr
ON s.ScoreDifference = sr.ScoreDifference

) AS ScoreID -- end select case

FROM cte c

) -- end inner join

AS ScoreResult
ON f.FixtureID = ScoreResult.FixtureID
INNER JOIN Score s
ON ScoreResult.ScoreID = s.ScoreID
INNER JOIN ScoreReference sr
ON s.ScoreDifference = sr.ScoreDifference


Select * from dbo.Score
Select * from dbo.ScoreReference
SELECT * from dbo.Fixture
SELECT * From TeamWeighting

END

Answer

Try this. This should work

DECLARE @fixtureID INT,
    @scoreDifference INT

;WITH cte AS
(
    SELECT f.FixtureID,
        ht.FinalTeamWeight - at.FinalTeamWeight AS TeamScore
    FROM dbo.Fixture f
    OUTER APPLY
    (
        SELECT tw.FinalTeamWeight
        FROM dbo.TeamWeighting tw
        WHERE tw.TeamID = f.HomeTeamID
    ) ht --HomeTeam
    OUTER APPLY
    (
        SELECT tw.FinalTeamWeight
        FROM dbo.TeamWeighting tw
        WHERE tw.TeamID = f.AwayTeamID
    ) at --AwayTeam
    WHERE f.HomeScore IS NULL AND f.FixtureDate < GETDATE()
)


SELECT FixtureID, 
    ScoreDifference
INTO #processData
FROM
(
    SELECT DISTINCT c.FixtureID,
            TeamScore, 
            CASE WHEN TeamScore BETWEEN sr.HomeWeighting AND sr.AwayWeighting 
                    THEN sr.ScoreDifference 
                 WHEN TeamScore BETWEEN sr.AwayWeighting  AND sr.HomeWeighting 
                    THEN sr.ScoreDifference 
            END AS ScoreDifference
    FROM cte c
    CROSS APPLY ScoreReference sr
) score
WHERE ScoreDifference IS NOT NULL

WHILE (SELECT COUNT(*) FROM #processData) > 0
BEGIN

    SELECT TOP 1 @fixtureID = FixtureID,
        @scoreDifference = ScoreDifference 
    FROM #processData

    UPDATE f
    SET f.HomeScore = score.HomeScore,
     f.AwayScore = score.AwayScore
    FROM
    (
        SELECT TOP 1  HomeScore, 
            AwayScore, 
            @fixtureID AS FixtureID 
        FROM Score s
        WHERE ScoreDifference = @scoreDifference
        ORDER BY NEWID()
    ) AS score
    INNER JOIN Fixture f
        ON f.FixtureID = score.FixtureID

    DELETE #processData
    WHERE FixtureID = @fixtureID

END

    DROP TABLE #processData
Comments