Here's my attempt at providing enough information so you can help. 🙂
I wasn't sure how to give you all the necessary code, so I'm dropping it all in at the end of my description here. I also, after I'd posted last week, gone back and tried redoing this as a cursor in the hope that it was simply my unfamiliarity that might be at issue, and I'm having the same problems even with the cursor. Since I've already posted my first attempt without the cursor above, I'll only add the second attempt with the cursor after all the set up code at the bottom.
Every year there's a lottery held to award the right to harvest on state-owned firewood lots to citizens. There is a web application they apply through (I did not write it, I didn't design any of this, I inherited it when a coworker suddenly passed away last year). The old code was using cold fusion and Microsoft Access with just two tables on an SQL Backend. I'm trying to update this so that I don't have to go through the laborious Access process my predecessor had set up without having to rewrite everything he did (we're not replacing the cold fusion, yet for instance, it works well enough for now). So the task at hand is to set up the code that will award the lots appropriately.
There are a specific number of lots awarded in different towns. The towns and the numbers of lots available changes every year. There are also specific numbers of alternates awarded afterwards. I was intending to simply create a second process the mirrored the first to do the alternate awards. I'm hoping that the comments in the code will make it clear what the process is, but I'm concerned that I might be bogging this down, so if you need me to outline the lottery procedure, I can do that, too, just let me know.
-- creating the two tables
CREATE TABLE [survey].[FirewoodLottery](
[FirewoodLottery_id] [int] IDENTITY(1,1) NOT NULL,
[Firstname] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MiddleInitial] [varchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Lastname] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Suffix] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[address] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[city] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[state] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[zip] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[phone] [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postdate] [datetime] NULL,
127.0.0.1 [varchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[choice1] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[choice2] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[assigned] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[status] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[District] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[sortindex] [int] NULL,
[choice] [int] NULL,
[Notes] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[possible_dup] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
CREATE TABLE [survey].[FirewoodTowns](
[FirewoodTowns_id] [int] IDENTITY(1,1) NOT NULL,
[Town] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[District] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Permits] [int] NULL,
[Forest] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[alternates] [int] NULL,
[PermitsCountdown] [int] NULL CONSTRAINT [DF_FirewoodTowns_PermitsCountdown] DEFAULT ((0)),
[AlternatesCountdown] [int] NULL CONSTRAINT [DF_FirewoodTowns_AlternatesCountdown] DEFAULT ((0))
) ON [PRIMARY]
-- creating the stored procedures referenced
CREATE PROCEDURE [survey].[sp_GetFirewoodLotteryTowns]
AS
BEGIN
Select Town, Permits, alternates, PermitsCountdown, AlternatesCountdown
from survey.FirewoodTowns
END
CREATE PROCEDURE [survey].[sp_GetRandomizedApplicants]
AS
BEGIN
select TOP 800 FirewoodLottery_id
,choice1
,choice2
FROM survey.FirewoodLottery
ORDER BY NEWID()
END
CREATE PROCEDURE [survey].[sp_ResetPermitsCountdown]
AS
BEGIN
Declare @LotteryTownName as varchar(40)
Declare @NumberOfPermits as int
Declare @NumberOfAlternates as int
Declare @CountdownOfPermits as int
Declare @CountdownOfAlternates as int
Declare @LoopCounter as int
Declare @RecordCount as int
set nocount on
create table #LotteryTowns (
Town varchar(40),
Permits int,
Alternates int,
PermitsCountdown int,
AlternatesCountdown int
)
INSERT INTO #LotteryTowns exec survey.sp_GetFirewoodLotteryTowns
select @RecordCount = count(town) from #LotteryTowns
declare cur cursor
for
select Town, Permits, Alternates, PermitsCountdown, AlternatesCountdown
from #LotteryTowns
open cur
set @LoopCounter = 0
/* Start loop here */
while @LoopCounter < @RecordCount
begin
fetch next from cur into @LotteryTownName, @NumberOfPermits, @NumberOfAlternates, @CountdownOfPermits, @CountdownOfAlternates;
if @CountdownOfPermits <> @NumberOfPermits
update [survey].[FirewoodTowns]
set PermitsCountdown = @NumberOfPermits
where Town = @LotteryTownName
SET @LoopCounter = @LoopCounter + 1;
/* End loop here */
end
close cur
deallocate cur
drop table #LotteryTowns
END
-- set identity insert on for lottery
SET IDENTITY_INSERT survey.FirewoodLottery ON
-- entering sample data into lottery table
INSERT INTO [survey].[FirewoodLottery]
(ID, DateValue, Value, YearValue, Monthvalue)
[Firstname]
,[MiddleInitial]
,[Lastname]
,[Suffix]
,[address]
,[city]
,[state]
,[zip]
,[phone]
,
,[postdate]
,127.0.0.1
,[choice1]
,[choice2]
,[assigned]
,[status]
,[District]
,[sortindex]
,[choice]
,[Notes]
,[possible_dup]
select 1,'Wombat',,'Champine',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,'Dupe',,,,,union all
select 2,'Wombat',,'Beaudry',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 3,'Wombat',,'Couture',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 4,'Wombat',,'VanHorn',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union all
select 5,'Wombat',,'Roach',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Johnson',,,,,,,union all
select 6,'Wombat',,'Relation',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 7,'Wombat',,'LaRose',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 8,'Wombat',,'Deuso',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','North Duxbury',,,,,,,union all
select 9,'Wombat',,'Valley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 10,'Wombat',,'Beaudoin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 11,'Wombat',,'VanHorn',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union all
select 12,'Wombat',,'Robinson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 13,'Wombat',,'Green',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 14,'Wombat',,'Wheeler',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union all
select 15,'Wombat',,'Trombley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 16,'Wombat',,'Trombley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 17,'Wombat',,'Compo',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 18,'Wombat',,'Kipp',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 19,'Wombat',,'Auger',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 20,'Wombat',,'Auger',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 21,'Wombat',,'Elliott','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Westmore',,,,,,,union all
select 22,'Wombat',,'Morse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union all
select 23,'Wombat',,'Morse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union all
select 24,'Wombat',,'Beauchemin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union all
select 25,'Wombat',,'Curtis',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','North Duxbury',,,,,,,union all
select 26,'Wombat',,'charron','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 27,'Wombat',,'Barnett','IV','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 28,'Wombat',,'Duke',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 29,'Wombat',,'Gerrish',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 30,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 31,'Wombat',,'Smith',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 32,'Wombat',,'Gonyaw','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 33,'Wombat',,'Gonyaw','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 34,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 35,'Wombat',,'charron',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 36,'Wombat',,'West',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 37,'Wombat',,'Gerrish','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 38,'Wombat',,'Clark','G','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 39,'Wombat',,'Palmer','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 40,'Wombat',,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 41,'Wombat',,'Anderson','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 42,'Wombat',,'Moser',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union all
select 43,'Wombat',,'beyor',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,'Dupe',,,,,union all
select 44,'Wombat',,'richart','mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Roxbury',,,,,,,union all
select 45,'Wombat',,'Tighe',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 46,'Wombat',,'Abare',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 47,'Wombat',,'Willette',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union all
select 48,'Wombat',,'Hinman',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 49,'Wombat',,'Barnett','111','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 50,'Wombat',,'Ware',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 51,'Wombat',,'Ware',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 52,'Wombat',,'Hackett',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 53,'Wombat',,'Drinkwater',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 54,'Wombat',,'Hale',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 55,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 56,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 57,'Wombat',,'John',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 58,'Wombat',,'alexander',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union all
select 59,'Wombat',,'rogers',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,,,,,,union all
select 60,'Wombat',,'Webster','Jr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Johnson',,,,,,,union all
select 61,'Wombat',,'Celley',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 62,'Wombat',,'Gerrish','Sr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 63,'Wombat',,'Wilson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Plymouth',,,,,,,union all
select 64,'Wombat',,'Tuthill',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union all
select 65,'Wombat',,'hall',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 66,'Wombat',,'Habel',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Roxbury',,,,,,,union all
select 67,'Wombat',,'Barnett','Jean','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 68,'Wombat',,'Hudson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Orange',,,,,,,union all
select 69,'Wombat',,'Carleton','L','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 70,'Wombat',,'Paige',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 71,'Wombat',,'Brown',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 72,'Wombat',,'Rhodes',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 73,'Wombat',,'Wyckoff',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Johnson',,,,,,,union all
select 74,'Wombat',,'Rhodes',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 75,'Wombat',,'LaCasse',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 76,'Wombat',,'Chase',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Groton',,,,,,,union all
select 77,'Wombat',,'keene',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Johnson',,,,,,,union all
select 78,'Wombat',,'Huff',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 79,'Wombat',,'Irwin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union all
select 80,'Wombat',,'Conway',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Groton',,,,,,,union all
select 81,'Wombat',,'page','d','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','North Duxbury',,,,,,,union all
select 82,'Wombat',,'Webster',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 83,'Wombat',,'Garrow',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 84,'Wombat',,'Roy','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','North Duxbury',,,,,,,union all
select 85,'Wombat',,'Gifford','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Westmore',,,,,,,union all
select 86,'Wombat',,'LeBlanc',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Shrewsbury',,,,,,,union all
select 87,'Wombat',,'Quintin','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 88,'Wombat',,'Anderson',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 89,'Wombat',,'Couch',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 90,'Wombat',,'Gove',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Groton',,,,,,,union all
select 91,'Wombat',,'Grass',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 92,'Wombat',,'Domina',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union all
select 93,'Wombat',,'Barney','Jr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 94,'Wombat',,'Royer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 95,'Wombat',,'Wheeler',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Westmore',,,,,,,union all
select 96,'Wombat',,'Lapan',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 97,'Wombat',,'Martell','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Roxbury',,,,,,,union all
select 98,'Wombat',,'Deutschbein',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 99,'Wombat',,'maston',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 100,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,'Dupe',,,,,union all
select 101,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Shrewsbury',,'Dupe',,,,,union all
select 102,'Wombat',,'Inman',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 103,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,,,,,,union all
select 104,'Wombat',,'Teer',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Shrewsbury','Plymouth',,'Dupe',,,,,union all
select 105,'Wombat',,'Putney',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 106,'Wombat',,'Russo',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','North Duxbury','Groton',,,,,,,union all
select 107,'Wombat',,'Davis',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 108,'Wombat',,'Smith',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union all
select 109,'Wombat',,'Bancroft',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 110,'Wombat',,'Nolan','none','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Groton',,,,,,,union all
select 111,'Wombat',,'LeBlanc','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Orange',,,,,,,union all
select 112,'Wombat',,'LeBlanc','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Orange',,,,,,,union all
select 113,'Wombat',,'follert','k','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Johnson','Johnson',,,,,,,union all
select 114,'Wombat',,'Demar',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','Westmore',,,,,,,union all
select 115,'Wombat',,'beauchemin',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Roxbury','Orange',,,,,,,union all
select 116,'Wombat',,'Doyon',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Orange',,,,,,,union all
select 117,'Wombat',,'Beyor','Mrs.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Groton','Orange',,,,,,,union all
select 118,'Wombat',,'Bearce','Mr','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Orange','Westmore',,,,,,,union all
select 119,'Wombat',,'Millard','Mr.','15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Plymouth','Shrewsbury',,,,,,,union all
select 120,'Wombat',,'Fitzpatrick',,'15 Some Random Road','Small Town','VT','11111','802-123-4567','someone@somewhere.com',1/2/2013 0:00:00,'111.11.111.11','Westmore','North Duxbury',,,,,,,,
select
-- set identity insert on for towns
SET IDENTITY_INSERT survey.FirewoodTowns ON
-- entering sample data into lottery table
INSERT INTO [survey].[FirewoodTowns]
([Town]
,[District]
,[Permits]
,[Forest]
,[alternates]
,[PermitsCountdown]
,[AlternatesCountdown)
select 14,'Plymouth','1',15,'Coolidge SF',5,15,0 union all
select 15,'Shrewsbury','2',8,'Coolidge SF',5,8,0 union all
select 16,'North Duxbury','3',10,'Camels Hump SF',10,10,0 union all
select 17,'Orange','4',10,'Groton SF',0,0,0 union all
select 18,'Roxbury','4',3,'Roxbury SF',0,3,0 union all
select 19,'Johnson','4',5,'Mt Mansfield SF',0,5,0 union all
select 20,'Groton','5',15,'Groton SF',10,0,0 union all
select 21,'Westmore','5',15,'Willoughby SF',10,15,0
Here's with the cursor:
CREATE PROCEDURE [survey].[sp_AwardFirewoodLots]
AS
BEGIN
Declare @ApplicantID as INT
Declare @TownCount as INT
Declare @PermitsCount as INT
Declare @firstchoice as NCHAR(50)
Declare @SecondChoice as NCHAR(50)
Declare @LoopCounter as int
Declare @RecordCount as int
set nocount on
exec [survey].[sp_ResetPermitsCountdown]
create table #RandomizedApplicants (
FirewoodLottery_id int,
Choice1 varchar(40),
Choice2 varchar(40)
)
INSERT INTO #RandomizedApplicants exec survey.[sp_GetRandomizedApplicants]
select @RecordCount = count(FirewoodLottery_id) from #RandomizedApplicants
declare cur cursor
for
select FirewoodLottery_id, choice1, choice2
from #RandomizedApplicants
open cur
set @LoopCounter = 0
/* Start loop here */
while @LoopCounter < @RecordCount
begin
fetch next from cur into @ApplicantID, @firstchoice, @SecondChoice;
SELECT @PermitsCount = [PermitsCountdown]
FROM survey.FirewoodTowns
WHERE [Town] = @firstchoice
IF @PermitsCount <> 0
BEGIN
UPDATE survey.FirewoodLottery
SET assigned = @firstchoice
,notes = 'Won First Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101)
WHERE FirewoodLottery_id = @ApplicantID
SELECT @TownCount = @PermitsCount - 1
UPDATE survey.FirewoodTowns
SET PermitsCountdown = @TownCount
WHERE Town = @firstchoice
END
ELSE
BEGIN
-- first choice is full so figure out if second choice is full
SELECT @PermitsCount = [PermitsCountdown]
FROM survey.FirewoodTowns
WHERE [Town] = @SecondChoice
IF @PermitsCount <> 0
BEGIN
SELECT @TownCount = @PermitsCount - 1
UPDATE survey.FirewoodTowns
SET PermitsCountdown = @TownCount
WHERE Town = @SecondChoice
UPDATE survey.FirewoodLottery
SET assigned = @SecondChoice
,notes = 'Won Second Choice ' + CONVERT(VARCHAR(15), GETDATE(), 101)
WHERE FirewoodLottery_id = @ApplicantID
END
ELSE
BEGIN
-- report that both choices are full
UPDATE survey.FirewoodLottery
SET assigned = 'Nothing'
,notes = 'Both Choices Full ' + CONVERT(VARCHAR(15), GETDATE(), 101)
WHERE FirewoodLottery_id = @ApplicantID
END
SET @LoopCounter = @LoopCounter + 1;
/* End loop here */
end
close cur
deallocate cur
drop table #RandomizedApplicants
end
END