I have this query:
SELECT p.PLAYER_ID , p.FIRST_NAME, p.LAST_NAME,
FROM tbl_WEEK w INNER JOIN xref_PLAYER_WEEK x ON x.WEEK_ID = w.WEEK_ID
WHERE x.PLAYER_ID = p.PLAYER_ID
AND w.SEASON_ID = @SEASON_ID
) AS AVAILABLE_POINTS
FROM tbl_PLAYER p
Basically, it gets players in a league and sums up the number of players there were each week. As you can see, tbl_WEEK has a column called "NUMBER_OF_PLAYERS".
However, a new rule has been implemented that I need to figure out how to include in the query.
A new column in tbl_WEEK has been added called "HAS_GHOST_PLAYER".
If this new column is "TRUE", then i need to subtract one from NUMBER_OF_PLAYERS. If it's "FALSE", then the query stays as-is.
I tried doing a CASE on the nested SELECT statement, which works great if there's only one WEEK in the season. But if there are multiple weeks, I get the "subquery returns multiple columns" errors.
Any help on how to go about this would be greatly appreciated.