Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Looping without cursor not updating properly
Add to briefcase
39 posts, Page 2 of 4
««1234»»»

Looping without cursor not updating properly Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Siobhan Perricone
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
Sean Lange
Posted Monday, March 11, 2013 10:40 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: 2 days ago @ 8:46 AM
Points: 8,547, Visits: 8,204
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
Post #1429395
Siobhan Perricone
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
ChrisM@Work
Posted Tuesday, March 12, 2013 3:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950
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
ChrisM@Work
Posted Tuesday, March 12, 2013 3:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950
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
Siobhan Perricone
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
ChrisM@Work
Posted Tuesday, March 12, 2013 6:34 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950
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
Siobhan Perricone
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
ChrisM@Work
Posted Tuesday, March 12, 2013 6:53 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 8:21 AM
Points: 5,602, Visits: 10,950
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
Siobhan Perricone
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
39 posts, Page 2 of 4
««1234»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.