SQL-based assignment table

Question

SQL-based assignment table

fmurphy

SSC Veteran

Points: 288
More actions
September 2, 2010 at 9:48 am

#227330

I've got a problem similar to this contrived example: I need a function/code snippet/whatever to help a large sports marketing firm assign an agent code to each of their clients. The required inputs are: sport, league, location, and status. The desired output is the intials of the assigned agent.
Sample data:
sport, league, location, status, agent
soccer, NULL, NULL, NULL, JFK
baseball, AL, NULL, NULL, LBJ
baseball, NL, NULL, NULL, GWC
baseball, NULL, NULL, NULL, AAA
basketball, NBA, TX, NULL, KFC
basketball, NBA, NY, NULL, CSL
basketball, NBA, NULL, retired, ARP
basketball, NBA, NULL, NULL, CTH
basketball, NULL, NULL, NULL, KFC
football, NFL, NULL, QB, JKU
football, NFL, CA, NULL, SAY
football, NFL, FL, retired, JEB
football, NFL, FL, NULL, PBR
So this means that all soccer players get the same agent (JFK). In baseball, all AL players get LBJ, NL = GWC and the minor leaguers = AAA.
The tricky part is the other leagues. A basketball player in the NBA living in TX gets KFC. NBA/NY = CSL. All retired players = ARP, even if they live in TX or NY. All other active NBA players get CTH and all minor leaguers go to KFC (also the NBA/TX agent).
Similar for football ... All QBs = JKU. All in CA=SAY. QB/CA = JKU. NFL/FL/retired = JEB and all other NFL/FL (except NFL/FL/QB) = PBR.
As you can see, multiple rules might apply, so I'd need a way to rank them (perhaps as simple as using "MIN(identity_row)" or "TOP 1/ORDER BY identity_row") and make sure I get only one agent per player.
What I tried:
At first, I thought about using a convoluter series of nested CASE statements, but it turned out to be huge (5 pages long) and looks like a booger to maintain.
Then I tried setting up a table and doing a series of JOINs, but there's a lot of fields and not all of them are used in each sport. Also, I'd like to have use wildcards (shown in the sample data as NULL) to cover the situations I don't anticipate.
The only constraint in play is sport. Assume that the other fields are freeform text and maintaining tables of allowable valuables in not feasible.
I did come up with something that (barely) works, but it is too slow to be practical. I made a table with entries like this:
id, code, agent
1, 'SOC ___ ___ ___', 'JFK'
2, 'BAS AL ___ ___', 'LBJ'
3, 'BAS NL ___ ___', 'GWC'
4, 'BAS ___ ___ ___', 'AAA'
etc
Then I made a function using LIKE and string wildcards (see below). I'd prefer a method where I can JOIN rather than use a lookup function. Also, the AGENT table has about 10,000 rows in it (and that's with the wildcards helping keep it smaller).
CREATE FUNCTION get_agent(@code CHAR(15)) RETURNS CHAR(3) BEGIN AS
DECLARE @ret CHAR(3)
SELECT TOP 1 @ret = id
FROM AGENT_ASSIGNMENTS AS X
WHERE
CASE
WHEN EXISTS (SELECT 1 FROM AGENT_ASSIGNMENTS WHERE = @code LIKE code)
THEN @code
ELSE
'!!DEFAULT!!'
END LIKE X.code
ORDER BY id DESC
RETURN (SELECT agent FROM AGENT_ASSIGNMENTS WHERE id = @ret)
END
Final thought:
I'm obviously not asking someone to do this for me. I just can't seem to wrap my head around this problem. After I got the wildcard-based function to work and it was too slow, I can't figure out what I try next. Any nudges, principles, thoughts, suggestions, etc. would be greatly appreciated.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

