SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Generate ID
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/27/2013 :  07:29:33  Show Profile  Reply with Quote
Do you know how to create Store Procedure where in this Store Procedure, we must generate ID like this "INV130155P095" and insert to table A?
INV + 130155(juliandate) + P095 (random character)

please help for the script

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/27/2013 :  08:10:32  Show Profile  Reply with Quote
Try this..........
DECLARE @D DATETIME = GETDATE()
SELECT 'INV'+ CAST((DATEPART(year,@D)-1900)*1000+DATEDIFF(day,@D,CAST('01-01-'+CAST(DATEPART(year,@D) AS CHAR(4)) AS SMALLDATETIME))+1 AS VARCHAR) + LEFT(NewId(), 4)
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/27/2013 :  23:12:46  Show Profile  Reply with Quote
juliandate get from this:
select right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) from F_DATES WHERE RECID='ID0010001'

and how to insert that Store Procedure into table A in [Column 0]?
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/28/2013 :  00:13:54  Show Profile  Reply with Quote
--May be this?
Insert into TableA([Column 0])
SELECT 'INV' + right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) + LEFT(NewId(), 4)
FROM F_DATES
WHERE RECID='ID0010001'
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/28/2013 :  03:06:33  Show Profile  Reply with Quote
my bos want me to use ROW_NUMBER for replace random character, do you know how to use it?

bandi, do you have email? i want to ask you about programming language SQL
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/28/2013 :  03:44:38  Show Profile  Reply with Quote
Any one can help you regarding SQL in the forum...

Let us know the full description about your requirement....
Post some sample input data and also output based on your ligoc....
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/28/2013 :  03:51:03  Show Profile  Reply with Quote
I have table A that have 10000 records, and [Column 0] is blank.
[Column 0] must be input by file that format like I said before that must be sequence using ROW_NUMBER and can fill the blank at [Column 0] as the number of records.

can you help me?
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/28/2013 :  03:53:40  Show Profile  Reply with Quote
I must create the store procedure Mr.Bandi
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/28/2013 :  04:23:56  Show Profile  Reply with Quote
why you need ROW_NUMBER here?
Post F_DATES table's data and structure....
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/28/2013 :  04:32:38  Show Profile  Reply with Quote
because it used to get sequence number that can't be duplicated.

table F_DATES is use to get date in that table where the format to get is right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5)

This case is how to insert [Column 0] in table A with data that format is INV+right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5)+ROW_NUMBER order by total records that must be sequence.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/28/2013 :  04:41:11  Show Profile  Reply with Quote
--May be this?
Insert into TableA([Column 0])
SELECT 'INV' + right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) + 'P' + RIGHT('000' + CAST(ROW_NUMBER() OVER(ORDER BY RECID) AS VARCHAR) , 3) FROM F_DATES


--Illustration (execute this sample script once)
DECLARE @Sample TABLE(id INT)
INsert into @Sample VALUES( 10), (20), (30), (40), (100), (130),(200)
SELECT 'INV' + 'YourJulianDate' +'P'+ RIGHT( '000' + CAST(ROW_NUMBER() OVER(ORDER BY id) AS VARCHAR), 3) from @Sample
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/28/2013 :  04:50:01  Show Profile  Reply with Quote
why you use this values ( 10), (20), (30), (40), (100), (130),(200)?
random character like P095 isn't use again, now use ROW_NUMBER that have 5 character and must be sequence.

my yuliandate is take from:
select XMLRECORD.value('(/row/c14)[1]','varchar(10)') from t24testsby.dbo.F_DATES WHERE RECID='ID0010001'

and how to read that i can read how number records to fill this format?
I want to read number records from [column 1] in table A
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/28/2013 :  04:56:36  Show Profile  Reply with Quote
i have try your query, i have new query that it is true, but row_number that result can be increase as number of records.

