Re-Sequence Out Of Order Numbers

Sep 8, 2012

I have the following data in a table:

Account SEQ
12345 1
12345 2
12345 4
12345 5
12345 7

I need to fix the SEQ field so that no gaps exist, like this:

Account SEQ
12345 1
12345 2
12345 3
12345 4
12345 5

Is there a way to do this with T-SQL?

View 3 Replies


ADVERTISEMENT

Sequence Numbers In Sql Server

Mar 25, 2004

Is is possible to Generate sequence numbers in a Query result in an SQL select Statement

like

Select sno,employee_name from empmaster

sno being a number with auto increments(this Field will not be in a Table).

I do not want to use Identity .....

This is Just to Provide a Serial Number to the Query Output...

thus the Out put should be something like

sno employeename
--- ------------------
1 abc
2 jhjk
3 kljl

View 4 Replies View Related

Adding Line Sequence Numbers

Jun 25, 2007

hi all

I am stuck with something that seems easy but im obviously clueless as how to execute the idea.

I have a custom table that houses invoices on the details level. So for example i have:

InvcNo
00000001
00000001
00000001
00000002
00000002
00000003

and so forth

What I am wanting to do in another column is keep track of the sequence number for each distinct invoice like:

SeqNo
1
2
3
1
2
1

I am working in a stored proc and i cant get past adding the numbers up at each line as a whole and not reseting when the next invoice number is present. Any help would be so greatly appreciated.

Thanks

View 10 Replies View Related

Generating Sequence Numbers In Target Table

Dec 4, 2007

Hi,
What transformations can be used to generate sequence numbers in a data flow?

View 2 Replies View Related

Assigning Unique Sequence Numbers Across Different Tables

Oct 11, 2007

I have a procedure which updates a sequence number in a table such as the one below.


Seq Sequence_Id

------ ------------------
NextNum 1


This is the procedure ...

create procedure DBO.MIG_SYS_NEXTVAL(@sequence varchar(10), @sequence_id int)
as
begin

update mig_sys_sequences
set
@sequence_id = sequence_id = sequence_id + 1
where
seq = 'CSN'

return(@sequence_id)
end


The purpose of this is to generate a sequential number each time the procedure is called. This number would then be used in a number of different tables to allocate a unique id so that the id is unique across the different tables.


1). What is the most efficient way of allocating these unique ids? The tables that I plan to update will already be populated with data.

2). How would I call the above procedure from an UPDATE statement?

Many thanks,

Fred

View 1 Replies View Related

Updating Two Columns With Sequence Numbers With Where Clause?

Jun 29, 2015

I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.

my example table:

numez numhx
111 111
111 111
0 111
111 0
111 0

and my results should be:

numez numhx
1 2
3 4
0 5
6 0
7 0

View 4 Replies View Related

SQL 2012 :: Update A Column With A Sequence Of Numbers Starting From 1?

Oct 7, 2015

If Exists ( Select c.name from sys.columns c where object_id = object_id('HH835HP') and C.name = 'ID_1' )
Begin
UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;
End;

Obviously... The stuff inside the IF is wrong syntax...I mean

UPDATE HH835HP
SET ID_1 =
( select ROW_NUMBER() OVER(ORDER BY CHKDTS ASC) AS ID_1 FROM HH835HP ) ;

View 4 Replies View Related

SQL Server 2012 :: Assign Sequence Numbers To Twins / Triplets Only

Sep 3, 2014

I have a Contact table where I enter a "Parent" (Mother or Father) with IsSubscriber = 1. I also enter all of their children in this same table, with IsDependent = 1.

I then have a Relationship table that relates each child to the appropriate parent record in the Contact table.

I need to assign a sequence number to each child ONLY if they were a multiple birth (twins, triplets, etc.; all have the same DOB). I've been successful at writing a query using ROW_NUMBER(), but it includes the single births (no other child of the same parent has the same DOB).

Stripped down version of Tables and Data and my failed attempt to write a query to do what I want:

IF OBJECT_ID('TempDB..#Contact','U') IS NOT NULL
DROP TABLE #Contact
CREATE TABLE #Contact (
ContactId INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
, IsSubscriber BIT

[Code] ....

This is as close as I can seem to get.

View 5 Replies View Related

SQL 2012 :: Populate Int Column With A Sequence Of Numbers But Sorted By Another Field

Oct 8, 2015

The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).

While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.

What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.

Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;

View 2 Replies View Related

Data Mining :: Updating Two Columns With Sequence Numbers With Where Clause

Jun 29, 2015

I have a question  in SQL server. For example I have a table which has two column like following table and I don't know how can I update theses two column with identity numbers but just the fields which are equal 111.

Example table:
numez numhx
111 111
111 111
0 111
111 0
111 0

and my results should be:
numez numhx
1 2
3 4
0 5
6 0
7 0

View 3 Replies View Related

Transact SQL :: Insert Rows Into A Table For Missing Sequence Numbers

Jul 29, 2015

In a t-sql 2012 sql update script listed below, it only works for a few records since the value of TST.dbo.LockCombination.seq only contains the value of 1 in most cases. Basically for every join listed below, there should be 5 records where each record has a distinct seq value of 1, 2, 3, 4, and 5. Thus my goal is to determine how to add the missing rows to the TST.dbo.LockCombination where there are no rows for seq values of between 2 to 5. I would like to know how to insert the missing rows and then do the following update statement. Thus can you show me the sql on how to add the rows for at least one of the missing sequence numbers?

UPDATE LKC
SET LKC.combo = lockCombo2
FROM [LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type
JOIN TST.dbo.Locker LKR ON SCH.schoolID = LKR.schoolID AND A.lockerNumber = LKR.number

[Code] ....

View 10 Replies View Related

How To Append A New Values Like Uniqueidentifiers, Sequence Numbers To Data Flow?

May 9, 2006

Hi,

I would like to know different possible ways in appending extra values like new uniqueidentifiers, sequence numbers, random number. Can you please tell what type of data flow components helps us ?



View 5 Replies View Related

Transact SQL :: How To Change Auto-sequence Numbers To Match Sorted Data

Jun 15, 2015

I have a database that has entries that I want sorted by date order. Each entry has an auto ID number allocated (primary key auto sequencing), which I want to change to reflect the sorting (so the first date has the first auto ID number and so on).I've gone into the database and sorted the entries as I want them. Then I've gone into Design View to delete and restablish the primary key autosequence. However, it is not keeping the date order in the database (ie entry ID 3140 date is 12/06/2015, but 3141 is 02/02/2012). How do I get it to maintain the order?

View 3 Replies View Related

Sequence Order

Apr 27, 2008

Hi all.
In one table fields vales are 9, 5, 7, 2, 5, 6, 44, 67, 789.
 I want to get 5 high values so please can you write sql query for this question.
 
Thanks.
 
Zahyea.

View 1 Replies View Related

ORDER IN Particular Sequence

Jan 23, 2008

Hi,

I have a table in which there is 5 column ...one with numbers like...1,2,3,4,..20...and one column is with description....and other column with other details...

Now I want to disply my results in sequence followed by 1,2,3,4,5,17,18,15,16,10,11,20 with all other columns...so can anybody suggest me what to do..?

View 3 Replies View Related

Order By Sequence

Oct 19, 2007

Hi fellows
I have these values with a PK and length.
PK length
dd1 2
dd1 4
dd1 6
dd1 8
dd1 9
dd2 1
dd2 3
dd2 6
dd2 9
dd3 3
dd3 4
dd3 9
dd3 15
dd3 25


I want to create another column showing a sequency like this THIS MEANS THAT WHEN I CHANGE THE PK I HAVE TO START WITH CERO.

PK FROM TO
dd1 0 2
dd1 2 4
dd1 4 6
dd1 6 8
dd1 8 9
dd2 0 1
dd2 1 3
dd2 3 6
dd2 6 9
dd3 0 3
dd3 3 4
dd3 4 9
dd3 9 15
dd3 15 25


CHEERS

View 4 Replies View Related

SQL Server 2008 :: Find Out Duplicate Order Sequence

Jun 30, 2015

In my asp.net project there are about 100 drop down list.I created a table to store data for drop down list in which including [DropdownID],[Order Sequence] and [Description] three columns. The sample like below. Data was input manually by a user. How to code to find out duplicate [OrderSequence]?

DropdownID--OrderSequence--Description
1-------------0--------------AAA
1-------------1--------------BBB
2-------------0--------------YYY
2-------------1--------------XXX
2-------------2--------------QQQ 'DUPLICATE OrderSequence
2-------------2--------------WWW 'DUPLICATE OrderSequence
2-------------3--------------RRR

View 2 Replies View Related

SQL 2005 Bug? Not Follow The Order By Sequence In View...but SQL 2000 Does!

Aug 24, 2006

I have a table:

CREATE TABLE [dbo].[tx1]( [f1] [nvarchar](50) , [seq] [int] IDENTITY(1000,1) NOT NULL ) ON [PRIMARY]

SELECT *
FROM dbo.tx1
ORDER BY seq DESC

go

f1 seq
zz 1003
uu 1002
kk 1001
yy 1000


create view vx1 as
SELECT top 100 percent *
FROM dbo.tx1
ORDER BY seq DESC
go
select * from vx1

yy 1000
kk 1001
uu 1002
zz 1003







View 7 Replies View Related

Sql SORT Order Not Working On Numbers?

Jul 20, 2005

Hello all... I'm using asp to get records from an access database, verysimilar to the way datagrid would work. The title of each column in my tableis a link that alternates the sort order between ascending and descending...my problem is that text WILL change its sort order just fine but nubers arenot always in order. ie: if sort order is ASC (ascending) I might see 2000,234, 789 (should be ordered but its not). I'm guessing that ASP is handingthe string as a text string (?) and getting confused, is there a way toforce ASP into treating the string as numerals if this is the case? anyother ideas? Thanks so much.here is one of my sql commands in case you want to see it. "sort" is avariable containing the recordset to sort by depending on which link isclicked. I hope I didn't confuse the whole issue because of a lack ofcaffiene over here :)strsql = "SELECT * FROM comments ORDER BY " & sort & " DESC"Thanks of the help, much appreciated.Eno

View 2 Replies View Related

Sequence Clustering Model Processing Fails Due To Sort Order

Dec 18, 2007


I am using Sequence Clustering algorithm. (I've built several models with Clustering algorithm and Decision Trees for this client, which work fine.).

Background: Sequence data must be stored in a nested table, which can have only 1 non-key attribute.
I specify a mining model structure with the nested table key as the datetime, and the nested table discrete prediction column as [sort name] . this builds the model fine.


When I try to process this data mining model, I get Process failed: "Errors in the OLAP storage engine: The sort order specified for distinct count records is incorrect".

Iit may be that OLAP distinct count requests numerical data type, but not from the examples I've seen. Tried this anyway €“ doesn€™t work on numeric either €“ same problem.
Any Suggestions?

View 1 Replies View Related

Need Help, How To Change Datatype In Order To Sort Some Extracted Numbers From String

Apr 3, 2006

Hello,

I am trying to extract from some strings like the following strings the number and order them by that number:

Box 1
Box 2
Box 3
Box 20
Box 21
...(and so on)


The problem I am having is that I already extracted the number using

Substring([field],[starting position],[lenght])

but the output seems to be in a string format, so the order is not in an ascending order.

Thanks for any suggestions.

View 6 Replies View Related

SQL Server 2012 :: List Of Order Numbers Based On Stock Availability - Filter Results?

Dec 23, 2014

Trying to build a list of order numbers based on stock availability.

The data looks something like this:

OrderNumber Stockcode quantityordered quantityinstock
123 code1 10 5
123 code2 5 10
124 code3 15 20
124 code4 10 10

In this case I would like to output a single result for each order, but based on stock availability order 123 is not a complete order and 124 is so the results will need to reflect this.

View 1 Replies View Related

Formatting Numbers In A Mixed Column (numbers In Some Cells Strings In Other Cells) In Excel As Numbers

Feb 1, 2007

I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.

I already tried to set the value as CDbl which returns error for the cells containing a string.

The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.

Any suggestions?



View 1 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

I Need To Update A Table With Random Numbers Or Sequential Numbers

Mar 11, 2008



I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.

Thanks for any help

View 6 Replies View Related

Generate List Of All Numbers (numbers Not In Use)

Feb 21, 2007

I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:

select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)


but don't know how. Any clues?

View 1 Replies View Related

Dataflow To Excel - Convert Numbers Stored As Text To Numbers Excel Cell Error

Mar 27, 2007

I'm trying to write data to excel from an ssis component to a excel destination.

Even thought I'm writing numerics, every cell gets this error with a green tag:

Convert numbers stored as text to numbers

Excel Cells were all pre-formated to accounting 2 decimal, and if i manually type the exact data Im sending it formats just fine.

I'm hearing this a common problem -

On another project I was able to find a workaround for the web based version of excel, by writing this to the top of the file:

<style>.text { mso-number-format:@; } </style>

is there anything I can pre-set in excel (cells are already formated) or write to my file so that numerics are seen as numerics and not text.

Maybe some setting in my write drivers - using sql servers excel destination.


So close.. Thanks for any help or information.

View 1 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

Jan 7, 2007

Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...

SQL Server Express edition

Visual Studios 2005

Jet 4.0 newest upgrade

.Net Framework 2.0 (or should I use 3.0)

VS2005 Security upgrade

Anything else I need for just creating a database for my VS2005 Visual Basic project?

I was trying to use MS Access as my backend db but would like to try SQL Express



Thank you, Mark





View 7 Replies View Related

Conditional Order By - Sort Result Set By Employee Number Ascending Order

Sep 24, 2012

In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception

1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)