fmurphy (9/2/2010)
I've got a problem similar to this contrived example: I need a function/code snippet/whatever to help a large sports marketing firm assign an agent code to each of their clients. The required inputs are: sport, league, location, and status. The desired output is the intials of the assigned agent.
Sample data:
sport, league, location, status, agent
soccer, NULL, NULL, NULL, JFK
baseball, AL, NULL, NULL, LBJ
baseball, NL, NULL, NULL, GWC
baseball, NULL, NULL, NULL, AAA
basketball, NBA, TX, NULL, KFC
basketball, NBA, NY, NULL, CSL
basketball, NBA, NULL, retired, ARP
basketball, NBA, NULL, NULL, CTH
basketball, NULL, NULL, NULL, KFC
football, NFL, NULL, QB, JKU
football, NFL, CA, NULL, SAY
football, NFL, FL, retired, JEB
football, NFL, FL, NULL, PBR
So this means that all soccer players get the same agent (JFK). In baseball, all AL players get LBJ, NL = GWC and the minor leaguers = AAA.
The tricky part is the other leagues. A basketball player in the NBA living in TX gets KFC. NBA/NY = CSL. All retired players = ARP, even if they live in TX or NY. All other active NBA players get CTH and all minor leaguers go to KFC (also the NBA/TX agent).
Similar for football ... All QBs = JKU. All in CA=SAY. QB/CA = JKU. NFL/FL/retired = JEB and all other NFL/FL (except NFL/FL/QB) = PBR.
As you can see, multiple rules might apply, so I'd need a way to rank them (perhaps as simple as using "MIN(identity_row)" or "TOP 1/ORDER BY identity_row") and make sure I get only one agent per player.
What I tried:
At first, I thought about using a convoluter series of nested CASE statements, but it turned out to be huge (5 pages long) and looks like a booger to maintain.
Then I tried setting up a table and doing a series of JOINs, but there's a lot of fields and not all of them are used in each sport. Also, I'd like to have use wildcards (shown in the sample data as NULL) to cover the situations I don't anticipate.
The only constraint in play is sport. Assume that the other fields are freeform text and maintaining tables of allowable valuables in not feasible.
I did come up with something that (barely) works, but it is too slow to be practical. I made a table with entries like this:
id, code, agent
1, 'SOC ___ ___ ___', 'JFK'
2, 'BAS AL ___ ___', 'LBJ'
3, 'BAS NL ___ ___', 'GWC'
4, 'BAS ___ ___ ___', 'AAA'
etc
Then I made a function using LIKE and string wildcards (see below). I'd prefer a method where I can JOIN rather than use a lookup function. Also, the AGENT table has about 10,000 rows in it (and that's with the wildcards helping keep it smaller).
CREATE FUNCTION get_agent(@code CHAR(15)) RETURNS CHAR(3) BEGIN AS
DECLARE @ret CHAR(3)
SELECT TOP 1 @ret = id
FROM AGENT_ASSIGNMENTS AS X
WHERE
CASE
WHEN EXISTS (SELECT 1 FROM AGENT_ASSIGNMENTS WHERE = @code LIKE code)
THEN @code
ELSE
'!!DEFAULT!!'
END LIKE X.code
ORDER BY id DESC
RETURN (SELECT agent FROM AGENT_ASSIGNMENTS WHERE id = @ret)
END
Final thought:
I'm obviously not asking someone to do this for me. I just can't seem to wrap my head around this problem. After I got the wildcard-based function to work and it was too slow, I can't figure out what I try next. Any nudges, principles, thoughts, suggestions, etc. would be greatly appreciated.

I'll probably create a rules table with a structure like...

- Sport

- League

- State

- Retired?

- Agent

... to describe the business rules for Agent assignment.

Then, at "assignment" time I'll start by checking if the row in Players table meets all four conditions, in the affirmative case I'll assign the Agent... if the row does not matches the four conditions I'll try with three conditiions to assign the Agent.

Do you get the idea?

Start with the more specific criteria then move to less specific criteria.

The agent with the less specific criteria would be Agent "UnAssingned" where the four criterias are Null.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.