Insert into A(ID)
SELECT 'INV' + right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) + RIGHT('00000' + CAST(ROW_NUMBER() OVER(ORDER BY RECID) AS VARCHAR) , 5) FROM t24testsby.dbo.F_DATES WHERE RECID='ID0010001'
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/28/2013 :  05:02:41  Show Profile  Reply with Quote
here RECID should be your Primary Key column name
(ROW_NUMBER() OVER(ORDER BY RECID)
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/28/2013 :  05:19:47  Show Profile  Reply with Quote
wow how great you are, thanks a lot.

in (ROW_NUMBER() OVER(ORDER BY [Column 1] asc), do you know how the different using asc or not?

why you use cast not convert?
Go to Top of Page

kabon
Starting Member

Indonesia
48 Posts

Posted - 03/28/2013 :  05:31:10  Show Profile  Reply with Quote
Bandi, it still duplicate.

select right(XMLRECORD.value('(/row/c14)[1]','varchar(10)'),5) AS jul into ##jul from t24testsby.dbo.F_DATES WHERE RECID='ID0010001'

Insert into A(ID)
select 'INV' + (select * from ##jul) + RIGHT('00000' + CAST(ROW_NUMBER() OVER(ORDER BY [Column 1] asc) AS VARCHAR) , 5) from ctmuratx_20130327
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 03/28/2013 :  05:31:46  Show Profile  Reply with Quote
You can use either of one( CAST, CONVERT)....

ROW_NUMBER() OVER(ORDER BY [Column 1] asc ) ----> By default it will consider Ascending...
So if you want row number by descending, you must specify DESC keyword instead of asc
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30114 Posts

Posted - 03/28/2013 :  06:59:32  Show Profile  Visit SwePeso's Homepage  Reply with Quote
CREATE TABLE	dbo.MyOwnSequenceTable
		(
			DocType CHAR(3) NOT NULL,
			DocDate INT NOT NULL,
			SubType CHAR(1) NOT NULL,
			Sequence SMALLINT NOT NULL
		);

CREATE UNIQUE CLUSTERED INDEX UCX_MyOwnSequenceTable ON dbo.MyOwnSequenceTable (DocType, DocDate, SubType);
GO
CREATE PROCEDURE dbo.uspGetMySequenceNumber
(
	@DocType CHAR(3),
	@DocDate DATE,
	@SubType CHAR(1),
	@Number CHAR(13) OUT
)
AS

SET NOCOUNT ON;

DECLARE	@Result TABLE
	(
		DocType CHAR(3) NOT NULL,
		DocDate INT NOT NULL,
		SubType CHAR(1) NOT NULL,
		Sequence SMALLINT NOT NULL
	);

MERGE	dbo.MyOwnSequenceTable AS tgt
USING	(
		VALUES	(@DocType, 10000 * (DATEPART(YEAR, @DocDate) % 100) + DATEPART(DAYOFYEAR, @DocDate), @SubType, 1)
	) AS src(DocType, DocDate, SubType, Delta) ON src.DocType = tgt.DocType
		AND src.DocDate = tgt.DocDate
		AND src.SubType = tgt.SubType
WHEN	MATCHED
		THEN	UPDATE
			SET	tgt.Sequence += src.Delta
WHEN	NOT MATCHED BY TARGET
		THEN	INSERT	(
					DocType,
					DocDate,
					SubType,
					Sequence
				)
			VALUES	(
					src.DocType,
					src.DocDate,
					src.SubType,
					src.Delta
				)
OUTPUT	inserted.DocType,
	inserted.DocDate,
	inserted.SubType,
	inserted.Sequence
INTO	@Result
	(
		DocType,
		DocDate,
		SubType,
		Sequence
	);

SELECT	@Number = DocType + CAST(DocDate AS VARCHAR(6)) + SubType + RIGHT('00' + CAST(Sequence AS VARCHAR(3)), 3)
FROM	@Result;
GO

DECLARE	@Number CHAR(13);

EXEC dbo.uspGetMySequenceNumber 'INV', '20130328', 'P', @Number OUT
SELECT	@Number
GO 3

DECLARE	@Number CHAR(13);

EXEC dbo.uspGetMySequenceNumber 'INV', '20130328', 'Q', @Number OUT
SELECT	@Number
GO 3

DECLARE	@Number CHAR(13);

EXEC dbo.uspGetMySequenceNumber 'INV', '20130328', 'P', @Number OUT
SELECT	@Number
GO 3

DECLARE	@Number CHAR(13);

EXEC dbo.uspGetMySequenceNumber 'PSD', '20130328', 'A', @Number OUT
SELECT	@Number
GO 3



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000