October 7, 2010 at 3:43 pm
WayneS (10/7/2010)
Here's some code.Note that I kept in the prefix checking... I need to strip it out anyway, so I just kept it in.
-- need to add some columns to the table.ALTER TABLE gaps2 ADD Prefix varchar(255),
GroupNo int;
-- change the controlnumber to not null and put a primary key on it.
ALTER TABLE gaps2 ALTER COLUMN controlnumber varchar(255) NOT NULL;
ALTER TABLE gaps2 ADD PRIMARY KEY CLUSTERED (controlnumber);
-- declare and initialize some variables for the update statement.
declare @Prefix varchar(255),
@GroupNo int,
@ControlNumber varchar(255),
@LastNumber int;
set @GroupNo = 0;
set @Prefix = '';
set @ControlNumber = '';
set @LastNumber = 0;
-- This form of the UPDATE statement has some particular rules.
-- See Jeff Moden's article at http://www.sqlservercentral.com/articles/T-SQL/68467/
-- for a complete discussion of how this works, and all of the rules for utilizing it.
UPDATE t1
SET @GroupNo = GroupNo = CASE WHEN @Prefix <> LEFT(controlnumber, PATINDEX('%[0-9]%', controlnumber)-1) THEN 1
-- new prefix - start over
WHEN CONVERT(INT, SUBSTRING(controlnumber, PATINDEX('%[0-9]%', controlnumber), 255)) = @LastNumber + 1 THEN @GroupNo
-- continues the chain - use same group number
ELSE @GroupNo + 1 -- increment the group number
END,
@Prefix = Prefix = LEFT(controlnumber, PATINDEX('%[0-9]%', controlnumber)-1), -- store the prefix so we can use it later
@LastNumber = CONVERT(INT, SUBSTRING(endno, PATINDEX('%[0-9]%', endno), 255)), -- get what the last number is, to compare to the next number
@ControlNumber = controlnumber -- anchor column
FROM gaps2 t1 WITH (TABLOCKX) -- prevent others from using the table
OPTION (MAXDOP 1); -- prevent parallelism
SELECT Prefix, GroupNo, MIN(controlnumber), max(endno)
FROM gaps2
GROUP BY Prefix, GroupNo
ORDER BY Prefix, GroupNo;
Doesn't work with my live data 🙁
It is picking up alot of false positives. When I check the results they are completely wrong. ERRR been working on this all morning and I still can't get it figured out. I'll try to post some more accurate test data, hopefully someone sees the issue.
*****edit******
Complete range file attached (267,150 records). Txt file zipped up to save space. I am getting 36 results back, and all are incorrect.
October 7, 2010 at 5:05 pm
...In your attachment are several rows with "NULL" instead of any data.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 7, 2010 at 5:09 pm
Odd, The numbers are correct, it just inserted 13 null rows for some reason. I updated the zip.
October 7, 2010 at 6:03 pm
Craig,
I'm having problems loading this much data into a sample table. Can I get you to export the data into a new table in a new database? Detach the database, and zip up the data and log files, and post them up here?
Thanks!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 8, 2010 at 6:26 am
craig-404139 (10/7/2010)
WayneS (10/7/2010)
Jeff Moden (10/7/2010)
So... without me running all the code presented so far, what has been decided? Does the numbering restart for each prefix or not?k from the OP yet on the restarting.
It does not. each prefix identifies a unique 'set' of received data. They have no relation to each other , other than they are documents for the same case.
Does that answer your question?
Heh... since each prefix identifies a unique 'set' of received data, then the number does, in fact, "restart" for each prefix. Each prefix should have a "1" in there somewhere, yes? 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2010 at 8:41 am
WayneS (10/7/2010)
Craig,I'm having problems loading this much data into a sample table. Can I get you to export the data into a new table in a new database? Detach the database, and zip up the data and log files, and post them up here?
Thanks!
Attached. Thanks again for sticking with this.
October 8, 2010 at 11:05 am
Sorry guys, came down with a case of the plague yesterday, didn't mean to abandon the topic. Was looking through Wayne's code and Craig-4 (we're gonna need to do that to keep us straight here...) can you tell me if any of your char codes that start the numerics ever have a numeric?
IE: Are they always ABCD<number> or can they be AB1CF<number>?
Part of the reason I did it in an equivalent of reversing by using the isnumeric = 0 instead of 1, then maxing.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 8, 2010 at 11:10 am
Craig Farrell (10/8/2010)
Sorry guys, came down with a case of the plague yesterday, didn't mean to abandon the topic. Was looking through the code and Craig-4 (we're gonna need to do that to keep us straight here...) can you tell me if any of your char codes that start the numerics ever have a numeric?IE: Are they always ABCD<number> or can they be AB1CF<number>?
Since I need to get this done for this specific case, lets just worry about this data for now and hopefully ill be able to adjust the logic in the future to accommodate with your help.
But to answer your question the prefixes will always be text based characters. Sometimes they may contain a - or _ though.
I attached a complete test db to my previous post with the complete range of data (different than my initial test data, as this is live data with a slight modification for security purposes)
October 8, 2010 at 11:20 am
craig-404139 (10/8/2010)
Craig Farrell (10/8/2010)
Sorry guys, came down with a case of the plague yesterday, didn't mean to abandon the topic. Was looking through the code and Craig-4 (we're gonna need to do that to keep us straight here...) can you tell me if any of your char codes that start the numerics ever have a numeric?IE: Are they always ABCD<number> or can they be AB1CF<number>?
Since I need to get this done for this specific case, lets just worry about this data for now and hopefully ill be able to adjust the logic in the future to accommodate with your help.
But to answer your question the prefixes will always be text based characters. Sometimes they may contain a - or _ though.
I attached a complete test db to my previous post with the complete range of data (different than my initial test data, as this is live data with a slight modification for security purposes)
Yeah, noted that, and I'm having some trouble because I max out at SQL 2k5 here at the office. Any chance you're able to drop this database down to 9.0 format and rezip for me? It's whining about being version 661 and I max off at 621. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 8, 2010 at 11:22 am
Craig Farrell (10/8/2010)
craig-404139 (10/8/2010)
Craig Farrell (10/8/2010)
Sorry guys, came down with a case of the plague yesterday, didn't mean to abandon the topic. Was looking through the code and Craig-4 (we're gonna need to do that to keep us straight here...) can you tell me if any of your char codes that start the numerics ever have a numeric?IE: Are they always ABCD<number> or can they be AB1CF<number>?
Since I need to get this done for this specific case, lets just worry about this data for now and hopefully ill be able to adjust the logic in the future to accommodate with your help.
But to answer your question the prefixes will always be text based characters. Sometimes they may contain a - or _ though.
I attached a complete test db to my previous post with the complete range of data (different than my initial test data, as this is live data with a slight modification for security purposes)
Yeah, noted that, and I'm having some trouble because I max out at SQL 2k5 here at the office. Any chance you're able to drop this database down to 9.0 format and rezip for me? It's whining about being version 661 and I max off at 621. 🙂
Sure. I was also thinking would this be easier if I added a PK to the table? Both of these columns are just data columns in my production system.
October 8, 2010 at 11:44 am
craig-404139 (10/8/2010)
Sure. I was also thinking would this be easier if I added a PK to the table? Both of these columns are just data columns in my production system.
Without seeing the table I can't say anything definitively, but my guess is at least an index on this field would be in order. It shouldn't have to bookmark so RID or key shouldn't matter much, at least for this query.
If you replace the file above instead of attaching a new please make a new comment so I can play tooooo. 🙂
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 8, 2010 at 11:47 am
Craig Farrell (10/8/2010)
craig-404139 (10/8/2010)
Sure. I was also thinking would this be easier if I added a PK to the table? Both of these columns are just data columns in my production system.Without seeing the table I can't say anything definitively, but my guess is at least an index on this field would be in order. It shouldn't have to bookmark so RID or key shouldn't matter much, at least for this query.
If you replace the file above instead of attaching a new please make a new comment so I can play tooooo. 🙂
Updated the zip. Changed the compatibility to 90, added a pk column, and truncated the old large log (sorry i forgot the first time)
October 8, 2010 at 12:03 pm
craig-404139 (10/8/2010)
Updated the zip. Changed the compatibility to 90, added a pk column, and truncated the old large log (sorry i forgot the first time)
It would appear the internal version change does not affect the external detachment. Same error. Still getting the R2 version code not able to attach to 2k5. Might be able to restore a backup but not sure off the 2k8 backwards to 2k5, haven't tried doing it before.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 8, 2010 at 12:15 pm
Craig Farrell (10/8/2010)
craig-404139 (10/8/2010)
Updated the zip. Changed the compatibility to 90, added a pk column, and truncated the old large log (sorry i forgot the first time)It would appear the internal version change does not affect the external detachment. Same error. Still getting the R2 version code not able to attach to 2k5. Might be able to restore a backup but not sure off the 2k8 backwards to 2k5, haven't tried doing it before.
Sorry didn't put 1 + 1 together. You can't restore a 2008 db to 2005. Unfortunetly I don't have 2005 available 🙁
October 8, 2010 at 12:19 pm
I had assumed, since 2k5 and 2k don't behave, but figured it was worth the question. I'm sure Wayne and the others will be able to get you a direct solution off the actual data. I'll just step back and watch the show unless we can do some form of CSV importing, but I'm not sure how much bandwidth you have to try to get that exported.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 31 through 45 (of 91 total)
You must be logged in to reply to this topic. Login to reply