Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««1234»»»

Looping without cursor not updating properly Expand / Collapse
Author
Message
Posted Monday, March 11, 2013 10:11 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20
I apologize for my fumbling attempts, I've never tried to do anything like this before, and several of the commands are unfamiliar to me. Here is another try at the dll. I have one problem in that it won't populate the sample data and I don't know why it won't. It throws this error: Msg 205, Level 16, State 1, Line 7
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

I've double checked (and fixed some problems). When I count the fields in the insert, compared to those in the creates and the sample data lists, they all appear to be in agreement as to the number of fields.

Use TestingSetupTemp
go

CREATE SCHEMA survey AUTHORIZATION dbo

-- 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,
[email] [varchar](60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[postdate] [datetime] NULL,
[ip] [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]
go

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]
go
-- creating the stored procedures referenced

CREATE PROCEDURE [survey].[sp_GetFirewoodLotteryTowns]
AS
BEGIN

Select Town, Permits, alternates, PermitsCountdown, AlternatesCountdown
from survey.FirewoodTowns
END
go

CREATE PROCEDURE [survey].[sp_GetRandomizedApplicants]
AS
BEGIN

select TOP 800 FirewoodLottery_id
,choice1
,choice2
FROM survey.FirewoodLottery
ORDER BY NEWID()

END
go

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
go

-- set identity insert on for lottery

SET IDENTITY_INSERT survey.FirewoodLottery ON

