August 7, 2009 at 10:28 am
I'm working on a process, and I can't figure out how to implement one of the requirements.
The process is:
1. A selection board will convene, consisting of 3 to N # of board members.
2. This selection board will review eligible candidates for scholarships.
3. Each candidate will be assigned to be reviewed by 3, and only 3, of the board members.
4. Each board member's "list" of candidates to review should be evenly distributed (doesn't have to be exact, but should be close to equal).
and the kicker...
5. The candidate should not be assigned to a board members list if that board member is one of the top 5 schools that the candidate wants a scholarship to. However, enforcement of rule #3 takes precedence over this rule... meaning that if there are 4 board members, and 2 of them are from the top 5 school choices of a candidate, then the candidate still needs to be assigned to three board member's list.
I've got 1-4 handled, but I just can't figure out how to conditionally enforce that 5th business rule.
I've attached code to show what all is going on with steps 1-4, with readily consumable data to work with.
I grately appreciate your help in getting this figured out.
Thanks,
Wayne
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 7, 2009 at 5:33 pm
Warning. Don't click on the link above. Save it and open it with notepad or some other editor first. I'm not quite sure what it is doing, but it isn't the data advertised.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
August 7, 2009 at 9:49 pm
Bob Hovious (8/7/2009)
Warning. Don't click on the link above. Save it and open it with notepad or some other editor first. I'm not quite sure what it is doing, but it isn't the data advertised.
Worked fine for me in Chrome.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 8, 2009 at 12:55 am
RBarryYoung (8/7/2009)
Bob Hovious (8/7/2009)
Warning. Don't click on the link above. Save it and open it with notepad or some other editor first. I'm not quite sure what it is doing, but it isn't the data advertised.Worked fine for me in Chrome.
Me too (Chrome 3.0.197.11)
Attachment contents reproduced below:
declare @BrdDt datetime
set @BrdDt = '07/29/2009'
-- get the board members. Minimum of 3, no maximum (reality says < 10)
if OBJECT_ID('tempdb..#BrdMbrs') IS NOT NULL DROP TABLE #BrdMbrs
CREATE TABLE #BrdMbrs (VoterID int PRIMARY KEY CLUSTERED, ssn_id char(9), board_dt datetime, SchoolCd char(6))
insert into #BrdMbrs (VoterID) select top 5 VoterID = ROW_NUMBER() OVER (ORDER BY object_id) from sys.objects
-- get the candidates that are ready for the board selection process
if OBJECT_ID('tempdb..#candidates') IS NOT NULL DROP TABLE #candidates
CREATE TABLE #candidates (CandidateID int PRIMARY KEY CLUSTERED)
insert into #candidates select top 1200 CandidateID = ROW_NUMBER() OVER (ORDER BY s1.object_id) from sys.objects s1, sys.objects s2
-- get the school choices that the candidates want to go to
if OBJECT_ID('tempdb..#CandidateSchoolChoices') IS NOT NULL DROP TABLE #CandidateSchoolChoices
CREATE TABLE #CandidateSchoolChoices (CandidateID int, ChoiceNbr tinyint, SchoolChoice char(6), PRIMARY KEY (CandidateID, ChoiceNbr))
insert into #CandidateSchoolChoices
select CandidateID, dt.ChoiceNbr, right('000000' + convert(varchar(6), ABS(CHECKSUM(NEWID())) %1000000+ 1 ), 6)
from #candidates
CROSS APPLY (select top 5 ChoiceNbr = ROW_NUMBER() OVER (ORDER BY OBJECT_ID) from sys.objects) dt
declare @VoterCount tinyint,
@Vote tinyint
select @VoterCount = COUNT(*) from #BrdMbrs
-- get the three most popular school choices, and "assign" a board member to that school
-- in reality, the board member is from a school, and this code won't be performed.
;with CTE AS
(
select top (@VoterCount) SchoolChoice, COUNT(*) qty from #CandidateSchoolChoices group by SchoolChoice order by qty desc
)
, CTE2 AS
(
select SchoolChoice, VoterID = ROW_NUMBER() OVER (ORDER BY (select 1))
from CTE
)
update b
set b.SchoolCd = CTE2.SchoolChoice
from #BrdMbrs b INNER JOIN CTE2 ON b.VoterID = CTE2.VoterID
-- each candidate will be assigned to 3, and only three lists.
if OBJECT_ID('tempdb..#votes') IS NOT NULL DROP TABLE #votes
CREATE TABLE #votes (VoteNbr tinyint PRIMARY KEY CLUSTERED)
insert into #votes select top 3 VoteNbr = ROW_NUMBER() OVER (ORDER BY object_id) from sys.objects
-- assign candidates to 3 board member lists
;with CTE AS
(
select CandidateID,
VoteNbr,
RowNbr = ROW_NUMBER() OVER (ORDER BY CandidateID, VoteNbr)
from #candidates CROSS APPLY #votes
)
,CTE2 As
(
select CandidateID,
BrdMbrNbr = (RowNbr % @VoterCount)+ 1
from CTE
)
select CTE2.CandidateID,
CTE2.BrdMbrNbr,
B.ssn_id,
B.board_dt,
BrdMbrVote = @Vote
from CTE2
INNER JOIN #BrdMbrs B ON CTE2.BrdMbrNbr = B.VoterID
order by CandidateID
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
August 10, 2009 at 7:16 pm
If I understand this correctly, your final selection of three board members for a candidate could be the first three rows of an intermediate resut (one of your CTE's perhaps) of all candidate-board member pairs, ordered by a binary indicator of whether the board member school matched any of the candidate's top five choices (0 or 1), and within that, the board member's current count of candidates assigned (0-n). This would, I think, require a pivoted set of each candidate's top five choices for comparison with the board member's school, and a running-tally of candidate assignments for each board member, perhaps using the "quirky update" method that Jeff Moden has described in many posts.
My apologies for just throwing out a couple of quick ideas on this; admittedly they''re at a fairly high conceptual level. I've got way too much on my agenda now (including jury duty) to give this the time it would need for some real development effort. I wish I did have the time... I do look forward to comments on this suggested approach and an eventual solution.
August 11, 2009 at 10:43 am
john.arnott (8/10/2009)
If I understand this correctly, your final selection of three board members for a candidate could be the first three rows of an intermediate resut (one of your CTE's perhaps) of all candidate-board member pairs, ordered by a binary indicator of whether the board member school matched any of the candidate's top five choices (0 or 1), and within that, the board member's current count of candidates assigned (0-n). This would, I think, require a pivoted set of each candidate's top five choices for comparison with the board member's school, and a running-tally of candidate assignments for each board member, perhaps using the "quirky update" method that Jeff Moden has described in many posts.My apologies for just throwing out a couple of quick ideas on this; admittedly they''re at a fairly high conceptual level. I've got way too much on my agenda now (including jury duty) to give this the time it would need for some real development effort. I wish I did have the time... I do look forward to comments on this suggested approach and an eventual solution.
John,
I think that you're on the right track... let me play around with it and see what I can come up with. Thanks for the input!
BTW, I think Jeff calls it the "pseudo-cursor". Isn't it Phil that calls it the "quirky update"?
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 11, 2009 at 2:10 pm
WayneS (8/11/2009)
...BTW, I think Jeff calls it the "pseudo-cursor". Isn't it Phil that calls it the "quirky update"?
Nah, that's me. Jeff hates the term "pseudo-cursor" and much prefer's Phil's "quirky update" terminology. 🙂
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 12, 2009 at 1:20 pm
I've played around a bit with John's suggestion. I've come up with a cursor-based solution, but I can't figure out a set-based solution. I think the problem is that I need to calc the number of candidates already assigned to a voter with each pass, whereas the set-based methods that I've tried seem to only calc it once.
In trying to come up with a way to do the quirky update, it seems that with a variable number of voters, I'm not able to see a way to do this. I'd be keeping a running total on a variable number of elements, and then assigned a candidate to the three lowest voters in order of whether they are at one of the schools that the candidate wants to go to.
I welcome guidance in getting this going...
Here's the current code:
declare @BrdDt datetime
set @BrdDt = '07/29/2009'
-- get the board members. Minimum of 3, no maximum (reality says < 10)
if OBJECT_ID('tempdb..#BrdMbrs') IS NOT NULL DROP TABLE #BrdMbrs
CREATE TABLE #BrdMbrs (VoterID int PRIMARY KEY CLUSTERED, ssn_id char(9), board_dt datetime, SchoolCd char(6))
insert into #BrdMbrs (VoterID) select top 5 VoterID = ROW_NUMBER() OVER (ORDER BY object_id) from sys.objects
-- get the candidates that are ready for the board selection process
if OBJECT_ID('tempdb..#Candidates') IS NOT NULL DROP TABLE #Candidates
CREATE TABLE #Candidates (CandidateID int PRIMARY KEY CLUSTERED)
insert into #Candidates select top 1200 CandidateID = ROW_NUMBER() OVER (ORDER BY s1.object_id) from sys.objects s1, sys.objects s2
-- get the school choices that the candidates want to go to
if OBJECT_ID('tempdb..#CandidateSchoolChoices') IS NOT NULL DROP TABLE #CandidateSchoolChoices
CREATE TABLE #CandidateSchoolChoices (CandidateID int, ChoiceNbr tinyint, SchoolChoice char(6), PRIMARY KEY (CandidateID, ChoiceNbr))
insert into #CandidateSchoolChoices
select CandidateID, dt.ChoiceNbr, right('000000' + convert(varchar(6), ABS(CHECKSUM(NEWID())) %1000000+ 1 ), 6)
from #Candidates
CROSS APPLY (select top 5 ChoiceNbr = ROW_NUMBER() OVER (ORDER BY OBJECT_ID) from sys.objects) dt
declare @VoterCount tinyint,
@Vote tinyint
select @VoterCount = COUNT(*) from #BrdMbrs
-- get the three most popular school choices, and "assign" a board member to that school
-- in reality, the board member is from a school, and this code won't be performed.
;with CTE AS
(
select top (@VoterCount) SchoolChoice, COUNT(*) qty from #CandidateSchoolChoices group by SchoolChoice order by qty desc
)
, CTE2 AS
(
select SchoolChoice, VoterID = ROW_NUMBER() OVER (ORDER BY (select 1))
from CTE
)
update b
set b.SchoolCd = CTE2.SchoolChoice
from #BrdMbrs b INNER JOIN CTE2 ON b.VoterID = CTE2.VoterID
-- each candidate will be assigned to 3, and only three lists.
if OBJECT_ID('tempdb..#votes') IS NOT NULL DROP TABLE #votes
CREATE TABLE #votes (VoteNbr tinyint PRIMARY KEY CLUSTERED)
insert into #votes select top 3 VoteNbr = ROW_NUMBER() OVER (ORDER BY object_id) from sys.objects
-- temp table to see how many candidates are assigned to each voter
if OBJECT_ID('tempdb..#VoterList') IS NOT NULL DROP TABLE #VoterList
CREATE TABLE #VoterList (
CandidateID INT NOT NULL,
BrdMbrNbr INT NOT NULL,
ssn_id char(9),
board_dt datetime,
BrdMbrVote tinyint,
PRIMARY KEY CLUSTERED (CandidateID, BrdMbrNbr))
-- can't figure out how to get rid of this cursor...
declare @CandidateID INT
declare cCandidates CURSOR LOCAL FORWARD_ONLY for
select CandidateID from #Candidates
open cCandidates
fetch next from cCandidates into @CandidateID
while @@FETCH_STATUS = 0 begin
;WITH CTE AS
(
select c.CandidateID,
b.VoterID,
Same_School_Flag = (select COUNT(*)
from #CandidateSchoolChoices cs
where cs.CandidateID = c.CandidateID
and cs.SchoolChoice = b.SchoolCd),
VoterQty = (select COUNT(*)
from #VoterList
where BrdMbrNbr = b.VoterID)
from #Candidates c
CROSS APPLY #BrdMbrs b
)
INSERT INTO #VoterList (CandidateID, BrdMbrNbr, board_dt)
select top 3 -- <<<<<<<< this is the number of votes each candidate is to receive
CTE.CandidateID,
CTE.VoterID,
@BrdDt
from CTE
where CTE.CandidateID = @CandidateID
order by Same_School_Flag, VoterQty
fetch next from cCandidates into @CandidateID
end
close cCandidates
deallocate cCandidates
select BrdMbrNbr, COUNT(*) from #VoterList group by BrdMbrNbr
--select * from #VoterList
truncate table #VoterList
-- set based method doesn't evenly assign candidates to voters.
;WITH CTE AS
(
select c.CandidateID,
b.VoterID,
Same_School_Flag = (select COUNT(*)
from #CandidateSchoolChoices cs
where cs.CandidateID = c.CandidateID
and cs.SchoolChoice = b.SchoolCd),
VoterQty = (select COUNT(*)
from #VoterList
where BrdMbrNbr = b.VoterID)
from #Candidates c
CROSS APPLY #BrdMbrs b
)
, CTE2 AS
(
select CandidateID,
VoterID,
Same_School_Flag,
VoterQty,
RowNbr = ROW_NUMBER() OVER (PARTITION BY CandidateID ORDER BY CandidateID, Same_School_Flag, VoterQty)
from CTE
)
INSERT INTO #VoterList (CandidateID, BrdMbrNbr, board_dt)
select CTE2.CandidateID, CTE2.VoterID, @BrdDt
from CTE2
where CTE2.RowNbr between 1 and 3
select BrdMbrNbr, COUNT(*) from #VoterList group by BrdMbrNbr
--select * from #VoterList
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 14, 2009 at 10:41 am
RBarryYoung (8/11/2009)
WayneS (8/11/2009)
...BTW, I think Jeff calls it the "pseudo-cursor". Isn't it Phil that calls it the "quirky update"?
Nah, that's me. Jeff hates the term "pseudo-cursor" and much prefer's Phil's "quirky update" terminology. 🙂
Heh... actually, that's not quite true. I used to prefer the term "Set Based Loops". I actually think that the term "pseudo-cursor" is quite elegant and applies to much more than just the "quirky update". In fact, I have an "Introdution to Pseudo-Cursors" in the book that I'm trying to write giving Barry full accolades for the term and was going to ask Barry for permission to do that in a private email. Since it's come out here, Barry, is it ok to use the term in the book with the accolades to you?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2009 at 10:48 am
Wayne: Any chance we could get some Create Table defs & sample data Inserts?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2009 at 10:52 am
Jeff Moden (8/14/2009)
RBarryYoung (8/11/2009)
WayneS (8/11/2009)
...BTW, I think Jeff calls it the "pseudo-cursor". Isn't it Phil that calls it the "quirky update"?
Nah, that's me. Jeff hates the term "pseudo-cursor" and much prefer's Phil's "quirky update" terminology. 🙂
Heh... actually, that's not quite true. I used to prefer the term "Set Based Loops". I actually think that the term "pseudo-cursor" is quite elegant and applies to much more than just the "quirky update". In fact, I have an "Introdution to Pseudo-Cursors" in the book that I'm trying to write giving Barry full accolades for the term and was going to ask Barry for permission to do that in a private email. Since it's come out here, Barry, is it ok to use the term in the book with the accolades to you?
Absolutely. 🙂 Though I am not certain that I came up with the term first. I know that I had never heard it before I used it, but I have seen the term used since by others in contexts that led me to believe that it may have been in use before I thought of it.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 14, 2009 at 11:05 am
RBarryYoung (8/14/2009)
Wayne: Any chance we could get some Create Table defs & sample data Inserts?
See Paul's post... it's the same stuff as before (I think).
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2009 at 11:07 am
RBarryYoung (8/14/2009)
Jeff Moden (8/14/2009)
RBarryYoung (8/11/2009)
WayneS (8/11/2009)
...BTW, I think Jeff calls it the "pseudo-cursor". Isn't it Phil that calls it the "quirky update"?
Nah, that's me. Jeff hates the term "pseudo-cursor" and much prefer's Phil's "quirky update" terminology. 🙂
Heh... actually, that's not quite true. I used to prefer the term "Set Based Loops". I actually think that the term "pseudo-cursor" is quite elegant and applies to much more than just the "quirky update". In fact, I have an "Introdution to Pseudo-Cursors" in the book that I'm trying to write giving Barry full accolades for the term and was going to ask Barry for permission to do that in a private email. Since it's come out here, Barry, is it ok to use the term in the book with the accolades to you?
Absolutely. 🙂 Though I am not certain that I came up with the term first. I know that I had never heard it before I used it, but I have seen the term used since by others in contexts that led me to believe that it may have been in use before I thought of it.
Thanks and not a problem. Doesn't matter much to me where you heard it... you were the first person I ever heard use the term. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2009 at 11:08 am
Jeff Moden (8/14/2009)
RBarryYoung (8/14/2009)
Wayne: Any chance we could get some Create Table defs & sample data Inserts?See Paul's post... it's the same stuff as before (I think).
Oops, my bad. I wasn't looking for single-line Create Tables... 🙂
(fixed)
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 15, 2009 at 8:17 am
RBarryYoung (8/14/2009)
Jeff Moden (8/14/2009)
RBarryYoung (8/14/2009)
Wayne: Any chance we could get some Create Table defs & sample data Inserts?See Paul's post... it's the same stuff as before (I think).
Oops, my bad. I wasn't looking for single-line Create Tables... 🙂
(fixed)
Sorry for the delay in responding... major upgrade weekend in progress...
The latest code that I have, which uses the cursor that I want to get rid of, is at this post.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply