Query Help - Sequential Numbers

Apr 27, 1999

I have a query which returns information about transactions similar to this:

Select account, trans_code, quantity
from ledger_table

This returns something like:

acct trans_code quantity
----- ---------- ------------
2 2 1000
2 3 500
2 3 300
3 2 100
3 2 500
etc.

What I need to do is add a lot number for each acct/trans code type. This is merely a sequential number for the transaction. This changes the output as follows:

acct trans_code quantity lot
----- ---------- ------------ ---
2 2 1000 1
2 3 500 1
2 3 300 2
3 2 100 1
3 2 500 2
3 3 1000 3
3 5 200 1
etc.

The lot number is reset for each grouping.

Does anyone have a way to do this outside of a cursor or temp table?

TIA, any help greatly appreciated.

View 1 Replies


ADVERTISEMENT

SQL Query - Find Block Of Sequential Numbers

Jul 20, 2005

I have a database that is pre-populated with sequential part numbers.As people reserve the parts I update a flag to show the # is no longeravailable. Now they want the ability to take out a block of "x"number of sequential part numbers - say for example 5.If my database had the following numbers available:101104105110111112113114It should return 110 thru 114 and then I would write an update queryto change the flags to 1 (checked out).I have only been able to return the first "x" number of records - havenot been able to make sure they are stepped sequentially - with thefollowing:SELECT ID_ITEM From PARTNO_CHKOUT_SPECIAL M Where (Select Count(*)FROM PARTNO_CHKOUT_SPECIAL NWHERE N.ID_ITEM <= M.ID_ITEM) >= 0 AND TYPE_REC=1 ANDFLAG_CHECKED_OUT=0 {maxrows 5}The above would return 101, 104, 105, 110, 111I tried using an (N.ID_ITEM+1)-M.ID_ITEM=0 to try stepping and geterrors, probably incorrect syntax. Can I do this in an SQL statement?

View 6 Replies View Related

Simple Query To Create Column Of Sequential Numbers

May 2, 2006

Hi,
Can anyone tell me offhand the simplest/most elegant way of updating an integer column to a sequential column of numbers with a query?

e.g given

intval | Description| Cost
0 | Descvalue0| 4.32
2 | Descvalue2| 4.33
3 | Descvalue3| 4.34
8 | Descvalue8| 4.35

change it to:

intval | Description| Cost
0 | Descvalue0| 4.32
1 | Descvalue2| 4.33
2 | Descvalue3| 4.34
3 | Descvalue8| 4.35

I think it might need a stored proc..

Many Thanks
greg

View 2 Replies View Related

T-SQL (SS2K8) :: Select Query With Records And Sequential Numbers

Apr 5, 2014

I have a problem. In my database I have the following numbers available:

101
104
105
110
111
112
113
114

What I need is to get a select query with records and sequentials numbers after it like:

101 0
104 1 (the number 105)
105 0
110 4 (the numbers 111,112,113,114)
111 3 (the numbers 112,113,114)
112 2 (the numbers 113,114)
113 1 (the numbers 114)
114 0

How can I do It?

View 2 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

Assign Sequential Numbers

Mar 17, 2004

I am trying to automatically insert records into my existing customer table. Is there a way when I insert these new records and assign the customer number that it can sequentially pick the next available unique customer number for each record that is inserted? for example the first record would be customer number 100, the next 101, and so on? Please advise.

View 4 Replies View Related

Sequential Numbers In Column

Oct 25, 2013

I have a table with 13,000 rows, in one column called Prioirty each row has a value of 1.

Is it possible to use SQL to replace all of these '1' values with a sequential list. Example 1,2,3,4,5,6,7....all the way to 13,000?

View 3 Replies View Related

Generating Auto Sequential Numbers

Jul 23, 2005

I am using sybase aSE12.5. I have a table with only one column withdatatype char(2). when i query this table to select all the records, ishould get these records in the ascending order and they should be numbered, i.e, the o/p should look something like thiscolumn_name------ --------1 AB2 AC3 ADand so on.I cannot add an extra column and i need this to be done in a single query.--Message posted via http://www.sqlmonster.com