-- entering sample data into lottery table
INSERT INTO [survey].[FirewoodLottery]
( [FirewoodLottery_ID]
,[Firstname]
,[MiddleInitial]
,[Lastname]
,[Suffix]
,[address]
,[city]
,[state]
,[zip]
,[phone]
,[email]
,[postdate]
,[ip]
,[choice1]
,[choice2]
,[assigned]
,[status]
,[District]
,[sortindex]
,[choice]
,[Notes]
,[possible_dup])
select 1,'Wombat',NULL,'Champine',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union all
select 2,'Wombat',NULL,'Beaudry',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 3,'Wombat',NULL,'Couture',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 4,'Wombat',NULL,'VanHorn',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 5,'Wombat',NULL,'Roach',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 6,'Wombat',NULL,'Relation',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 7,'Wombat',NULL,'LaRose',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 8,'Wombat',NULL,'Deuso',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 9,'Wombat',NULL,'Valley',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 10,'Wombat',NULL,'Beaudoin',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 11,'Wombat',NULL,'VanHorn',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 12,'Wombat',NULL,'Robinson',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 13,'Wombat',NULL,'Green',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 14,'Wombat',NULL,'Wheeler',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 15,'Wombat',NULL,'Trombley',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 16,'Wombat',NULL,'Trombley',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 17,'Wombat',NULL,'Compo',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 18,'Wombat',NULL,'Kipp',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 19,'Wombat',NULL,'Auger',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 20,'Wombat',NULL,'Auger',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 21,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 22,'Wombat',NULL,'Morse',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 23,'Wombat',NULL,'Morse',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 24,'Wombat',NULL,'Beauchemin',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 25,'Wombat',NULL,'Curtis',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 26,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 27,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 28,'Wombat',NULL,'Duke',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 29,'Wombat',NULL,'Gerrish',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 30,'Wombat',NULL,'charron',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 31,'Wombat',NULL,'Smith',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 32,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 33,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 34,'Wombat',NULL,'charron',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 35,'Wombat',NULL,'charron',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 36,'Wombat',NULL,'West',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 37,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 38,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 39,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 40,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 41,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 42,'Wombat',NULL,'Moser',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 43,'Wombat',NULL,'beyor',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union all
select 44,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 45,'Wombat',NULL,'Tighe',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 46,'Wombat',NULL,'Abare',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 47,'Wombat',NULL,'Willette',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 48,'Wombat',NULL,'Hinman',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 49,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 50,'Wombat',NULL,'Ware',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 51,'Wombat',NULL,'Ware',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 52,'Wombat',NULL,'Hackett',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 53,'Wombat',NULL,'Drinkwater',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 54,'Wombat',NULL,'Hale',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 55,'Wombat',NULL,'John',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 56,'Wombat',NULL,'John',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 57,'Wombat',NULL,'John',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 58,'Wombat',NULL,'alexander',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 59,'Wombat',NULL,'rogers',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 60,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 61,'Wombat',NULL,'Celley',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 62,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 63,'Wombat',NULL,'Wilson',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 64,'Wombat',NULL,'Tuthill',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 65,'Wombat',NULL,'hall',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 66,'Wombat',NULL,'Habel',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 67,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 68,'Wombat',NULL,'Hudson',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 69,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 70,'Wombat',NULL,'Paige',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 71,'Wombat',NULL,'Brown',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 72,'Wombat',NULL,'Rhodes',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 73,'Wombat',NULL,'Wyckoff',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 74,'Wombat',NULL,'Rhodes',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 75,'Wombat',NULL,'LaCasse',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 76,'Wombat',NULL,'Chase',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 77,'Wombat',NULL,'keene',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 78,'Wombat',NULL,'Huff',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 79,'Wombat',NULL,'Irwin',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 80,'Wombat',NULL,'Conway',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 81,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 82,'Wombat',NULL,'Webster',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 83,'Wombat',NULL,'Garrow',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 84,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 85,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 86,'Wombat',NULL,'LeBlanc',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 87,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 88,'Wombat',NULL,'Anderson',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 89,'Wombat',NULL,'Couch',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 90,'Wombat',NULL,'Gove',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 91,'Wombat',NULL,'Grass',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 92,'Wombat',NULL,'Domina',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 93,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 94,'Wombat',NULL,'Royer',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 95,'Wombat',NULL,'Wheeler',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 96,'Wombat',NULL,'Lapan',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 97,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 98,'Wombat',NULL,'Deutschbein',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 99,'Wombat',NULL,'maston',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 100,'Wombat',NULL,'Teer',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union all
select 101,'Wombat',NULL,'Teer',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union all
select 102,'Wombat',NULL,'Inman',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 103,'Wombat',NULL,'Teer',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 104,'Wombat',NULL,'Teer',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL,NULL union all
select 105,'Wombat',NULL,'Putney',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 106,'Wombat',NULL,'Russo',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 107,'Wombat',NULL,'Davis',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 108,'Wombat',NULL,'Smith',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 109,'Wombat',NULL,'Bancroft',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 110,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 111,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 112,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 113,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 114,'Wombat',NULL,'Demar',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 115,'Wombat',NULL,'beauchemin',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 116,'Wombat',NULL,'Doyon',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 117,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 118,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 119,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL union all
select 120,'Wombat',NULL,'Fitzpatrick',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL


-- set identity insert on for towns
SET IDENTITY_INSERT survey.FirewoodTowns ON