2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)

How shall I achieve this?

View 4 Replies View Related

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

May 19, 2015

I never paid much attention to this before but I noticed this today in a new table I was creating.

For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.

I have a date table I setup and I noticed it is NOT respecting the sort order.

I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.

I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.

Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?

View 8 Replies View Related

Calculate Total Amount Of Order Details Based On Particular Order

Apr 10, 2014

I have a query that calculate the total amount of order details based on a particular order:

Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID

My question is what if I wanted to create a formula to something like:

UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount

Do I need to create a function for that? Also is it possible to have m y query as a table variable?

View 7 Replies View Related

Default Sort Order - Open Table - Select Without Order By

Mar 27, 2008

Hi!

I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.

The table returns the data in the same order in SQL Manager "Open Table"

So I started to wonder what deterimins the sort order when there is no order by clause ?

I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.

Peace.

/P

View 5 Replies View Related

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

Jan 4, 2008

Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.

below is a Stored Procedure that i have wrote in creating a PO:



CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)

AS

BEGIN

INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)

END



SET @POno = @@IDENTITY

RETURN


However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?


CREATE TRIGGER trgInsertPOItem

ON PurchaseOrderItem

FOR INSERT

AS

BEGIN


'What do i entered???'
END

RETURN


help is needed asap! thanks!

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved