March 12, 2013 at 6:16 am
Holy cow! That's brilliant and thank you. What I'm wondering, though, is if will bring the expected result, or if it matters. I'm not great at stepping through the logic on this, so I do need to make sure I understand it.
I need to be able to defend the process here to people who used to do this manually with paper (which is something I did myself last week just to get them the list already) as well as being able to adequately defend it to all the applicants, some of whom may inevitably complain.
The sequence of award is important for how we've done it in the past, but I'm not sure that it's important now. The way it was done in the past (and the way I was trying to do it this time) was the table got randomized. We started with the first record and subtracted one from the number of lots available for their first choice, then to the next record. This usually goes on for several records, each of the first ones getting their first choices. Then the first time we get to someone whose first choice is no longer available, that person gets their second choice awarded. And so you go down the list, if the first choice isn't available, and the second choice isn't available, then that person doesn't get anything and you move onto the next name. This goes on until all of the lots have been awarded and there are none left. Then you do the whole thing again for assigning the alternates (which are a different set of numbers).
So I'm just trying to 1) make sure I understand what the code you've written here does (so I can explain it, as I said), and 2) make sure we've got a legal leg to stand on if this way is different than what we've been doing. Also, I just don't always think about these things in the right ways. I've come to coding through several intermediary steps that haven't included much formal training. Most of what I know is self-taught through trial and error and largely in something of an emergency mode.
March 12, 2013 at 6:34 am
This proposed solution is slightly different.
Start by allocating punters picked at random, to pitches, based on punter's first choice. Using the sample set, most towns have all pitches allocated, one town has a few pitches left. All first choices which can be allocated have been, so we move on to second choices.
To help with this we prepare two new lists - remaining pitches for each town, and remaining punters. Then the same type of query is run again using second choice.
I think this could be very easily modified so that the punters are allocated sequentially until pitches are exhausted instead of at random. Give me 5 mins.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2013 at 6:42 am
Just wanted to add that I think this is likely to work out fine. I just need approval from my boss for this method.
March 12, 2013 at 6:53 am
This solution I belive matches your description:
IF OBJECT_ID('tempdb..#FirstChoice') IS NOT NULL DROP TABLE #FirstChoice;
IF OBJECT_ID('tempdb..#SecondChoice') IS NOT NULL DROP TABLE #SecondChoice;
IF OBJECT_ID('tempdb..#RemainingPitches') IS NOT NULL DROP TABLE #RemainingPitches;
IF OBJECT_ID('tempdb..#RemainingPunters') IS NOT NULL DROP TABLE #RemainingPunters;
IF OBJECT_ID('tempdb..#TheChosenOnes') IS NOT NULL DROP TABLE #TheChosenOnes
-- Fill a small pot from a big table of hopefuls
SELECT TOP(80) -- reduced size of pot to fit sample data!!!
rn = ROW_NUMBER() OVER (ORDER BY NEWID()),
FirewoodLottery_id,
Choice1,
Choice2
INTO #TheChosenOnes
FROM [survey].[FirewoodLottery]
ORDER BY NEWID()
-- Allocate as many first choices as possible
SELECT
Choice = 1,
t.FirewoodTowns_id,
t.town,
t.Permits,
PermitsLeft = t.Permits - COUNT(*) OVER(PARTITION BY t.town),
x.FirewoodLottery_id
INTO #FirstChoice
FROM survey.FirewoodTowns t
CROSS APPLY ( -- randomly pick folks for available pitches
SELECT TOP(t.Permits)
l.FirewoodLottery_id
FROM #TheChosenOnes l
WHERE l.Choice1 = t.town -- first choice
ORDER BY rn
) x
ORDER BY t.town;
-- What pitches remain after first allocation?
SELECT DISTINCT
t.FirewoodTowns_id,
t.town,
t.Permits,
PermitsLeft = ISNULL(PermitsLeft,t.Permits)
INTO #RemainingPitches
FROM survey.FirewoodTowns t
LEFT JOIN #FirstChoice f ON f.FirewoodTowns_id = t.FirewoodTowns_id
WHERE ISNULL(PermitsLeft,t.Permits) > 0
-- What remains of the chosen ones after the first allocation?
SELECT
l.rn,
l.FirewoodLottery_id,
l.Choice2
INTO #RemainingPunters
FROM #TheChosenOnes l
WHERE NOT EXISTS (SELECT 1 FROM #FirstChoice f WHERE f.FirewoodLottery_id = l.FirewoodLottery_id)
-- Allocate any remaining pitches as second choices
SELECT
Choice = 2,
t.FirewoodTowns_id,
t.town,
Permits = t.Permits,
PermitsLeft = t.PermitsLeft - COUNT(*) OVER(PARTITION BY t.town),
x.FirewoodLottery_id
INTO #SecondChoice
FROM #RemainingPitches t
CROSS APPLY ( -- randomly pick from remaining punters
SELECT TOP(t.PermitsLeft)
l.FirewoodLottery_id
FROM #RemainingPunters l
WHERE l.Choice2 = t.town -- second choice
ORDER BY rn
) x
ORDER BY t.town;
-- Results
SELECT *
FROM #FirstChoice
UNION ALL
SELECT *
FROM #SecondChoice
ORDER BY
FirewoodTowns_ID,
Choice,
FirewoodLottery_id;
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2013 at 6:57 am
I am completely flabbergasted. You just rock SO HARD! Thank you so much!
Actually, thank all of you so much. This has not only been helpful but I'm actually learning!
I feel like those words are insufficient but if I continue I'll become insanely effusive. So just, thanks.
March 12, 2013 at 7:13 am
Gosh :blush: thanks!
A gentle reminder - always always test 🙂 If this solution works, it can be condensed and tweaked for performance.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2013 at 7:25 am
Thanks for picking this up and seeing it through Chris. I got slammed yesterday afternoon and couldn't get back to this one.
_______________________________________________________________
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/
March 12, 2013 at 7:28 am
No worries mate. I had the day off yesterday and picked it up at home - when you hadn't posted up by this morning it was ready to go.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2013 at 7:50 am
ChrisM@Work (3/12/2013)
Gosh :blush: thanks!A gentle reminder - always always test 🙂 If this solution works, it can be condensed and tweaked for performance.
Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. 🙂 I'm still poking at it and trying to be sure I understand it before I try tweaking.
March 12, 2013 at 7:57 am
Siobhan Perricone (3/12/2013)
ChrisM@Work (3/12/2013)
Gosh :blush: thanks!A gentle reminder - always always test 🙂 If this solution works, it can be condensed and tweaked for performance.
Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. 🙂 I'm still poking at it and trying to be sure I understand it before I try tweaking.
Proper job! A number of folks around here have a line in their sig saying something like "don't use any code from the internet without first fully understanding how it works". If in doubt, just ask. It's quite straightforward stuff.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2013 at 8:06 am
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 12, 2013 at 8:31 am
TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.
Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it".
It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2013 at 8:36 am
Siobhan Perricone (3/12/2013)
Yeah, I'm testing now. Looks like it's missing on assigning 5 of the lots, so they're not all getting assigned. 🙂 I'm still poking at it and trying to be sure I understand it before I try tweaking.
Because of the random selection at the start of this process, you get different results every time the code is run. That's a real PITA because other folks can't see the same data as you and verify your assumptions. To get around it, here's a fixed set of 80 rows of "chosen ones" for folks to play with:
SELECT rn, FirewoodLottery_id, Choice1, Choice2
INTO #TheChosenOnes
FROM (
SELECT rn = 1, FirewoodLottery_id = 100, Choice1 = 'Shrewsbury', Choice2 = 'Shrewsbury' UNION ALL
SELECT 2, 6, 'Orange', 'Groton' UNION ALL
SELECT 3, 83, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 4, 12, 'Orange', 'Groton' UNION ALL
SELECT 5, 104, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 6, 4, 'North Duxbury', 'North Duxbury' UNION ALL
SELECT 7, 49, 'Groton', 'Groton' UNION ALL
SELECT 8, 27, 'Groton', 'Orange' UNION ALL
SELECT 9, 64, 'Johnson', 'Johnson' UNION ALL
SELECT 10, 29, 'Orange', 'Groton' UNION ALL
SELECT 11, 98, 'Plymouth', 'Shrewsbury' UNION ALL
SELECT 12, 117, 'Groton', 'Orange' UNION ALL
SELECT 13, 109, 'Groton', 'Orange' UNION ALL
SELECT 14, 86, 'North Duxbury', 'Shrewsbury' UNION ALL
SELECT 15, 45, 'Orange', 'Groton' UNION ALL
SELECT 16, 94, 'Westmore', 'Westmore' UNION ALL
SELECT 17, 62, 'Orange', 'Groton' UNION ALL
SELECT 18, 32, 'Groton', 'Orange' UNION ALL
SELECT 19, 56, 'Westmore', 'Westmore' UNION ALL
SELECT 20, 26, 'Westmore', 'Groton' UNION ALL
SELECT 21, 33, 'Groton', 'Orange' UNION ALL
SELECT 22, 120, 'Westmore', 'North Duxbury' UNION ALL
SELECT 23, 9, 'Westmore', 'Westmore' UNION ALL
SELECT 24, 2, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 25, 84, 'Johnson', 'North Duxbury' UNION ALL
SELECT 26, 65, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 27, 19, 'Westmore', 'Westmore' UNION ALL
SELECT 28, 89, 'Plymouth', 'Shrewsbury' UNION ALL
SELECT 29, 76, 'Johnson', 'Groton' UNION ALL
SELECT 30, 96, 'Groton', 'Orange' UNION ALL
SELECT 31, 59, 'Shrewsbury', 'Shrewsbury' UNION ALL
SELECT 32, 38, 'Orange', 'Groton' UNION ALL
SELECT 33, 17, 'Groton', 'Orange' UNION ALL
SELECT 34, 79, 'Johnson', 'North Duxbury' UNION ALL
SELECT 35, 116, 'Orange', 'Orange' UNION ALL
SELECT 36, 30, 'Westmore', 'Groton' UNION ALL
SELECT 37, 77, 'Groton', 'Johnson' UNION ALL
SELECT 38, 14, 'Shrewsbury', 'Shrewsbury' UNION ALL
SELECT 39, 20, 'Westmore', 'Westmore' UNION ALL
SELECT 40, 106, 'North Duxbury', 'Groton' UNION ALL
SELECT 41, 103, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 42, 105, 'Orange', 'Groton' UNION ALL
SELECT 43, 31, 'Groton', 'Orange' UNION ALL
SELECT 44, 92, 'Johnson', 'Johnson' UNION ALL
SELECT 45, 69, 'Westmore', 'Westmore' UNION ALL
SELECT 46, 118, 'Orange', 'Westmore' UNION ALL
SELECT 47, 51, 'Westmore', 'Westmore' UNION ALL
SELECT 48, 18, 'Westmore', 'Westmore' UNION ALL
SELECT 49, 7, 'Orange', 'Groton' UNION ALL
SELECT 50, 54, 'Westmore', 'Westmore' UNION ALL
SELECT 51, 75, 'Orange', 'Groton' UNION ALL
SELECT 52, 88, 'Orange', 'Groton' UNION ALL
SELECT 53, 99, 'Plymouth', 'Shrewsbury' UNION ALL
SELECT 54, 78, 'Groton', 'Orange' UNION ALL
SELECT 55, 113, 'Johnson', 'Johnson' UNION ALL
SELECT 56, 1, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 57, 95, 'North Duxbury', 'Westmore' UNION ALL
SELECT 58, 46, 'Groton', 'Groton' UNION ALL
SELECT 59, 73, 'North Duxbury', 'Johnson' UNION ALL
SELECT 60, 72, 'Westmore', 'Westmore' UNION ALL
SELECT 61, 110, 'Orange', 'Groton' UNION ALL
SELECT 62, 22, 'Johnson', 'North Duxbury' UNION ALL
SELECT 63, 97, 'North Duxbury', 'Roxbury' UNION ALL
SELECT 64, 115, 'Roxbury', 'Orange' UNION ALL
SELECT 65, 16, 'Groton', 'Orange' UNION ALL
SELECT 66, 11, 'North Duxbury', 'North Duxbury' UNION ALL
SELECT 67, 114, 'Westmore', 'Westmore' UNION ALL
SELECT 68, 58, 'Shrewsbury', 'Shrewsbury' UNION ALL
SELECT 69, 34, 'Westmore', 'Groton' UNION ALL
SELECT 70, 63, 'Johnson', 'Plymouth' UNION ALL
SELECT 71, 47, 'Roxbury', 'Orange' UNION ALL
SELECT 72, 74, 'Westmore', 'Westmore' UNION ALL
SELECT 73, 111, 'Orange', 'Orange' UNION ALL
SELECT 74, 43, 'Groton', 'Orange' UNION ALL
SELECT 75, 5, 'North Duxbury', 'Johnson' UNION ALL
SELECT 76, 91, 'Shrewsbury', 'Plymouth' UNION ALL
SELECT 77, 101, 'Shrewsbury', 'Shrewsbury' UNION ALL
SELECT 78, 102, 'Plymouth', 'Shrewsbury' UNION ALL
SELECT 79, 80, 'Westmore', 'Groton' UNION ALL
SELECT 80, 107, 'Westmore', 'Westmore'
) d
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 12, 2013 at 8:49 am
ChrisM@Work (3/12/2013)
TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it".
It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.
Getting a bit off-topic here, but:
A) I can probably count on two hands the number of times in 15 years of consulting on SQL Server I have seen cases where an index on a temp table is, overall, beneficial to the whole process.
B) Speaking of "whole process", I am unclear how doing a SORT before inserting and then another (likely less costly but still work) SORT for a clustered index build could be more efficient than just doing one sort for building the CI. And again I will state that if you are hitting the table ONCE (which is the case in the VAST majority of times in my experience) then the index is almost certainly work for no benefit. :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
March 12, 2013 at 9:02 am
TheSQLGuru (3/12/2013)
ChrisM@Work (3/12/2013)
TheSQLGuru (3/12/2013)
One minor point ChrisM - never do a SELECT INTO... with an ORDER BY. No benefit there.Thanks for the reminder, Kevin. I wouldn't say "never" however, I'd say "don't rely on it".
It can make a difference if you run a ton of rows into a temp table then cluster on the ORDER BY column - the clustered index can be created more quickly than without the ORDER BY. This may be an edge case in most shops but it's common in marketing and nowadays I always test both cases, with and without the ORDER BY, and pick whichever version is quickest. That means emulating as closely as possible the code block (query and cluster creation at least and often a few prior steps) as it would be run in prod to account for cacheing. Not always trivial but frequently worth the effort.
Getting a bit off-topic here, but:
A) I can probably count on two hands the number of times in 15 years of consulting on SQL Server I have seen cases where an index on a temp table is, overall, beneficial to the whole process.
B) Speaking of "whole process", I am unclear how doing a SORT before inserting and then another (likely less costly but still work) SORT for a clustered index build could be more efficient than just doing one sort for building the CI. And again I will state that if you are hitting the table ONCE (which is the case in the VAST majority of times in my experience) then the index is almost certainly work for no benefit. :hehe:
We'll have to agree to disagree for now because I can't fault the sense of your words. I time virtually everything I write and have done for quite a while. You can tell if I've written a query because it's likely to have something like this after it;
-- (24611 row(s) affected) / 00:00:01😉
I'd love to have enough time to set up a decent test to demonstrate what I'm seeing, but it won't happen this week.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 15 posts - 16 through 30 (of 38 total)
You must be logged in to reply to this topic. Login to reply