View 2 Replies View Related

Create Sequential Numbers In A Column

Jul 20, 2005

I have a temp table that's populated with an insert query in as toredprocedure. The temp table has a uniqueID as the primary key.In that table I have a column SortOrder.What I want to do is to create a sequential number in SortOrder butonly for records matching a WHERE statement, for example:(pardon the shorthand...)Insert *.tblPermanent into tblTempIf myField = 1 thenSortOrder = 1(2,3,4,5,.....etc.)elseSortOrder = 0Thankslq

View 1 Replies View Related

Populating Empty Table With Sequential Numbers?

Jan 15, 2015

I have a column within a table which is already truncated/deleted all records within (Microsoft SQL 2008). I have to now populate the column with sequential numbers up to 50,000 records arbitrary numbers (doesn't mater) up to 7 characters.

what SQL statement I need to write that will automatically polulate the newly empty table with A000001,A0000002,A0000003, or any form for that matter etc so that I can sort number the records within the table.

I have approximately 50000 records which I need to sequentially entered and I really dont want to number the column manually via hand editing.

View 5 Replies View Related

Update Column With Increasing Sequential Numbers

Feb 18, 2008

Here is my problem. I have a table with 4 columns id1,id2,id3,boxnum. Here is some sample data.

id1 id2 id3 boxnum
1 1 1

1 1 1
1 2 1
1 2 1
1 2 1
2 2 2
2 3 4

What I need is to be able to update boxnum with sequential numbers based on the unique set of values from the id columns. So my output would be this.


id1 id2 id3 boxnum
1 1 1 1

1 1 1 2
1 2 1 1
1 2 1 2
1 2 1 3
2 2 2 1
2 3 4 1

View 6 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

Query To Group Sequential Items

Jul 23, 2005

Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.

View 9 Replies View Related

Creating A Sequential Row Number In A Query Resultset --- Is It Possible?

Feb 17, 2005

Hello everyone,

I have a stored procedure that supplies rows for a front-end DataGrid that allows custom paging. The stored procedure must return the requested "page" of rows as identified by a sproc argument. Currently, I'm loading the the query's result set into a temporary table that has an identity column as primary key. I then run a second query against the temp table that uses the identity column value to strip out the requested "page" of rows and return them to the front-end DataGrid.

I'd like to eliminate the temporary table. To do so I would need to create the equivalent of an identity column in the query's sorted results and reference this value in the WHERE clause in order to return only the requested rows.

Does anyone know of a way to generate a sequential number (starting at 1) within a query (and after the rows have been sorted by the ORDER BY)? I don't think this can be done but I put it out for those who may know better.

Thanks for your help. If you know this is impossible, that would be helpful feedback as well.

BlackCatBone

View 3 Replies View Related

SQL Server 2012 :: Query Result For Sequential Range

Aug 9, 2014

I wanted to know the best way to achieve the following results. I have a table that I need output sequential range of vouchers in a table. For instance I have the following data in a column called vouchers. The output will consist of a years worth of vouchers, so voucher numbers may contain gaps and so the need to have a sequential range that has a From and To output. The query needs to know the min and max within that numerical range and then output the next min and max range until it gets to the end.

The data looks like:
ABCD-001869202
ABCD-001869203
ABCD-001869204
ABCD-001869205
ABCD-001869209
ABCD-0018692010
ABCD-0018692011
ABCD-001869309
ABCD-001869310
ABCD-001869311
ABCD-001869312
ABCD-001869313
ABCD-001869314

Desired out put:

From To
ABCD-001869202 ABCD-001869205
ABCD-001869209 ABCD-0018692011
ABCD-001869309 ABCD-001869314

I have tried the following, but it does not quite do what I need it to do, so not sure if I am taking the right approach:

SELECT voucher vouchers,right(voucher, charindex('-', voucher) + 3) voucher
INTO #tempVoucher
FROM LEDGERJOURNALTRANS
where TRANSDATE between '10/1/2013' and '7/31/2014' and VOUCHER like 'APIN%'

[Code] ...

View 6 Replies View Related

Query Problem: Automatically Resetting A Sequential Number List After Row Deletion

Apr 7, 2004

Hello!
Got a problem I hope some clever people can help me out with..

I have a web form that displays a set of records in a grid. The grid is "paged" according to a PageNum column, with a dropdown box to change pages and buttons allowing the items to be moved up or down a list within a page or moved between pages. So the backend table (simplified) looks something like this

PageNum ItemNum ItemDescription
----------- ----------- -------------------
1 1 aaaaaaa
1 2 bbbbbbb
1 3 cccccccc
2 1 ddddddd
2 2 eeeeeee
2 3 ffffffffffff
3 1 ggggggg
3 2 hhhhhhh
3 3 iiiiiiiiiiiiiiii

The problem is when I want to delete a page - I need the page numbers to automatically resequence themselves, so for example, If I delete "Page 2" (i.e. delete rows where PageNum = 2), all items on "Page 3" become "Page 2" (and any items on "Page 4" become "Page 3" etc).

This has proved straightforward to when deleting an item from a particular page, and can resequence ItemNum thanks to a clever bit of code found on SQLteam.com:

DECLARE @intCounter int
SET @intCounter = 0
UPDATE <Item Table>
@intCounter = ItemNum = @intCounter + 1
WHERE Pagenumber = <Currently Selected Page>

However I haven't been able to adapt this to resequence the Page number, as this involves resequencing blocks of numbers. The closest I can get is:

DECLARE @intCounter int
SET @intCounter = 1
UPDATE <Itemtable>
SET @intCounter = PageNum = CASE
WHEN @intCounter = PageNum - 1 THEN @intCounter + 1
WHEN @intCounter = PageNum - 2 THEN @intCounter + 1
ELSE @intCounter
END

But this doesn't quite work.
Anyone got any other ideas??

Thanks
Greg

View 3 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

Adding Numbers While Doing Query

Mar 28, 1999

I used this with an access DB and would like to re-write it to work with
sql.

sql = "SELECT funds.itemdisc, Sum(IIf(funds.status='Credit'"
sql = sql & ",CDbl(funds.fundamount),0,)) AS income, Sum(IIf funds.status='Debt'"

sql = sql & ",-1*CDbl(funds.fundamount),0)) AS outgo"
sql = sql & " FROM funds "
sql = sql & "GROUP BY funds.itemdisc"

What I am doing here is collecting the columns and using status as
credit or debt, if its debt I am subtracting from outgo if it is credit
I am adding to credit. This also will only select records so that
there are not any double listings. Here is an example.

DB
desc status fundamount
bill debt 10.00
bill debt 15.00
in credit 10.00
bills debt 5.00
in credit 5.00
paper debt 5.00


When I do the query I should have this

desc fundamount
bill -25.00
bills - 5.00
paper - 5.00
in 15.00

Something like that...

What do you think?
Thanks in advance.

View 1 Replies View Related

How To Get Row Numbers From SQL Express 2005 Query?

Nov 11, 2007

 Hi,  I'm using SQL Express 2005 and VIsual Studio 2005, and this sounds like it should be easy. I'm trying to return the row numbers of my queries, but if I use the Row_Number() command I get the following error: "The OVER SQL construct or statement is not supported." So, is Row_Number() not supported in SQL Express 2005? If not, how can I return row numbers with my queries? Or, more specifically, how can I return a limited result set from a query (i.e. Return only row number 10-20)? My current command is as follows:  SELECT (SELECT Row_Number() OVER (ORDER BY UserName) As RowNumber), *FROM UsersORDER BY UserName  Thanks for any advice you can offer!  

View 8 Replies View Related

Automatically Adding Numbers To Query

Jun 4, 2008

Hello everyone!

I have a question for you. I have a product database with a lookup that pulls by product number. All my product numbers are made up the same way. IE. N59840, N00951, N00951. ect.

I have a stored procedure that looks up by that product number with a "LIKE" statement that looks like this.

WHERE ([Product#] LIKE '%' + @PRODUCTNUM + '%')

Which has this problem if someone types in "852" it returns

N00852
N05852
N98852

Is there anyway that I can have SQL put in zeros to fill up the 5 number spots so "852" brings up "00852" or "5852" brings up "05852"

I hope this makes sense.

Thank you for your help!!!

View 4 Replies View Related

Interesting Requirement! How To Query Port Numbers From 120 Servers!

Dec 7, 2004

I need to document the port numbers from nearly 120 sql servers in the network. Is there any query that I can use to get this info??

Thanks,

View 13 Replies View Related

Search A Column Of Comma Delimited Numbers (was Help With Sql Query)

Nov 30, 2005

Hi, I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query:

SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%'

Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help. Thanks in advance. I have tried using IN instead of LIKE, but that didn't seem to get the results I wanted either.

View 4 Replies View Related

SQL 2005 Management Studio: Line Numbers In Query Window.

Dec 19, 2005

Can anyone tell me if you can display line numbers in the query windowof SQL 2005 Management Studio and if so how do I go about doing it?Thanks a bunch. TFD

View 2 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

Sequential Guids

Oct 31, 2007

 Hello,I am creating a new database and I was advised to use Sequential Guids.I was reading some information and, as far as I understood, I can use NEWSEQUENTIALID. This can be used when I have a uniqueidentifier column as the key of a clustered index to avoid fragmentation during insert. Ok, so I use NEWSEQUENTIALID instead of NEWID.But I will use LINQ most of the time instead of Stored Procedures.So can I specify in my tables scripts to use Sequential Guids when, for example, a record is created? And am I right when using Sequential Guids?Here is a part of my code:-- Blogs ...
create table dbo.Blogs
(
BlogID uniqueidentifier not null
constraint PK_Blog primary key clustered,
Title nvarchar(400) null,
Description nvarchar(2000) null,
CreatedDate datetime null
)

-- Posts ...
create table dbo.Posts
(
PostID uniqueidentifier not null
constraint PK_Post primary key clustered,
BlogID uniqueidentifier not null,
AuthorID uniqueidentifier not null,
Title nchar(1000) null,
Body nvarchar(max) null,
UpdatedDate datetime not null,
IsPublished bit not null,
constraint FK_Posts_Blogs
foreign key(BlogID)
references dbo.Blogs(BlogID)
on delete cascade,
constraint FK_Posts_Users
foreign key(AuthorID)
references dbo.Users(UserID)
on delete cascade Thanks,Miguel

View 2 Replies View Related

Sequential Queries

Apr 8, 2008



In Foxpro I would often run sequential queries on data. Each query would store the results in a cursor, which would be the datasource for the next query. It was an easy way to write several simple queries to accomplish something complex.

Like this:

select <fields> from table1 where <condition> into cursor work1
select <fields> from work1 where <condition>

How do I accomplish this in TSQL?

View 4 Replies View Related

T-SQL Sequential Record Navigation

Jan 25, 2007

Hi everyone I stumbled across this problem recently and have tried to figure out a good solution and have come up with nothing.
Environment:  ADO.NET, ASP.NET 2.0, MS SQL 2000, C#
Problem:  I have a set of data which I order according to two columns of data in the set.  One column is a varchar or a date value (depending on what the user has chosen from GUI controls) and the other is an identity column.  This dataset contains lots of data so its not feasible to pull the entire dataset to the client, also everything must be performed via ADO.net (no stored procedures).  When the user selects one of the records I load another page and drill down into the record details (think of this as the record details page).  This is fine and easy but on this record details page I would like to keep a Next and Previous button so users can move to the next record in the set (remember this set is sorted somehow on the previous page).  My question is how can I know what next record should be?   It would seem I need to attach a sequential number to the rows of data so I can easily grab the next one in the set.  Solutions to this seem to make use of a temp table which I dont think is possible via ado.net.
Is there a decent performing way to do this through ado.net?
 Thanks for all insight.
 

View 4 Replies View Related

Create Non-Sequential Unique ID (EAN?)

Feb 28, 2007

Hi all,I might be getting this all wrong but bear with me.
I need to create some kind of Unique field in my DB that is
nonsequential.  This is because I need it to be difficult to guess
ids if you have an example in front of you.I have looked at
8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these)  Can anyone tell me
how many uniques I can get out of this system?For my ID: I have looked at something along the lines of:

Hex(row_id) + "T" + Hex( Trimmed(EAN)
)                                  
The "T" serves to split the numbers for when I am converting back.
So for example:row_id   EAN_code   Hex(row_id) + "T" + Hex( Trimmed(EAN) )
------------------------------------------------------------------------------------------
3166    00031663    C5ET7BAF   
3167    00031673    C5FT7BB9   
3168    00031686    C60T7BC6   

Is this too easy to guess (once you can tell there are two hex numbers there?)
What do people think?
 Thanks,Pete  

View 4 Replies View Related

Getting Sequential Rows From A Calculation

Apr 4, 2008

I am currently trying to get a dataset of sequential decreasing calculations, but they all end up on one row.  I need each of the secondary selects statements to show up as separate rows, any idea?Select

(select Rate * @Premium from ProfitLossTable where rateID =1)
, (select Rate * @Premium - @premium from ProfitLossTable where rateID =1)
,(select @premium - Rate * @Premium from ProfitLossTable where rateID =3)  

View 3 Replies View Related

SQL: Sequential Random Select

Mar 8, 2006

Hello--I need to create a SQl statement (SQL SERVER 2000) which will do the following:

read table1 sequentially
select every "nth" record
write each selected record to and output table

Your help is greatly appreciated.
thanks,
MB

View 3 Replies View Related

Sequential Numbering Problem

Jul 27, 2006

I've created a sproc that will provide a recordset for an Access report via a pass-thru query. The report is a production schedule.
Some of the runs on the schedule have a note associated with them. I need
to be able to number these notes, so that they can be displayed in the report
bibliography style. When I populate the data in the sproc, if the "notes"
field in my table contains any data I display a 1, else it's a 0
Like:

SELECT NOTE_FLAG = CASE WHEN ISNULL(SCHED_NOTE,'')<>'',1,0
FROM MASTER_SCHEDULE


The problem is, I really need to display sequential numbers instead of 1's

If I create a base set with this:

CREATE TABLE #TMPRST (
RECID INTEGER,
PRODUCT VARCHAR(10),
QTY FLOAT,
NOTE_FLAG INTEGER)

INSERT INTO #TMPRST
SELECT 1, 'ABC123', 4, 0
UNION ALL
SELECT 4, 'DEF123', 5, 1
UNION ALL
SELECT 5, 'ABC456', 12, 0
UNION ALL
SELECT 13, 'PQR789', 10, 1



How do I go back and convert the note_flag column to read
... 0
... 1
... 0
... 2

View 14 Replies View Related

Sequential Number In A View?

Oct 17, 2012

We are integrating all our applications/databases into one application/database. During the transition phase, I need to create a number of views based on the new database that mimic the old tables of the old databases, so the old programs can continue to function until they are gradually replaced.

In one of the views, I need to generate a sequential number. The value is unimportant, as long as it is unique in the dataset; strictly spoken, it even doesn't need to be sequential:

eg:
SELECT * FROM myView

should give

Code:
col1col2...id
lalacar..1
bababike..2
....
zsrdpen..896
ghrtink..897
SELECT * FROM myView ORDER BY col2

should give

Code:
col1col2...id
bababike..1
lalacar..2
..
ghrtink..45
..
zsrdpen..396
....

The view is created based on a number of tables.

View 1 Replies View Related







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