-- entering sample data into lottery table
INSERT INTO [survey].[FirewoodTowns]
([FirewoodTowns_id]
,[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

Post #1429373
Posted Monday, March 11, 2013 10:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 13,138, Visits: 11,977
Well that was close. There were still a few issues but I was able to muddle my way through. (the insert issue was the very last one on the list had too many NULLs at the end.

Now that I could actually see what was going on this whole procedure can be made about a million times more simple.

Basically all you were doing was getting all the towns, then looping through them one at a time and updating the PermitsCountdown to the value in Permits where the PermitsCountdown <> Permits. You can do this in a single update statement with no need at all for a cursor.

Unless I am missing something I think you could change your entire sp_ResetPermitsCountdown proc to be as simple as:

CREATE PROCEDURE [survey].[sp_ResetPermitsCountdown] AS 
update [survey].[FirewoodTowns]
set PermitsCountdown = Permits
where PermitsCountdown <> Permits

Also, you really should not use the "sp_" prefix for your procedure names. SQL server will first think is a system stored proc.



_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1429395
Posted Monday, March 11, 2013 11:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20
That's great and I'll take a look at your suggested change.

However the real problem I'm having is the procedure where I'm trying to award the lots. That's in a separate post. You can find the first one I did, without cursors, in my first post. You can find the version of it I did with cursors, in my uhm, third? post.

The stuff I just posted was the setup for being able to try out the other procedure.
Post #1429412
Posted Tuesday, March 12, 2013 3:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 7,133, Visits: 13,520
Here's a working dataload:

-- set identity insert on for lottery
SET IDENTITY_INSERT survey.FirewoodLottery ON

-- entering sample data into lottery table
INSERT INTO [survey].[FirewoodLottery]
(FirewoodLottery_id --DateValue, Value, YearValue, Monthvalue,
,[Firstname]
,[MiddleInitial]
,[Lastname]
,[Suffix]
,[address]
,[city]
,[state]
,[zip]
,[phone]
,[email]
,[postdate]
,[ip]
,[choice1]
,[choice2]
,[assigned]
,[status]
,[District]
,[sortindex]
,[choice]
,[Notes]
--,[possible_dup]
)
select 1,'Wombat',NULL,'Champine',NULL,'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',
NULL,'Dupe',NULL,NULL,NULL,NULL UNION all
select 2,'Wombat',NULL,'Beaudry',NULL,'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',
NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 3,'Wombat',NULL,'Couture',NULL,'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',
NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 4,'Wombat',NULL,'VanHorn',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 5,'Wombat',NULL,'Roach',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 6,'Wombat',NULL,'Relation',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 7,'Wombat',NULL,'LaRose',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 8,'Wombat',NULL,'Deuso',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 9,'Wombat',NULL,'Valley',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 10,'Wombat',NULL,'Beaudoin',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 11,'Wombat',NULL,'VanHorn',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 12,'Wombat',NULL,'Robinson',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 13,'Wombat',NULL,'Green',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 14,'Wombat',NULL,'Wheeler',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 15,'Wombat',NULL,'Trombley',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 16,'Wombat',NULL,'Trombley',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 17,'Wombat',NULL,'Compo',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 18,'Wombat',NULL,'Kipp',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 19,'Wombat',NULL,'Auger',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 20,'Wombat',NULL,'Auger',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 21,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 22,'Wombat',NULL,'Morse',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 23,'Wombat',NULL,'Morse',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 24,'Wombat',NULL,'Beauchemin',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 25,'Wombat',NULL,'Curtis',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 26,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 27,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 28,'Wombat',NULL,'Duke',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 29,'Wombat',NULL,'Gerrish',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 30,'Wombat',NULL,'charron',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 31,'Wombat',NULL,'Smith',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 32,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 33,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 34,'Wombat',NULL,'charron',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 35,'Wombat',NULL,'charron',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 36,'Wombat',NULL,'West',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 37,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 38,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 39,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 40,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 41,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 42,'Wombat',NULL,'Moser',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 43,'Wombat',NULL,'beyor',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL UNION all
select 44,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 45,'Wombat',NULL,'Tighe',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 46,'Wombat',NULL,'Abare',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 47,'Wombat',NULL,'Willette',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 48,'Wombat',NULL,'Hinman',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 49,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 50,'Wombat',NULL,'Ware',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 51,'Wombat',NULL,'Ware',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 52,'Wombat',NULL,'Hackett',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 53,'Wombat',NULL,'Drinkwater',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 54,'Wombat',NULL,'Hale',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 55,'Wombat',NULL,'John',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 56,'Wombat',NULL,'John',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 57,'Wombat',NULL,'John',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 58,'Wombat',NULL,'alexander',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 59,'Wombat',NULL,'rogers',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 60,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 61,'Wombat',NULL,'Celley',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 62,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 63,'Wombat',NULL,'Wilson',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 64,'Wombat',NULL,'Tuthill',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 65,'Wombat',NULL,'hall',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 66,'Wombat',NULL,'Habel',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 67,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 68,'Wombat',NULL,'Hudson',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 69,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 70,'Wombat',NULL,'Paige',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 71,'Wombat',NULL,'Brown',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 72,'Wombat',NULL,'Rhodes',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 73,'Wombat',NULL,'Wyckoff',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 74,'Wombat',NULL,'Rhodes',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 75,'Wombat',NULL,'LaCasse',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 76,'Wombat',NULL,'Chase',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 77,'Wombat',NULL,'keene',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 78,'Wombat',NULL,'Huff',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 79,'Wombat',NULL,'Irwin',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 80,'Wombat',NULL,'Conway',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 81,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 82,'Wombat',NULL,'Webster',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 83,'Wombat',NULL,'Garrow',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 84,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 85,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 86,'Wombat',NULL,'LeBlanc',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 87,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 88,'Wombat',NULL,'Anderson',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 89,'Wombat',NULL,'Couch',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 90,'Wombat',NULL,'Gove',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 91,'Wombat',NULL,'Grass',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 92,'Wombat',NULL,'Domina',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 93,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 94,'Wombat',NULL,'Royer',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 95,'Wombat',NULL,'Wheeler',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 96,'Wombat',NULL,'Lapan',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 97,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 98,'Wombat',NULL,'Deutschbein',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 99,'Wombat',NULL,'maston',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 100,'Wombat',NULL,'Teer',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL UNION all
select 101,'Wombat',NULL,'Teer',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL UNION all
select 102,'Wombat',NULL,'Inman',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 103,'Wombat',NULL,'Teer',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 104,'Wombat',NULL,'Teer',NULL,'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',NULL,'Dupe',NULL,NULL,NULL,NULL UNION all
select 105,'Wombat',NULL,'Putney',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 106,'Wombat',NULL,'Russo',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 107,'Wombat',NULL,'Davis',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 108,'Wombat',NULL,'Smith',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 109,'Wombat',NULL,'Bancroft',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 110,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 111,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 112,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 113,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 114,'Wombat',NULL,'Demar',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 115,'Wombat',NULL,'beauchemin',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 116,'Wombat',NULL,'Doyon',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 117,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 118,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 119,'Wombat',NULL,'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',NULL,NULL,NULL,NULL,NULL,NULL UNION all
select 120,'Wombat',NULL,'Fitzpatrick',NULL,'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',
NULL,NULL,NULL,NULL,NULL,NULL

SET IDENTITY_INSERT survey.FirewoodLottery OFF

-- set identity insert on for towns
SET IDENTITY_INSERT survey.FirewoodTowns ON

-- entering sample data into lottery table
INSERT INTO [survey].[FirewoodTowns]
([FirewoodTowns_id]
,[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

SET IDENTITY_INSERT survey.FirewoodTowns Off





“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429639
Posted Tuesday, March 12, 2013 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 7,133, Visits: 13,520
Here's a proposed solution. I've broken it up into stages using #temp tables which can be read for the purpose of verifying the results.

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(800)
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 NEWID()
) 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.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 NEWID()
) x
ORDER BY t.town;

-- Results
SELECT *
FROM #FirstChoice
UNION ALL
SELECT *
FROM #SecondChoice
ORDER BY
FirewoodTowns_ID,
Choice,
FirewoodLottery_id;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429645
Posted Tuesday, March 12, 2013 6:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20
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.

Post #1429731
Posted Tuesday, March 12, 2013 6:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 7,133, Visits: 13,520
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.


“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429744
Posted Tuesday, March 12, 2013 6:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20
Just wanted to add that I think this is likely to work out fine. I just need approval from my boss for this method.
Post #1429750
Posted Tuesday, March 12, 2013 6:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 7,133, Visits: 13,520
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;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
Post #1429758
Posted Tuesday, March 12, 2013 6:57 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, March 12, 2013 11:58 AM
Points: 14, Visits: 20
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.
Post #1429764
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse