Anyone good with SQL

[Ken]

I use SQL every day at work but I can't wrap my brain around a query I'm trying to write for a personal project.
I have written much, much more complex queries than this, but I just can't think of the algorithm I need here.

I have a set of "positions" say this table:

Position
C
1B
2B
3B
SS
MI
OF
P

and I have a set of Player Positions, say this table (simplifying and just having a small dataset to determine the algorithm I'll use in a true roto example):

player/position
a c
a 1B
b 1B
b 2B
c 2B
d 3B
e SS
e OF

I want to know what positions are *potentially* available.

Obviously P is available (no player is a P in this table). But so are SS and OF (because "e" can be at EITHER one so both are potentially free).
2B is NOT available (because "c" has it), but C and 1b are also not available, because with "c" at 2nd, "b" is forced to 1st and "a" is similarly forced to C.

Normally I'd be thinking of a query looking for all combinations (massive cross joining) and finding a combination where a particular column is null. But these are rows not columns and my brain is stuck in knots. I think there's an easy solution here I'm just missing it. Recursive CTE? Not sure.

[umjewman]

I am not. Happy to answer that question of yours.

[Gregg]

I am not. Happy to answer that question of yours.

I think it is a trick question. The set of positions is a row. The set of player/position is a column.

[heyelander]

try either mjl or controller jacobs

[Steve 2.0]

The only person I ever met that was good at SQL is dead.

[umjewman]

The only person I ever met that was good at SQL is dead.

Jeez, who invited Debbie Downer to the thread?

[Steve 2.0]

Jeez, who invited Debbie Downer to the thread?

Sometimes the truth is inconvenient.

[Kevin Seitzer]

I think I would want to create a table (possibly just as a subquery) that had one row for each player and one column for each possible position, with a 1 or 0 based on the eligibility of that player at that position. Seems like that would be easier to work with than the second table that you have. There might still be some tricky logic around things like MI, but I think that would simplify what you are doing.

[Ken]

I think I would want to create a table (possibly just as a subquery) that had one row for each player and one column for each possible position, with a 1 or 0 based on the eligibility of that player at that position. Seems like that would be easier to work with than the second table that you have. There might still be some tricky logic around things like MI, but I think that would simplify what you are doing.

Yes, pivoting the table was one of my first thoughts too. I think you are correct. Typically when you pivot you define your column list and I was hoping to stay more dynamic (using this in multiple leagues, some with 1C 2UT, some with 2C 1UT).

I think there are ways to pivot more generally. I could also just create columns 1-50 and assume that no league will have more than 50 active roster spots, backfilling the numbers that are > the active roster size as taken.

[madducks]

Sometimes the truth is inconvenient.

An Inconvenient Truth would be a great name for a movie.

[Steve 2.0]

Yes, pivoting the table was one of my first thoughts too. I think you are correct. Typically when you pivot you define your column list and I was hoping to stay more dynamic (using this in multiple leagues, some with 1C 2UT, some with 2C 1UT).

I think there are ways to pivot more generally. I could also just create columns 1-50 and assume that no league will have more than 50 active roster spots, backfilling the numbers that are > the active roster size as taken.

It's posts like this that make me glad I retired from fantasy sports.

[Steve 2.0]

An Inconvenient Truth would be a great name for a movie.

I see what you did there.

[Kevin Seitzer]

It's posts like this that make me glad I retired from fantasy sports.

Got tired of all the heavy lifting?

[Steve 2.0]

Got tired of all the heavy lifting?

Yes, being the commish of my local league was exhausting. Kudo to ANYONE who can commish AND keep their sanity.

[Ken]

OK, I have a solution. It's not optimized yet. Following Seitzer's recommendation I started thinking about columns. I can dynamically create columns using string concatenation and delimiters, and that actually leads to a viable solution:

declare @Positions table (id int, pos varchar(5))
insert into @positions select 1, 'C1'
insert into @positions select 2, 'C2'
insert into @positions select 3, '1B'
insert into @positions select 4, '2B'
insert into @positions select 5, '3B'
insert into @positions select 6, 'SS'
insert into @positions select 7, 'CI'
insert into @positions select 8, 'MI'
insert into @positions select 9, 'OF1'
insert into @positions select 10, 'OF2'
insert into @positions select 11, 'OF3'
insert into @positions select 12, 'OF4'
insert into @positions select 13, 'OF5'
insert into @positions select 14, 'UT'
insert into @positions select 15, 'P1'
insert into @positions select 16, 'P2'
insert into @positions select 17, 'P3'
insert into @positions select 18, 'P4'
insert into @positions select 19, 'P5'
insert into @positions select 20, 'P6'
insert into @positions select 21, 'P7'
insert into @positions select 22, 'P8'
insert into @positions select 23, 'P9'

declare @playerpositions table (id int identity(1,1), player varchar(max), pos varchar(max), posid int)
insert into @playerpositions select 'Yan Gomes', 'C1', 1
insert into @playerpositions select 'Yan Gomes', 'C2', 2
insert into @playerpositions select 'Yan Gomes', 'UT', 14
insert into @playerpositions select 'Tom Murphy', 'C1', 1
insert into @playerpositions select 'Tom Murphy', 'C2', 2
insert into @playerpositions select 'Tom Murphy', 'UT', 14
insert into @playerpositions select 'Bobby Bradley', 'UT', 14
insert into @playerpositions select 'Trevor Bauer', 'P1', 15
insert into @playerpositions select 'Trevor Bauer', 'P2', 16
insert into @playerpositions select 'Trevor Bauer', 'P3', 17
insert into @playerpositions select 'Trevor Bauer', 'P4', 18
insert into @playerpositions select 'Trevor Bauer', 'P5', 19
insert into @playerpositions select 'Trevor Bauer', 'P6', 20
insert into @playerpositions select 'Trevor Bauer', 'P7', 21
insert into @playerpositions select 'Trevor Bauer', 'P8', 22
insert into @playerpositions select 'Trevor Bauer', 'P9', 23
insert into @playerpositions select 'Kenta Maeda', 'P1', 15
insert into @playerpositions select 'Kenta Maeda', 'P2', 16
insert into @playerpositions select 'Kenta Maeda', 'P3', 17
insert into @playerpositions select 'Kenta Maeda', 'P4', 18
insert into @playerpositions select 'Kenta Maeda', 'P5', 19
insert into @playerpositions select 'Kenta Maeda', 'P6', 20
insert into @playerpositions select 'Kenta Maeda', 'P7', 21
insert into @playerpositions select 'Kenta Maeda', 'P8', 22
insert into @playerpositions select 'Kenta Maeda', 'P9', 23

declare @count int
select @count = count(distinct player) from @playerpositions

select 'LINEUP'

;with playercombo (ids, players, positions, playercount, positionnumber)
as (

select '|' + cast(id as varchar(max)) + '|' as ids, '|' + player + '|' as players, '|' + pos + '|' as positions, 1 as playercount, posid as positionnumber from @playerpositions p

union all

select pc.ids + '|' + cast(p.id as varchar(max)) + '|' as players,
pc.players + '|' + p.player + '|' as players,
pc.positions + '|' + p.pos + '|' as positions,
pc.playercount + 1 as playercount,
pc.positionnumber + p.id as positionnumber
from playercombo pc
inner join @playerpositions p
on pc.players not like '%|' + p.player + '|%'
and pc.positions not like '%|' + p.pos + '|%'


)
select pp1.player, pp1.pos
from @playerpositions pp1
inner join (
select top 1 *
from playercombo
where playercount = @count
order by positionnumber asc
) pc
on pc.ids like '%|' + cast(pp1.id as varchar(10)) + '|%'
where pc.playercount = @count

SELECT 'CAN BID ON'

;with playercombo (players, positions, playercount)
as (

select '|' + player + '|' as players, '|' + pos + '|' as positions, 1 as playercount from @playerpositions p

union all

select pc.players + '|' + p.player + '|' as players,
pc.positions + '|' + p.pos + '|' as positions,
pc.playercount + 1 as playercount
from playercombo pc
inner join @playerpositions p
on pc.players not like '%|' + p.player + '|%'
and pc.positions not like '%|' + p.pos + '|%'


)
select pos.pos from @Positions pos
where exists (select 1 from playercombo where playercount = @count and positions not like '%|' + pos.pos + '|%')

Results:

LINEUP

Yan Gomes C1
Tom Murphy C2
Bobby Bradley UT
Trevor Bauer P1
Kenta Maeda P2

CAN BID ON

1B
2B
3B
SS
CI
MI
OF1
OF2
OF3
OF4
OF5
P1
P2
P3
P4
P5
P6
P7
P8
P9

Next I'm going to look at bitmasks, I think with up to 14 players on offense 2^14 is a reasonable sized number such that bitmasks may make more sense rather than string concatenation.

Also I'm overdoing it on the pitchers. Pitcher number doesn't matter, I can just handle them separately. Except Ohtani. I'll have to have an Ohtani exception - I'll treat him as a Pitcher by default but also allow for if you have an open UT slot he can be UT as well. Not hard to make a one-off exception like that.