September 22, 2015 at 9:16 am
Lynn Pettis (9/22/2015)
I have a question for the respondents. Why am I the only one that actually created an itvf? The OP did ask for a function.
I sort of took this as an X / Y question. They asked for a function but imho a function isn't needed here. Instead it seems like a rethinking/re-architecture of the primary key would be better. 😀
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 22, 2015 at 9:17 am
I was just trying to prove a point. My option is very similar to yours but with the simple way to speed things up as validation isn't needed because the values are digits only (possibly a minus sign).
Here's a possible adaptation for Sean's solution.
create function dbo.ConcatSortSean( @p1 int, @p2 int, @p3 int, @p4 int, @p5 int)
returns varchar(100)
as
BEGIN
declare @MyNewValue varchar(100) = ''
select @MyNewValue = @MyNewValue + CAST(u.myCols as varchar(10))
from (SELECT @p1, @p2, @p3, @p4, @p5) s(col1, col2, col3, col4, col5)
unpivot (myCols for field in (col1, col2, col3, col4, col5)) u
order by u.myCols
RETURN @MyNewValue
END;
go
I'm uncertain about this concatenation method as I remember that the order isn't guaranteed even with the order by.
September 22, 2015 at 9:24 am
Okay, thank you all. I would still like a better explanation of the business use case from the OP since what has been provided so far is really vague and doesn't make much sense to me. Hopefully the OP can post something representative of the problem domain that we could actually run in our own sandbox environments.
September 22, 2015 at 10:13 am
You guys are using way too many foreign terms for me!!!! :crazy:
First of all, I referenced 5 columns in my original post (ha!, just figured out what OP means! lol) only as an attempt to simplify what I was trying to accomplish. The 9 columns is the actual case.
This I do not understand (from SSChampion): "This sounds like a perfect time for a composite key across those columns. Or make that combination of columns a unique clustered index and add an identity column as a nonclustered primary key."
Below is the SQL I use in MS Access that would give me the result I'm looking for if it could handle the task. Notice the function "fxMakeKey" is where I create the key.
Here's a high level of the data for one table:
Table: pos1
Column: pos1 - long type, ID
Column: pos1_cost - an integer value representing cost
Column: pos1_rating - an integer value representing ability (high is good)
Think of pos1 table as holding Red items. Pos2a-b hold Blue items. Pos3a-c hold Yellow items. Pos4 holds Green items. Pos5 can hold either blue, yellow, or green items. and finally pos6 holds Black items.
Each 9 column combination has 2 calculated values: TotCost (all of the individual col##_cost added together), and TotRating (all of the col##_ratings added together).
Also as noted before, pos2a and pos2b cannot be the same (hence WHERE pos2b.pos2b <>[pos2a] ). Same applies to pos3a-c and pos5
INSERT INTO target_table ( pos1, pos2a, pos2b, pos3a, pos3b, pos3c, pos4, pos5, pos6, TotCost, TotRating, RowKey )
SELECT pos1.pos1, pos2a.pos2a, pos2b.pos2b, pos3a.pos3a, pos3b.pos3b, pos3c.pos3c, pos4.pos4, pos5.pos5, pos6.pos6, [pos1_cost]+[pos2a_cost]+[pos2b_cost]+[pos3a_cost]+[pos3b_cost]+[pos3c_cost]+[pos4_cost]+[pos5_cost]+[pos6_cost] AS TotCost, [pos1_rating]+[pos2a_rating]+[pos2b_rating]+[pos3a_rating]+[pos3b_rating]+[pos3c_rating]+[pos4_rating]+[pos5_rating]+[pos6_rating] AS TotRating, fxMakeKey([pos1],[pos2a],[pos2b],[pos3a],[pos3b],[pos3c],[pos4],[pos5],[pos6]) AS Expr1
FROM pos1, pos2a, pos2b, pos4, pos5, pos3a, pos3b, pos3c, pos6
WHERE (((pos2b.pos2b)<>[pos2a]) AND ((pos3b.pos3b)<>[pos3a] AND (pos3b.pos3b)<>[pos3c]) AND((pos3c.pos3c)<>[pos3a] AND (pos3c.pos3c)<>[pos3b]) AND ((pos5.pos5)<>[pos2a] AND (pos5.pos5)<>[pos3b] AND (pos5.pos5)<>[pos3c] AND (pos5.pos5)<>[pos4]) AND(([pos1_cost]+[pos2a_cost]+[pos2b_cost]+[pos3a_cost]+[pos3b_cost]+[pos3c_cost]+[pos4_cost]+[pos5_cost]+[pos6_cost])<500) AND(([pos1_rating]+[pos2a_rating]+[pos2b_rating]+[pos3a_rating]+[pos3b_rating]+[pos3c_rating]+[pos4_rating]+[pos5_rating]+[pos6_rating])>25) AND((([pos5].[pos5])<>[pos2b])<>[pos3a]));
September 22, 2015 at 11:01 am
hreyes819 (9/22/2015)
You guys are using way too many foreign terms for me!!!! :crazy:First of all, I referenced 5 columns in my original post (ha!, just figured out what OP means! lol) only as an attempt to simplify what I was trying to accomplish. The 9 columns is the actual case.
This I do not understand (from SSChampion): "This sounds like a perfect time for a composite key across those columns. Or make that combination of columns a unique clustered index and add an identity column as a nonclustered primary key."
Below is the SQL I use in MS Access that would give me the result I'm looking for if it could handle the task. Notice the function "fxMakeKey" is where I create the key.
Here's a high level of the data for one table:
Table: pos1
Column: pos1 - long type, ID
Column: pos1_cost - an integer value representing cost
Column: pos1_rating - an integer value representing ability (high is good)
Think of pos1 table as holding Red items. Pos2a-b hold Blue items. Pos3a-c hold Yellow items. Pos4 holds Green items. Pos5 can hold either blue, yellow, or green items. and finally pos6 holds Black items.
Each 9 column combination has 2 calculated values: TotCost (all of the individual col##_cost added together), and TotRating (all of the col##_ratings added together).
Also as noted before, pos2a and pos2b cannot be the same (hence WHERE pos2b.pos2b <>[pos2a] ). Same applies to pos3a-c and pos5
INSERT INTO target_table ( pos1, pos2a, pos2b, pos3a, pos3b, pos3c, pos4, pos5, pos6, TotCost, TotRating, RowKey )
SELECT pos1.pos1, pos2a.pos2a, pos2b.pos2b, pos3a.pos3a, pos3b.pos3b, pos3c.pos3c, pos4.pos4, pos5.pos5, pos6.pos6, [pos1_cost]+[pos2a_cost]+[pos2b_cost]+[pos3a_cost]+[pos3b_cost]+[pos3c_cost]+[pos4_cost]+[pos5_cost]+[pos6_cost] AS TotCost, [pos1_rating]+[pos2a_rating]+[pos2b_rating]+[pos3a_rating]+[pos3b_rating]+[pos3c_rating]+[pos4_rating]+[pos5_rating]+[pos6_rating] AS TotRating, fxMakeKey([pos1],[pos2a],[pos2b],[pos3a],[pos3b],[pos3c],[pos4],[pos5],[pos6]) AS Expr1
FROM pos1, pos2a, pos2b, pos4, pos5, pos3a, pos3b, pos3c, pos6
WHERE (((pos2b.pos2b)<>[pos2a]) AND ((pos3b.pos3b)<>[pos3a] AND (pos3b.pos3b)<>[pos3c]) AND((pos3c.pos3c)<>[pos3a] AND (pos3c.pos3c)<>[pos3b]) AND ((pos5.pos5)<>[pos2a] AND (pos5.pos5)<>[pos3b] AND (pos5.pos5)<>[pos3c] AND (pos5.pos5)<>[pos4]) AND(([pos1_cost]+[pos2a_cost]+[pos2b_cost]+[pos3a_cost]+[pos3b_cost]+[pos3c_cost]+[pos4_cost]+[pos5_cost]+[pos6_cost])<500) AND(([pos1_rating]+[pos2a_rating]+[pos2b_rating]+[pos3a_rating]+[pos3b_rating]+[pos3c_rating]+[pos4_rating]+[pos5_rating]+[pos6_rating])>25) AND((([pos5].[pos5])<>[pos2b])<>[pos3a]));
First, OP is actually an overloaded term. It can mean Original Poster (you in this case) or Original Post (the first post of the thread). You have to look at the context of the term to really know which.
Second, a composite key is an index based on more than one column of data. Yes, you can do this. In your case it may not provide what you are looking for since you sort the values in the first five (using your OP) columns then concatenate these values to provide your unique key.
Third, I am still lost at the business case for this requirement. Nothing you have posted has really helped me (at least) understand what you are doing and why. Why is this important? Because, there may be a better and more efficient way of accomplishing what you are doing in SQL Server compared to how you did it in Access.
If you can create a set of scripts that we could use in a sandbox environment (DDL for the tables, sample data, expected results based on the sample data) that would go a long way to helping us understand what you are doing.
Yes, we can just give you an answer and go on our way, but many of us like to try and help beyond that. If we can show you better ways to accomplish a task you learn more as well. Plus, it keeps our skills sharp as we don't always get to work on things like yours in our normal paid positions. Help you actually helps us as well.
September 22, 2015 at 4:01 pm
Alright, as I told Lynn, I got the impression that the great responses I was getting were all-business and this is not a business case. I'm working on a hobby idea for my fantasy football games :smooooth:
Anyway, the game is salary based (every player has an assigned salary value) - you put together a lineup that does not exceed $$$$ dollars. Every player plays a specific position and you have a set number of players you can pick by position. What I'm trying to do is for post completion of the games during a particular week when we have the score that the players actually accumulated. The goal: to generate what would have been the best lineups in order to analyze and generate better lineup suggestions going forward.
Originally all of the players are in one table but I thought that it would be cleaner to break the one table into an individual table by position in the lineup. The lineup is made up of; 1 QB, 2 RBs, 3 WRs, 1 TE, 1 FLX (RB, WR, or TE) and 1 D (defense). So the qb table would have all of the players that have the pos = "QB". The rb1 and rb2 tables would have all of the players with pos = "RB" (so on, so forth). So the columns for the qb table look like:
-qb - player ID
-qb_sal - the player's salary
-qb_score - the player's score that week
I use the same logic for coming up with potential lineups before the games happen BUT, I don't use the whole player universe to run the append qry. I pre-select a few players by position (ones I really like for that week) usually not exceeding 15-20 total players (compared to close to 400 players in this post game completion case). So the tool then gives me every possible lineup combination that could result from those selected players. To give you an example, this past week I selected 19 players and wound up with about 17,000 potential lineups (which I filter by selecting players from each position - show me lineups that have player1, player12, player45, etc). If I didn't create a record key on the fly like I do, those 17,000 combinations could very well be several hundred thousand - but with duplicate lineups - same players plugged into different buckets (positions).
In my post-games case and to reduce the number of potential lineup combinations, I limit the generated lineups to those that total at least 170 points (winning score is typically just over 200 points).
I tried to generate a script to share with you guys but I opened the resulting file and there was no data for the individual tables. Just the script to create the tables. I'd be happy to try it again if somebody can detail for me how to generate a script that not only will create the tables, but include the data.
So again, here is the sql I put together in Access with my function to create the key: fxMakeKey([qb],[rb1],[rb2],[wr1],[wr2],[wr3],[te],[flx],[d]). This returns all 9 ids sorted and concatenated into one string value that is appended to the primary key field. This works in Access because when a duplicate key is found, it is ignored and not appended.
INSERT INTO lineups ( qb, rb1, rb2, wr1, wr2, wr3, te, flx, d, LineupSal, LineupScore, LineupKey )
SELECT qb.qb, rb1.rb1, rb2.rb2, wr1.wr1, wr2.wr2, wr3.wr3, te.te, flx.flx, d.d, [qb_sal]+[rb1_sal]+[rb2_sal]+[wr1_sal]+[wr2_sal]+[wr3_sal]+[te_sal]+[flx_sal]+[d_sal] AS LineupSal, [qb_score]+[rb1_score]+[rb2_score]+[wr1_score]+[wr2_score]+[wr3_score]+[te_score]+[flx_score]+[d_score] AS LineupScore, fxMakeKey([qb],[rb1],[rb2],[wr1],[wr2],[wr3],[te],[flx],[d]) AS Expr1
FROM qb, rb1, rb2, te, flx, wr1, wr2, wr3, d
WHERE (((rb2.rb2)<>[rb1]) AND ((wr2.wr2)<>[wr1] And (wr2.wr2)<>[wr3]) AND ((wr3.wr3)<>[wr1] And (wr3.wr3)<>[wr2]) AND ((flx.flx)<>[rb1] And (flx.flx)<>[wr2] And (flx.flx)<>[wr3] And (flx.flx)<>[te]) AND (([qb_sal]+[rb1_sal]+[rb2_sal]+[wr1_sal]+[wr2_sal]+[wr3_sal]+[te_sal]+[flx_sal]+[d_sal])<50000) AND (([qb_score]+[rb1_score]+[rb2_score]+[wr1_score]+[wr2_score]+[wr3_score]+[te_score]+[flx_score]+[d_score])>170) AND ((([flx].[flx])<>[rb2])<>[wr1]));
October 2, 2015 at 2:39 pm
-- disclaimer: wild guess, I've run nothing! posting just for fun!
Well I'm making the assumption that a player thats a rb can be rb1, rb2, or rb3, and there is no profit in putting him in as rb1 instead of rb3, you've said as much because you're sorting to eliminate those dupes. I'm also assuming that a rb is no use as a wr, or flx, or whatever, otherwise, you'll have to include the additional positions in the combination logic (as opposed to permutations).
Its possible that what you want is combinations for the rb's and wr's, and cross joins for everything else, and maybe you can just ditch the key all together.
WITH rb1 as SELECT (rb rb1, rb_sal rb1_sal, rb_score rb1_score from rb),
rb2 as SELECT (rb rb2, rb_sal rb2_sal, rb_score rb2_score from rb),
wr1 as SELECT (wr wr1, wr_sal wr1_sal, wr_score wr1_score from wr),
wr2 as SELECT (wr wr2, wr_sal wr2_sal, wr_score wr2_score from wr),
wr3 as SELECT (wr wr3, wr_sal wr3_sal, wr_score wr3_score from wr)
INSERT INTO lineups ( qb, rb1, rb2, wr1, wr2, wr3, te, flx, d, LineupSal, LineupScore, LineupKey )
SELECT qb.qb, rb1.rb rb1, rb2.rb rb2, wr1.wr wr1, wr2.wr wr2, wr3.wr wr3, te.te, flx.flx, d.d,
[qb_sal]+[rb1_sal]+[rb2_sal]+[wr1_sal]+[wr2_sal]+[wr3_sal]+[te_sal]+[flx_sal]+[d_sal] AS LineupSal,
[qb_score]+[rb1_score]+[rb2_score]+[wr1_score]+[wr2_score]+[wr3_score]+[te_score]+[flx_score]+[d_score] AS LineupScore
FROM qb cross join rb1 cross join rb2 cross join te cross join flx cross join wr1 cross join wr2 cross join wr3 cross join d
WHERE rb1 < rb2 and wr1 < wr2 and wr2 < wr3
AND [qb_sal]+[rb1_sal]+[rb2_sal]+[wr1_sal]+[wr2_sal]+[wr3_sal]+[te_sal]+[flx_sal]+[d_sal] < @target_sal
AND [qb_score]+[rb1_score]+[rb2_score]+[wr1_score]+[wr2_score]+[wr3_score]+[te_score]+[flx_score]+[d_score] > @target_score
Permutation is when order matters, combination is when it doesn't.
edit: fix possible syntax, remove qualifier from adjective "wild", and everything else might not be actual permutations.
also forgot to mention, this uses one table for rb's and one table for wr's.
Viewing 7 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply