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:
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?
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.
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.
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
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
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.
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 ?
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.
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.
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%'
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
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??
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.
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
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!
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"
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.
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
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)
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
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>
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.
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
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)
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
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
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
