Hello folks,
I have a table where the records are like followings.
ID Value
---------------
1 aa
1 aa
1 aa
1 bb
1 bb
1 bb
1 bb
1 cc
2 pp
2 dd
2 dd
3 qq
4 aa
I need to include one column "SeqId" which will be having value based on the 1st 2 columns (ID + Value). I am struglling with this since last few days. Can I request you guys to help me out in this reagard.
ID Value SeqId
-----------------------------
1 aa 1
1 aa 2
1 aa 3
1 bb 1
1 bb 2
1 bb 3
1 bb 4
1 cc 1
2 pp 1
2 dd 1
2 dd 2
3 qq 1
4 aa 1
I need to know how to generating a sequence number, for example, from 300,000 to 900,000 without skipping any number due to failure. For example, if user 1 request a number then he/she will get 300000 in a transaction. User 2 will get 300001. How ever user 1's transaction fails, then the next request should get 300000. Is it possible to do this in SQL2K? If so, how do I create a table that and stored procedure that can do this.
Hi, I got stuck with a trivial issue. I have a table named T1 having column as Col1 (INT). I have another table named T2 having columns Col1(Int), COl2(Int). Following are the reacords in my table T1.
Col1 ---- 1 1 1 1 2 2 3 3 3 3 3 4 5 6 6 7
And I want to migrate the data from T1 to T2 where the Col1 data of T1 will get migrated to Col1 of T1 and after the migration is done the T2 should have the data like this.........
I'm having a brain not functioning day - well who am I kidding - more like a year :rolleyes:
I need some help with some sequence numbering and cannot even get my head around the logic I want to use, let alone the actual code.
I have a dataset with 3 fields:
Area ID RefNo
This table contains a list of employee ID's by Area and each employee has a RefNo (counter) in each area.
The data comes from 2 different sources and is combined in this table. Some employees had no RefNo already assigned to them so I have entered their RefNo as 10000 in order to ensure they are sorted at the bottom of the list.
The ID's that have RefNo's have to keep the one they have. Therefore, I need to create RefNo's for the ones that currently have RefNo 10000.
These numbers I create have to follow on from the highest RefNo for the Area.
For example:
Area ID RefNo A Z 1 A Y 2 A X 3 A W 10000 A V 10000 B N 1 B O 10000 B P 10000
So, for Area A, ID's W and V would have to be assigned RefNo 4 and 5, and for Area B, ID's O and P would have to be assigned RefNo 2 and 3.
Hi, I've a question, if I've the LSN (Log Sequence Number) of a transaction, keep with the program "Log Explorer", can I know which is the IP of the user that have do the transaction (perhaps serching in a log file of the Win 2000 Server) ? 10x, Clara
SELECT @ddo = a.DDO_DSCR_SHORT FROM dbo.DIM_DDO a JOIN dbo.Temp_ABR_HDR b ON a.DDO_ID = b.DDO WHERE b.SESSIONID = @sessid
SELECT @ay = AY FROM dbo.Temp_ABR_HDR WHERE SESSIONID = @sessid
-- set the default seq_no SELECT @seq_no = 1 -- get the max abrid. if no record return the seq_no will be 1 SELECT @seq_no = convert(integer, max(right(abrid, 4))) FROM dbo.ABR_HDR WHERE left(abrid, 7) = @ay + @ddo
-- convert @seq_no to string prefix by 0 SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4), @seq_no)), 4)
SELECT @ddo = a.DDO_DSCR_SHORT FROM dbo.DIM_DDO a JOIN dbo.Temp_ABR_HDR b ON a.DDO_ID = b.DDO WHERE b.SESSIONID = @sessid
SELECT @ay = AY FROM dbo.Temp_ABR_HDR WHERE SESSIONID = @sessid
-- set the default seq_no --SELECT @seq_no = 1 -- get the max abrid. if no record return the seq_no will be 1 --SELECT @seq_no = convert(integer, max(right(abrid, 4))) --FROM dbo.ABR_HDR --WHERE left(abrid, 7) = @ay + @ddo
-- convert @seq_no to string prefix by 0 --SELECT @abrid = @ay + @ddo + right('0000' + rtrim(convert(char(4),@seq_no)), 4)
is it possible to generate a number sequence in a query (without using loop). I want the output to look as ------- ID ------- 1 2 3 4 5 6 7 8 9 .... upto the last number I give in the query
Hello all,Iīm currently using a SQL Serve 2K. Would like to do a selectwhich returns the row number - this should not be physically stored inthe database. So for example, I would like to do a query against theCUSTOMER table and receive:* rowID || name1 Evander2 Ron3 Scoth4 JaneI donīt want to store the ID, because if I change the order byclause, the sequence may modifiy, and, for another example, having thesame set of data, I would receive:* rowID || name1 Scoth2 Ron3 Jane4 Evander could someone help me ?best regards,Evandro
Hi all, I have a question about generating membership numbers on the fly when someone registers to my website.
Rather than using the auto increment field as a membership number, I would rather keep it as just as the ID for the record and I would like to have a seperate membership number that looks something similar to this...
SR357324J
This will then stay with them for the lifetime of their membership and be on their printed loyalty card.
My questions are... 1) Is there a 'good practice' for membership number format and generation?
2) If this was used as a unique field, is there a degradation in performance when looking up records due to it being alphanumeric.
I may be well off base here, however these are my thoughts so far and your opinion/help is greatly appreciated.
declare @newAnswerId int declare @newQuestionId int
set @QuestionName='New Question'
BEGIN TRANSACTION Q1
--Creates New QuestionId with AnswerId 0 INSERT INTO Questions(QuestionId,Name,AnswerId) SELECT 1 + COALESCE(MAX(QuestionId), 0),RTRIM(@QuestionName),0 FROM Questions
--QuestionId just now created SELECT @newQuestionId=QuestionId FROM Questions WHERE Name=@QuestionName
BEGIN TRANSACTION QA1
--Create an AnswerId INSERT INTO Answers(AnswerId) SELECT 1 + COALESCE(MAX(AnswerId), 0) FROM Answers
--AnswerId just now created(I hope not the best way to do like this) SELECT @newAnswerId=MAX(AnswerId) from Answers --is it the best way to call statement like this or any other way better than this
--update Questions Table with this new Answerid UPDATE Questions set AnswerId=@newAnswerId where QuestionId=@newQuestionId
COMMIT TRANSACTION QA1 COMMIT TRANSACTION Q1
I think the second Transaction is not locking the table.so some how i should be able to get the newly create AnswerId
i can't use the identity column in my tables
Can some one please have a look at it and suggest me how do we go about it..
Does anyone know an efficient method for generating a sequence number in the following form?
Starting with 2 columns 1 2 ---- A X A Y B X B Y B Z C X
I want to then generate a third column as follows: 1 2 3 ------- A X 1 A Y 2 B X 1 B Y 2 B Z 3 C X 1
The purpose being so that I can easily identify the previous row within a Column1 group. So given column1=A and column2=Y I know that the previous row is Column3 - 1 where column1 = A. Therefore I will be able to join to the previous result of any row within any group quickly for future calculations.
INSERT INTO #invoice SELECT 'A', 100 UNION SELECT 'A', 200 UNION SELECT 'A', 300 UNION SELECT 'B', 400 UNION SELECT 'B', 500 UNION SELECT 'B', 600 UNION SELECT 'B', 700
Now I want a output like this
INVOICE_NO AMOUNT SEQUENCE_NO A 100 1 A 200 2 A 300 3 B 400 1 B 500 2 B 600 3 B 700 4
The sequence_no should set back to 1 on change of invoice_no these are items in a an invoice. they just want a sequence_no
I can do this using cursors, but if anyone can help with WHILE LOOP would be great
Hi, I have an error table that is to be updated by more than one package. There is a sequence number generated in the error table. It is generated by using the max value of the previous data present in the table. When more than one package runs parallely, conflict occurs in generating the sequence number. How can this be handled?
Hallo,Hot to get unique, sequential number during executionof stored procedure ?I can create table with autoincrement column,add record, get ident_current and delete recordeach time i need the number.However its not elegant i guess.best regardspluton
How can I create a number sequence starting at a certain number and continue on for the number of records I have.
For example I have 3000 records in my table and a field named I created called RecordId which I'd like to start at number 1 and goto 3000 (or maybe even start at 9000 and goto 12000 or however many records there are).
In my pseudo SQL code Im guessing it would be something like...
select * from Incident
update Incident set RecordId( i=9000; i<=Number of Records in Table; i++)
SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeA a UNION SELECT row_number() over (ORDER by a.employeeID) as rec_num, a.* FROM EmployeeB a
1 777 Mike HR 2 888 Susy HR 1 111 Smith TECH 2 222 John TECH 3 333 Lenny TECH
How do i get sequence number for all of this records. The rec_num reset for every statement. I want the records numbering for second statement continue from first statement so that it can be like this :
hi, i am a newcomer and a freshman in asp.net. i am now writing a web-based system for SME as my final year project. i am going to use sql server and asp.net in C# to perform my final year project. as asp.net is new for me, i would have some simple problems to ask. 1. in the project, i would like the system can automatically generate the enquiry number for each new order input to the system. for example today is 05 July 2006, the enquiry number would like 2006211xxxx, where 2006 is year, 211 is the day count start from 1 Jan and xxxx is the random number/ ordered number. how can i implement this? i even don't know how to generate the ordered number. could anyone help me 2. if there is an unknown test sample in each order input. as the sample number for each order is different, how can i set a flexible table that can have different number of rows for user to input the test result. thanks Rgds, universe
Hi all,Ive been trying to figure this out and has proven to be quite difficult for me. Lets say i do a select on all client numbers from a clients table i would want the first available number returned.Given the client table below my query would return 4 because its the lowest number availeble. Thanks. client tableclnum12367
Database in:SqlServer builtin interface with VS05 Using:ASP.NET VISUAL STUDIO 2005 I want to create a unique ID that identifies a row. Id:STU+(SeqNumber) for Student and Id:EMP+(SeqNumber) for Employee [Question]:But how can i create sequence number and store it for use like that of ORACLE
Current Idea:Based on registration info if type is student generate STU+seq num and insert to table along with username and other details and if employee like wise. So a procedure will receive this from user and append that with seqnumber and stores that in the table
SOURCE TABLE ID________COMMENT 123_______I am joe 123_______I am programmer 124_______I am Wang 124_______I am programmer 124_______I like cricket
DESTINATION TABLE
ID_____SEQ______COMMENT 123_____1_______I am joe 123_____2_______I am programmer 124_____1_______I am wang 124_____2_______I am programmer 124_____3_______I like cricket can somebody please advise the easiest way to do this in sql 2000?
I have a table with no sequence column. I created a new column, but I need a way to add sequence number. This is not going to be a Primary key or indexed. I assume I will use update, but how do I do this?
Example
Before Name | Seq Paul | NULL Tom | NULL Grant | NULL
Hi all anbody can help me writing sql code for this. All i need is to generate sequence basing on id_no Ex: if ID=ABC(twice) in seq_col as abc --1 abc ---2 Tables which I have Uniques_No ID_NO SEQ --------------------------------------------- 1 ABC 2 ABC 3 ABC 4 BBC 5 BBC
Expected results as below : ------------------
Uniques_No ID_NO SEQ --------------------------------------------- 1 ABC 1 2 ABC 2 3 ABC 3 4 BBC 1 5 BBC 2
