October 21, 2009 at 2:31 pm
I'm trying to assign a number sequence for a subset of data. Basically, we'll be getting, um, plants in where it's grouped by FileRef and where there can be up to 6 instances of different plants for the same FileRef. I thought this would be easy, but no matter what I do, I get a wide variety of incorrect data.
Here's my code:
DECLARE @FileID int
DECLARE @NTCID int
DECLARE @plant nvarchar(50)
DECLARE @ADDRESS1 nvarchar(250)
DECLARE @City1 nvarchar(250)
DECLARE @State1 nvarchar(250)
DECLARE @Zip1 nvarchar(250)
DECLARE @HomePhone1 nvarchar(250)
DECLARE @WorkPhone1 nvarchar(250)
DECLARE @SSN1 nvarchar(250)
Declare @plantNum as int
delete from tempAddress
drop table #temp
CREATE TABLE #temp(FileRef int
, NTCID int
,plantName nvarchar(50)
,Address nvarchar(250)
,City nvarchar(250)
,State nvarchar(250)
,Zip nvarchar(250)
,HomePhone nvarchar(250)
,WorkPhone nvarchar(250)
,SSN nvarchar(250))
INSERT into #temp(FileRef
, NTCID
,plantName
,Address
,City
,State
,Zip
,HomePhone
,WorkPhone
,SSN)
SELECT [File Ref#]
,[NTCID]
,[plant name]
,[Address]
,[City]
,[State]
,[Zip]
,[HomePhone]
,[WorkPhone]
,[SSN]
FROM [dbo].[qry_plant_Addresses]
while Exists(select * from #temp)
begin
INSERT INTO dbo.tempAddress(FileID, NTCID, plantID, plantName, ADDRESS1, City, State, Zip, HomePhone, WorkPhone, SSN)
SELECT TOP 1 [FileRef]
,[NTCID]
,1 as plantID
,[plantName]
,[Address]
,[City]
,[State]
,[Zip]
,[HomePhone]
,[WorkPhone]
,[SSN]
FROM #temp
delete from #temp
where #temp.FileRef = (Select FileID from dbo.tempAddress)and
#temp.NTCID = (Select NTCID from dbo.tempAddress)
if Exists(select * from #Temp where FileRef = (Select FileID from dbo.tempAddress))
continue
INSERT INTO dbo.tempAddress(FileID, NTCID, plantID, plantName, ADDRESS1, City, State, Zip, HomePhone, WorkPhone, SSN)
SELECT TOP 1 [FileRef]
,[NTCID]
,2 as plantID
,[plantName]
,[Address]
,[City]
,[State]
,[Zip]
,[HomePhone]
,[WorkPhone]
,[SSN]
FROM #temp
delete from #temp
where #temp.FileRef = (Select FileID from dbo.tempAddress)and
#temp.NTCID = (Select NTCID from dbo.tempAddress)
if Exists(select * from #Temp where FileRef = (Select FileID from dbo.tempAddress))
continue
INSERT INTO dbo.tempAddress(FileID, NTCID, plantID, plantName, ADDRESS1, City, State, Zip, HomePhone, WorkPhone, SSN)
SELECT TOP 1 [FileRef]
,[NTCID]
,3 as plantID
,[plantName]
,[Address]
,[City]
,[State]
,[Zip]
,[HomePhone]
,[WorkPhone]
,[SSN]
FROM #temp
delete from #temp
where #temp.FileRef = (Select FileID from dbo.tempAddress)and
#temp.NTCID = (Select NTCID from dbo.tempAddress)
if Exists(select * from #Temp where FileRef = (Select FileID from dbo.tempAddress))
continue
INSERT INTO dbo.tempAddress(FileID, NTCID, plantID, plantName, ADDRESS1, City, State, Zip, HomePhone, WorkPhone, SSN)
SELECT TOP 1 [FileRef]
,[NTCID]
,4 as plantID
,[plantName]
,[Address]
,[City]
,[State]
,[Zip]
,[HomePhone]
,[WorkPhone]
,[SSN]
FROM #temp
delete from #temp
where #temp.FileRef = (Select FileID from dbo.tempAddress)and
#temp.NTCID = (Select NTCID from dbo.tempAddress)
if Exists(select * from #Temp where FileRef = (Select FileID from dbo.tempAddress))
continue
INSERT INTO dbo.tempAddress(FileID, NTCID, plantID, plantName, ADDRESS1, City, State, Zip, HomePhone, WorkPhone, SSN)
SELECT TOP 1 [FileRef]
,[NTCID]
,5 as plantID
,[plantName]
,[Address]
,[City]
,[State]
,[Zip]
,[HomePhone]
,[WorkPhone]
,[SSN]
FROM #temp
delete from #temp
where #temp.FileRef = (Select FileID from dbo.tempAddress)and
#temp.NTCID = (Select NTCID from dbo.tempAddress)
if Exists(select * from #Temp where FileRef = (Select FileID from dbo.tempAddress))
continue
INSERT INTO dbo.tempAddress(FileID, NTCID, plantID, plantName, ADDRESS1, City, State, Zip, HomePhone, WorkPhone, SSN)
SELECT TOP 1 [FileRef]
,[NTCID]
,6 as plantID
,[plantName]
,[Address]
,[City]
,[State]
,[Zip]
,[HomePhone]
,[WorkPhone]
,[SSN]
FROM #temp
delete from #temp
where #temp.FileRef = (Select FileID from dbo.tempAddress)and
#temp.NTCID = (Select NTCID from dbo.tempAddress)
break
end
What happens is that the first record has a 1 assigned to it, but then the second record, instead of resetting back to 1 and then checking to see if there's another plant connected to the fileref, assigns 2 - 6 to the same record.
Any suggestions? Easier ways? All I'd like is:
fileref 12345 1 petunia.......
12346 1 petunia.....
12347 1 petunia...
12347 2 daisy.....
12348 1 petunia...
Thanks!
October 21, 2009 at 2:41 pm
Get rid of the whole loop and use the Row_Number() function. Partition by PlantID and you'll have a numeric sequence for each plant ID.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 22, 2009 at 6:05 am
Thanks! I went Doh! and hit my forehead when I saw your answer. Works great!:w00t:
October 22, 2009 at 7:09 am
You're welcome. Glad I could help.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply