Solved

Sql Query - Preassign numbers to prefix

Posted on 2011-09-14
19
533 Views
Last Modified: 2012-06-27
Hello experts,

Not sure how I can do this in SQL Query.  I can do it via vb.net, but not sure how simple it is to do it via SQL Query.  I have an excel list that is given to me on a daily basis.  I need to take this excel file and dump it into a temp table.  Then take the prefix and look in the database to see what number is already being use.  If a number is available, then I want it to append the availble number to the prefix.

Example:

Prefix ABCD is on the list.  In the database table "Item" has a ton of parts that start with ABCD.  All these item id are only 10 digits long.  So we got the prefix and append any number with leading zeros to it.  Example, item "ABCD000300" is the last number so this new item ID will be ABCD000301.  If there's a skip number between ABCD000298 and ABCD000300, then ABCD000299 will be use.  

So to clarify, I need to search through the table "Item" for like prefix.  Then compare the number to see which number is available.  If it's available, then use that and append leading zeros so that the new ID is 10 digits long.  Any ideas?  See screenshot.
example.jpg
0
Comment
Question by:holemania
  • 7
  • 6
  • 5
  • +1
19 Comments
 
LVL 40

Expert Comment

by:RQuadling
ID: 36537382
Can you give us an example of the data in the temp table as that is what we are needing to manipulate.
0
 

Expert Comment

by:wenogk
ID: 36537400
You will have to give me more code into this please.
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 36537879
in 2005 I couldn't think of a convenient way to do it,  in 2008 the LAG analytic makes this much easier


but, barring that,  this works in 2005 when I tested it for TTMD

SELECT   stuff(right(replicate('0', 10) + LTRIM(str(MIN(newid))), 10), 1, datalength(prefix), prefix)
    FROM (SELECT prefix, CAST(REPLACE(newid, prefix, '') AS INT) - 1 newid
            FROM (SELECT x.*,
                         ROW_NUMBER() OVER (PARTITION BY prefix ORDER BY newid DESC, b_newid DESC) rn
                    FROM (SELECT   a.*, MAX(b.newid) b_newid
                              FROM     yourtable a
                                   LEFT JOIN
                                       yourtable b
                                   ON a.prefix = b.prefix AND a.prefix = 'TTMD' AND a.newid > b.newid
                          GROUP BY a.newid, a.prefix)as x
                   WHERE CAST(REPLACE(newid, prefix, '') AS INT)
                         - CAST(REPLACE(b_newid, prefix, '') AS INT) > 1)as x
           WHERE rn = 1
          UNION
          SELECT   prefix, MAX(CAST(REPLACE(newid, prefix, '') AS INT)) + 1 newid
              FROM yourtable
             WHERE prefix = 'TTMD'
          GROUP BY prefix) as x
GROUP BY prefix




just change TTMD to TTT or other prefix in both places to search for other prefix id's
0
 

Author Comment

by:holemania
ID: 36539403
Normally, I'm giving an excel spreadsheet with a prefix and description for a new item.  I need to look at the "ITEM" table and deteremine which number has been use already for that prefix.  What I do is dump this excel list to a SQL table and work off that.

Excel List:
Prefix     Description
TTT        6"x5" ABS 304
TTT        7"x5.5" ABS 304
TTMD     RAIL 6"x3.5"
STR        STRAP NYLON
FABR04  FABRICATED TYPE 04 MAT.

So from the temp table that I'm dumpinng these data into, it's looking into another table called "ITEM".  In there, we have item number that starts with prefix and followed by numbers.  The item number is 10 digits, so prefix follow by a number with leading zeros.

Id                          Description
TTT0000001          1"x1" ABS 304
TTT0000002          1.5"x1" ABS 304
TTT0000005          1.8"x3.5" ABS 304
TTMD000001          RAIL 6"x1.25"
TTMD000003          RAIL 6"x3"

So I want a SQL Script that will read from the temp table and look at the prefix.  Compare that prefix with the table ITEM for like prefix.  In this case the first prefix is "TTT".  It'll look and see that 1 and 2 is being use, but 3 and 4 is not.  So it takes the "TTT" and 3 and 4 and pad it with leading zeros to make TTT0000003 and TTT0000004 and so on.  Next prefix is "TTMD" and see that TTMD has 1 and 3 being use, but 2 is available so it assign TTMD000002.  It just kept on incrementing the number for available number with similar like prefix.  Once I get the output, I need to copy it back into excel and send it for review.  Once approve, I have a VB small apps that read from this excel file and dump it into the "ITEM" table for use.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36539606
are you changing this question? if so, please close this one and open a new one with the new requirements.


I tested the query above it works for the sample data you have provided
if you have a test cast where it doesn't, please post it.


if you're simply trying to swap in a query for "yourtable", just create a CTE with your query and use that
0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36541839
Here is some code I put together. Based upon a random table of data, it seems to be working as expected.

BEGIN TRANSACTION

-- Create a temporary table with some values to play with
CREATE TABLE #Parts(Part CHAR(10) PRIMARY KEY CLUSTERED)

INSERT INTO #Parts(Part)
SELECT 'BB00000001' UNION ALL
SELECT 'EEEEE00002' UNION ALL
SELECT 'DDDD000003' UNION ALL
SELECT 'CCC0000004' UNION ALL
SELECT 'BB00000005' UNION ALL
SELECT 'A000000006' UNION ALL
SELECT 'CCC0000007' UNION ALL
SELECT 'EEEEE00008' UNION ALL
SELECT 'BB00000009' UNION ALL
SELECT 'BB00000010' UNION ALL
SELECT 'CCC0000011' UNION ALL
SELECT 'DDDD000012' UNION ALL
SELECT 'CCC0000013' UNION ALL
SELECT 'EEEEE00014' UNION ALL
SELECT 'CCC0000015' UNION ALL
SELECT 'A000000016' UNION ALL
SELECT 'CCC0000017' UNION ALL
SELECT 'EEEEE00018' UNION ALL
SELECT 'BB00000019' UNION ALL
SELECT 'CCC0000020' UNION ALL
SELECT 'BB00000021' UNION ALL
SELECT 'DDDD000022' UNION ALL
SELECT 'BB00000023' UNION ALL
SELECT 'EEEEE00024' UNION ALL
SELECT 'CCC0000025' UNION ALL
SELECT 'EEEEE00026' UNION ALL
SELECT 'DDDD000027' UNION ALL
SELECT 'EEEEE00028' UNION ALL
SELECT 'A000000029' UNION ALL
SELECT 'EEEEE00030' UNION ALL
SELECT 'DDDD000031' UNION ALL
SELECT 'A000000032' UNION ALL
SELECT 'BB00000033' UNION ALL
SELECT 'CCC0000034' UNION ALL
SELECT 'BB00000035' UNION ALL
SELECT 'A000000036' UNION ALL
SELECT 'BB00000037' UNION ALL
SELECT 'BB00000038' UNION ALL
SELECT 'DDDD000039' UNION ALL
SELECT 'BB00000040' UNION ALL
SELECT 'A000000041' UNION ALL
SELECT 'A000000042' UNION ALL
SELECT 'EEEEE00043' UNION ALL
SELECT 'EEEEE00044' UNION ALL
SELECT 'BB00000045' UNION ALL
SELECT 'A000000046' UNION ALL
SELECT 'DDDD000047' UNION ALL
SELECT 'CCC0000048' UNION ALL
SELECT 'A000000049' UNION ALL
SELECT 'DDDD000050' UNION ALL
SELECT 'BB00000051' UNION ALL
SELECT 'EEEEE00052' UNION ALL
SELECT 'CCC0000053' UNION ALL
SELECT 'EEEEE00054' UNION ALL
SELECT 'BB00000055' UNION ALL
SELECT 'A000000056' UNION ALL
SELECT 'CCC0000057' UNION ALL
SELECT 'A000000058' UNION ALL
SELECT 'BB00000059' UNION ALL
SELECT 'CCC0000060' UNION ALL
SELECT 'EEEEE00061' UNION ALL
SELECT 'BB00000062' UNION ALL
SELECT 'DDDD000063' UNION ALL
SELECT 'CCC0000064' UNION ALL
SELECT 'BB00000065' UNION ALL
SELECT 'CCC0000066' UNION ALL
SELECT 'BB00000067' UNION ALL
SELECT 'BB00000068' UNION ALL
SELECT 'DDDD000069' UNION ALL
SELECT 'EEEEE00070' UNION ALL
SELECT 'CCC0000071' UNION ALL
SELECT 'DDDD000072' UNION ALL
SELECT 'BB00000073' UNION ALL
SELECT 'DDDD000074' UNION ALL
SELECT 'EEEEE00075' UNION ALL
SELECT 'CCC0000076' UNION ALL
SELECT 'A000000077' UNION ALL
SELECT 'A000000078' UNION ALL
SELECT 'DDDD000079' UNION ALL
SELECT 'EEEEE00080' UNION ALL
SELECT 'BB00000081' UNION ALL
SELECT 'EEEEE00082' UNION ALL
SELECT 'DDDD000083' UNION ALL
SELECT 'A000000084' UNION ALL
SELECT 'EEEEE00085' UNION ALL
SELECT 'BB00000086' UNION ALL
SELECT 'EEEEE00087' UNION ALL
SELECT 'EEEEE00088' UNION ALL
SELECT 'BB00000089' UNION ALL
SELECT 'BB00000090' UNION ALL
SELECT 'DDDD000091' UNION ALL
SELECT 'A000000092' UNION ALL
SELECT 'EEEEE00093' UNION ALL
SELECT 'A000000094' UNION ALL
SELECT 'DDDD000095' UNION ALL
SELECT 'DDDD000096' UNION ALL
SELECT 'BB00000097' UNION ALL
SELECT 'DDDD000098' UNION ALL
SELECT 'EEEEE00099' UNION ALL
SELECT 'BB00000100'


-- Assume that @s_Prefix is coming in from a function.
DECLARE @s_Prefix VARCHAR(10)
SET @s_Prefix = 'BB'


-- I don't know how to determine the max length of a column, so hardcode it.
DECLARE @i_MaxLen INT
SET @i_MaxLen = 10

DECLARE @i_NextPart INT

;WITH
	-- Get a list of all the part numbers currently recorded.
	NumericPart(PartNumber) AS
		(
		SELECT
			CAST(SUBSTRING(Part, 1 + LEN(@s_Prefix), LEN(Part)) AS INT) PartNumber
		FROM
			#Parts
		WHERE
			Part LIKE @s_Prefix + '[0-9]%'
		),

	-- Determine the maximum value in this range.
	MaxPartNumber(MaxPartNumber) AS
		(
		SELECT
			MAX(PartNumber)
		FROM
			NumericPart
		),

	-- Get the range of numbers from 1 to the maxiumum + 1
	NumericCount(PartNumber) AS
		(
		SELECT
			1

		UNION ALL

		SELECT
			NP.PartNumber + 1
		FROM
			NumericCount NP,
			MaxPartNumber MNP
		WHERE
			NP.PartNumber <= MNP.MaxPartNumber
		)

-- Get the next part number which is the lowest value in the NumericCount range that isn't present in the NumericPart set.
SELECT
	@i_NextPart = MIN(PartNumber)
FROM
	NumericCount
WHERE
	PartNumber NOT IN
		(
		SELECT
			PartNumber
		FROM
			NumericPart
		)
		
SELECT
	@s_Prefix [Prefix],
	@i_NextPart [Next Part],
	@s_Prefix + REPLICATE('0', @i_MaxLen - LEN(@s_Prefix) - LEN(CAST(@i_NextPart AS VARCHAR))) + CAST(@i_NextPart AS VARCHAR) [Part],
	LEN(@s_Prefix + REPLICATE('0', @i_MaxLen - LEN(@s_Prefix) - LEN(CAST(@i_NextPart AS VARCHAR))) + CAST(@i_NextPart AS VARCHAR)) [Part Length]

DROP TABLE #Parts

ROLLBACK TRANSACTION

Open in new window


Outputs
Prefix	Next Part	Part	Part Length
BB	2	BB00000002	10

Open in new window


I've not tested all edge-cases, but would be willing to help refine this if needed or if you find a combination of data that fails.
0
 
LVL 40

Accepted Solution

by:
RQuadling earned 250 total points
ID: 36541885
Refined. Automatically inserts and reports back a list of all the items, highlighting the latest addition.

If you keep running the code, it will insert more and more items.

IF OBJECT_ID('tempdb..#Parts') IS NULL
	BEGIN
	-- Create a temporary table with some values to play with
	CREATE TABLE #Parts(UniqueID INT IDENTITY(1,1),Part CHAR(10) PRIMARY KEY CLUSTERED)

	INSERT INTO #Parts(Part)
	SELECT 'BB2B000001' UNION ALL
	SELECT 'EEEEE5E002' UNION ALL
	SELECT 'CCC3C00003' UNION ALL
	SELECT 'EEEEE5E004' UNION ALL
	SELECT 'CCC3C00005' UNION ALL
	SELECT 'BB2B000006' UNION ALL
	SELECT 'EEEEE5E007' UNION ALL
	SELECT 'CCC3C00008' UNION ALL
	SELECT 'A1A0000009' UNION ALL
	SELECT 'DDDD4D0010' UNION ALL
	SELECT 'EEEEE5E011' UNION ALL
	SELECT 'BB2B000012' UNION ALL
	SELECT 'A1A0000013' UNION ALL
	SELECT 'BB2B000014' UNION ALL
	SELECT 'EEEEE5E015' UNION ALL
	SELECT 'A1A0000016' UNION ALL
	SELECT 'CCC3C00017' UNION ALL
	SELECT 'CCC3C00018' UNION ALL
	SELECT 'CCC3C00019' UNION ALL
	SELECT 'A1A0000020' UNION ALL
	SELECT 'DDDD4D0021' UNION ALL
	SELECT 'A1A0000022' UNION ALL
	SELECT 'BB2B000023' UNION ALL
	SELECT 'DDDD4D0024' UNION ALL
	SELECT 'BB2B000025' UNION ALL
	SELECT 'EEEEE5E026' UNION ALL
	SELECT 'DDDD4D0027' UNION ALL
	SELECT 'EEEEE5E028' UNION ALL
	SELECT 'EEEEE5E029' UNION ALL
	SELECT 'CCC3C00030' UNION ALL
	SELECT 'A1A0000031' UNION ALL
	SELECT 'CCC3C00032' UNION ALL
	SELECT 'CCC3C00033' UNION ALL
	SELECT 'BB2B000034' UNION ALL
	SELECT 'A1A0000035' UNION ALL
	SELECT 'DDDD4D0036' UNION ALL
	SELECT 'BB2B000037' UNION ALL
	SELECT 'DDDD4D0038' UNION ALL
	SELECT 'A1A0000039' UNION ALL
	SELECT 'EEEEE5E040' UNION ALL
	SELECT 'DDDD4D0041' UNION ALL
	SELECT 'BB2B000042' UNION ALL
	SELECT 'EEEEE5E043' UNION ALL
	SELECT 'EEEEE5E044' UNION ALL
	SELECT 'BB2B000045' UNION ALL
	SELECT 'DDDD4D0046' UNION ALL
	SELECT 'EEEEE5E047' UNION ALL
	SELECT 'EEEEE5E048' UNION ALL
	SELECT 'CCC3C00049' UNION ALL
	SELECT 'BB2B000050' UNION ALL
	SELECT 'DDDD4D0051' UNION ALL
	SELECT 'EEEEE5E052' UNION ALL
	SELECT 'DDDD4D0053' UNION ALL
	SELECT 'EEEEE5E054' UNION ALL
	SELECT 'A1A0000055' UNION ALL
	SELECT 'EEEEE5E056' UNION ALL
	SELECT 'DDDD4D0057' UNION ALL
	SELECT 'A1A0000058' UNION ALL
	SELECT 'A1A0000059' UNION ALL
	SELECT 'BB2B000060' UNION ALL
	SELECT 'A1A0000061' UNION ALL
	SELECT 'EEEEE5E062' UNION ALL
	SELECT 'A1A0000063' UNION ALL
	SELECT 'CCC3C00064' UNION ALL
	SELECT 'A1A0000065' UNION ALL
	SELECT 'DDDD4D0066' UNION ALL
	SELECT 'DDDD4D0067' UNION ALL
	SELECT 'A1A0000068' UNION ALL
	SELECT 'A1A0000069' UNION ALL
	SELECT 'EEEEE5E070' UNION ALL
	SELECT 'DDDD4D0071' UNION ALL
	SELECT 'BB2B000072' UNION ALL
	SELECT 'EEEEE5E073' UNION ALL
	SELECT 'EEEEE5E074' UNION ALL
	SELECT 'DDDD4D0075' UNION ALL
	SELECT 'A1A0000076' UNION ALL
	SELECT 'EEEEE5E077' UNION ALL
	SELECT 'EEEEE5E078' UNION ALL
	SELECT 'CCC3C00079' UNION ALL
	SELECT 'BB2B000080' UNION ALL
	SELECT 'EEEEE5E081' UNION ALL
	SELECT 'CCC3C00082' UNION ALL
	SELECT 'DDDD4D0083' UNION ALL
	SELECT 'DDDD4D0084' UNION ALL
	SELECT 'A1A0000085' UNION ALL
	SELECT 'CCC3C00086' UNION ALL
	SELECT 'DDDD4D0087' UNION ALL
	SELECT 'EEEEE5E088' UNION ALL
	SELECT 'BB2B000089' UNION ALL
	SELECT 'A1A0000090' UNION ALL
	SELECT 'BB2B000091' UNION ALL
	SELECT 'BB2B000092' UNION ALL
	SELECT 'BB2B000093' UNION ALL
	SELECT 'EEEEE5E094' UNION ALL
	SELECT 'A1A0000095' UNION ALL
	SELECT 'BB2B000096' UNION ALL
	SELECT 'DDDD4D0097' UNION ALL
	SELECT 'A1A0000098' UNION ALL
	SELECT 'BB2B000099' UNION ALL
	SELECT 'EEEEE5E100'
	END

-- Assume that @s_Prefix is coming in from a function.
DECLARE @s_Prefix VARCHAR(10)
SET @s_Prefix = 'A1A'


-- I don't know how to determine the max length of a column, so hardcode it.
DECLARE @i_MaxLen INT
SET @i_MaxLen = 10

DECLARE @i_NextPart INT

;WITH
	-- Get a list of all the part numbers currently recorded.
	NumericPart(PartNumber) AS
		(
		SELECT
			CAST(SUBSTRING(Part, 1 + LEN(@s_Prefix), LEN(Part)) AS INT) PartNumber
		FROM
			#Parts
		WHERE
			Part LIKE @s_Prefix + '[0-9]%'
		),

	-- Determine the maximum value in this range.
	MaxPartNumber(MaxPartNumber) AS
		(
		SELECT
			MAX(PartNumber)
		FROM
			NumericPart
		),

	-- Get the range of numbers from 1 to the maxiumum + 1
	NumericCount(PartNumber) AS
		(
		SELECT
			1

		UNION ALL

		SELECT
			NP.PartNumber + 1
		FROM
			NumericCount NP,
			MaxPartNumber MNP
		WHERE
			NP.PartNumber <= MNP.MaxPartNumber
		)

-- Insert the next part number into the set where the numeric part is the lowest value in the NumericCount range that isn't present in the NumericPart set.
INSERT INTO
	#Parts
SELECT
	@s_Prefix + REPLICATE('0', @i_MaxLen - LEN(@s_Prefix) - LEN(CAST(MIN(PartNumber) AS VARCHAR))) + CAST(MIN(PartNumber) AS VARCHAR) [Part]
FROM
	NumericCount
WHERE
	PartNumber NOT IN
		(
		SELECT
			PartNumber
		FROM
			NumericPart
		)

SELECT
	UniqueID,
	Part,
	CASE WHEN SCOPE_IDENTITY() = UniqueID THEN 'Lastest' ELSE '' END Latest
FROM
	#Parts
WHERE
	Part LIKE @s_Prefix + '[0-9]%'

Open in new window


Outputs (after several runs)
UniqueID	Part	Latest
101	A1A0000001	
102	A1A0000002	
103	A1A0000003	
104	A1A0000004	
105	A1A0000005	
106	A1A0000006	
107	A1A0000007	
108	A1A0000008	
9	A1A0000009	
109	A1A0000010	
110	A1A0000011	
111	A1A0000012	
13	A1A0000013	
112	A1A0000014	
113	A1A0000015	
16	A1A0000016	
114	A1A0000017	
115	A1A0000018	
116	A1A0000019	Lastest
20	A1A0000020	
22	A1A0000022	
31	A1A0000031	
35	A1A0000035	
39	A1A0000039	
55	A1A0000055	
58	A1A0000058	
59	A1A0000059	
61	A1A0000061	
63	A1A0000063	
65	A1A0000065	
68	A1A0000068	
69	A1A0000069	
76	A1A0000076	
85	A1A0000085	
90	A1A0000090	
95	A1A0000095	
98	A1A0000098	

Open in new window

0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36541895
Based upon the above state, then next insert for "A1A" will be "A1A0000021" with an ID of 117
UniqueID	Part	Latest
116	A1A0000019	
20	A1A0000020	
117	A1A0000021	Lastest
22	A1A0000022	
31	A1A0000031	

Open in new window

0
 

Author Comment

by:holemania
ID: 36545255
sdstuber,

I'm not changing the question.  I'm just clarifying it since I feel like I didn't provide adequate information for people to understand what I want to do.  

As explained, I get an excel file with prefix and description.  I'm supposed to generate new IDs for it using the prefix and description given to me.  The new Id consist of prefix and next available number.  I just need a way to look at the "ITEM" table and assign a new ID using the prefix and auto generated number.


I will look at all that was provided and give me feedback.  Thanks all that has contribute.
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 73

Expert Comment

by:sdstuber
ID: 36545321
ah,  so "yourtable"  is ITEM?  

if so, then what I provided should work fine
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36545326
your second example made it sound like all your source data wasn't in place as it was in the original question.

0
 

Author Comment

by:holemania
ID: 36545458
Yeah sorry for the confusion.  But from the few responses, I thought that my original question wasn't adequate.

But yes, I create a temp table to dump the excel file to to work with since it seems easier for me to work with in SQL.  I have an actual table called "ITEM" that stored the part Item numbers.  What I normally get is an excel file with just the prefix and description.  The prefix determine what type of item and the description help tell what item it should be.  

I am tasked with auto assigning numbers to the prefix to make it a new number.  Currently to do this, I have to manually look at each part that begins with the prefix and manually make the update.  

So I end up doing this select statement.


Select *
From Item
Where  ID like 'TTT%'

I would get a list of parts with TTT.

TTT0000001
TTT0000002
TTT0000004

Seeing that I know that 3 isn't use, so I name the first prefix with TTT0000003.  I keep on searching until I'm done with that set of prefix.  Then go onto the next one which is time consuming and all done manually.

So my plan is to load the excel to a temp table, and hopefully to have a script that will automate.  Once I get my new numbers I will export to excel and send it for review and then use a vb.net application which just insert the new ID and description into the "ITEM" table that will read off the excel file.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36545480
as long as your temp table looks like what is your original question

that is  3 columns - newid,  prefix,  description  

or in particular,  the first 2 columns,  newid and prefix

then my query should do it for you  (it works when I test it anyway)


0
 

Author Comment

by:holemania
ID: 36545547
The temp table just has 2 fields.  It's got the prefix and description since that's what I get from the user.  My screenshot was just an example of my output result, and could be what is causing the confusion.

So my temp table is same as the excel file as followed:

Prefix     Description
TTT        6"x5" ABS 304
TTT        7"x5.5" ABS 304
TTMD     RAIL 6"x3.5"
STR        STRAP NYLON
FABR04  FABRICATED TYPE 04 MAT.

I am tasked to assign new numbers to the prefix above.  In order to assign new numbers, I have to look at the "ITEM" table since there's numbers there with these perfix already.  So I would query what's in the "ITEM" table with the prefix like my last post.  Then manually assign next number to it.

With the thread I opened, was hoping to find a quicker way of generating these new ID by having it look at a temp table for the prefix and compare it with "ITEM" table.  Then auto generate next available number to use for that prefix.

I will try both of the examples provided and see what would work.

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 36545589
that's changing the question.


however, like I said above, it's easily resolved,
just create a CTE  for yourtable so it returns columns like you originally asked for
and then apply my query to that

WITH yourtable as (select newid, prefix,description from ......)
select .... <my query>
0
 

Author Comment

by:holemania
ID: 36546191
Sorry, I wasn't being clear on my original thread.  However, my intention is still the same in trying to auto generate numbers for a list with just prefix.  I will play around with both that's been provided and see if I need additional help.

0
 
LVL 40

Expert Comment

by:RQuadling
ID: 36548822
Did you try my code? If so, does it do what you want? If not, what does it do that is not right for you?
0
 

Author Comment

by:holemania
ID: 36589612
Sorry had been out for a week.  However, I did look at both and both seems to work if I just tweak it a bit.  So I will use one, but will give credit to both of you for helping out since they both are working, but will have to decide upon one.
0
 

Author Closing Comment

by:holemania
ID: 36589621
Thank you you both.  That helps a lot.
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now