How Can It Automatically Generating A Ordered Number
Jul 5, 2006
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
View 1 Replies
ADVERTISEMENT
Aug 8, 2007
Hi,
I am new to SQl Server, i have created a logical model for a database that i am creating for a project. Is there a way i can assign automatic values to my Primary Key (PK) Column other than using an identity?
For example i have a table called indicator for which the Primary Key (PK) is indicator_identifier; i want the primary key values to be
ind_0001
ind_0002
ind_0003
Please note that i may be populating the tables from a VB.NET form
Please can anybody help me?
View 2 Replies
View Related
Jan 20, 2005
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.
Thanks for your contribution.
View 2 Replies
View Related
Aug 29, 2001
Hello,
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.
Thank you so much.
NK
View 3 Replies
View Related
Oct 3, 2007
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.........
Col1 Col2
---- -----
1 1
1 2
1 3
1 4
2 1
2 2
3 1
3 2
3 3
3 4
3 5
4 1
5 1
6 1
6 2
7 1
Thanks In Advance,
Rahul Jha
View 14 Replies
View Related
Nov 14, 2007
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
Thanks,
Rahul Jha
View 14 Replies
View Related
Jul 19, 2000
Is there wa way to generate unique sequence numbers in SQL server?
(just like the way it is in Oracle i.e. seqeuence and then use nextval)
View 2 Replies
View Related
Apr 9, 2008
:eek:
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.
Hope this makes sense to all.
BTW, am using SQL 2000 at the moment.
Thanks in advance for any help!
View 11 Replies
View Related
Aug 17, 2007
Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?
Thanks you very much!!!!
Sergio
View 2 Replies
View Related
May 8, 2008
Can you help me with SQL issue I€™m stuck on?
I wish to take source data that looks like this:
Invoic_num
Line_num
6658
0
6658
2
6658
8
7721
2
7721
3
And rebuild the line numbers like this:
Invoic_num
Line_num
6658
1
6658
2
6658
3
7721
1
7721
2
This seems completely impossible to me. So I was thinking that maybe a second procedure using update could be run against the table after the initial build.
View 10 Replies
View Related
Aug 23, 2004
hey all,
I've posted about this before so apologies to anyone who read it the first time.
I want to have an ID column in my table, but I want the automatically generated number to have a prefix such as ABC. I'd also like to be able to change this prefix from an asp page depending on what is selected from a drop down.
Is any of this possible? I'd really appreciate a reply, even if it's to say this can't be done. Thanks
View 2 Replies
View Related
Feb 11, 2014
Other than right-clicking on each individual table in SSMS and generating a CREATE script, is there a simple way to generate CREATE TABLE scripts for tables within a given database?
Background: I have a bunch of tables in one database, and I would like to add tables to a second database that have the same names and basic structures of some of the tables from the first database.
I do not need to transfer any data from the tables, this is a seperate project that will use a similar data structure. I just want to generate the CREATE TABLE scripts for 30ish tables within the first database, and then I'll tweak the scripts as appropriate and run them against the new database.
[URL] ....
View 7 Replies
View Related
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
Jun 25, 2007
Hello Experts. You may have more luck at this than me.
I am interested in finding the quantity of items that were ordered alone. I have an orderid field and a product field. So the count of the orderid has to equal one and the have them grouped by product.
Example of how data looks like
I am looking for transactions like orderid 3 and 5.
OrderID
Product
1
hotdog
1
burger
1
taco
2
burrito
2
snack
2
chips
3
burger
4
hotdog
4
burger
4
taco
5
burrito
6
snack
6
chips
When i run
SELECT product, count(orderid)
From Table
Where BusinessDateID = 20060725
group by product
having (count(orderid)=1)
I only get back items that were only sold once.
I am looking for a result that looks like this
Product
Ordered alone
hotdog
2
burger
3
taco
4
burrito
32
snack
12
chips
76
View 7 Replies
View Related
Sep 13, 2000
I need to export data in a specific order (e.g. the order in which the data was inserted into the table). My BCP was doing this then just started exporting in random order. I know that SQL 7 doesn't guarantee return order unless specified by an ORDER BY clause. However, that's not possible with BCP unless you use the command line BCP with the QUERYOUT option. That's my problem. I'm using the BulkCopy object in DMO and I don't see an option to order the data or use a query with an order by statement. Any suggestions?
--Buddy
View 1 Replies
View Related
Jan 31, 2008
Hi all,
I feel like I'm missing something really simple here...
I'm trying to write an sp to return a list of countries alphabetically to populate a web drop-down list in a form. However, since most people using the form will be from USA, I want "USA" to appear as the first row, then the rest should be alphabetical, e.g. ("USA", "Afghanistan", "Albania"... "Zimbabwe")
I'm using a UNION query, but it's ordering the result set so that USA appears alphabetically, not as the first row. I'm not using an ORDER BY clause.
Here's the code I'm using:
CREATE PROCEDURE GetCountries AS
SELECT Country_Name
FROM Countries
WHERE Country_Name = 'USA'
UNION
SELECT Country_Name
FROM Countries
WHERE Country_Name <> 'USA'
GO
I also tried selecting into a temp table and doing a UNION that way, but got the same results.
View 3 Replies
View Related
Jul 23, 2005
We have a 10 digit primary key value in this format: M000123456. Theorder for this key is first determined by positions 3 and 4 in thisexample, then positions 1 and 2. So a brief sample of correct orderingwould look like this:M000001501M000011501M000021501M000001601M000011601M000021601Now my question: how can I use a BETWEEN (or > and <) in my WHEREclause to get a range of values for this column? I use the followingORDER BY clause to control how the results are sorted, but I can't getthe same logic to work with BETWEEN in a WHERE clause.ORDER BY SUBSTRING(<fieldname>, 7, 2), SUBSTRING (<fieldname>, 5, 2)How do I return values between M000011501 and M000011601 for example?
View 3 Replies
View Related
Jan 18, 2008
I€™m trying to follow examples from: http://www.sqlmag.com/Articles/ArticleID/49240/49240.html?Ad=1 for an update statement that needs to run in a specified order.
Here€™s my problem:
I have a table:
TopicActivity
PK €“ DayNum int (this is a date in YYYYDDD format)
PK €“ TopicId int
Visits int
LifetimeVisits int
LifetimeVisits is a new column that I want to calculate the value using the previous Visits column data.
Sample Data:
DayNum TopicId Visits LifetimeVisits
2008001 1 5 0
2008002 2 1 0
2008002 1 3 0
2008003 1 10 0
I want the end result to look like this:
DayNum TopicId Visits LifetimeVisits
2008001 1 5 5
2008002 2 1 1
2008002 1 3 8
2008003 1 10 18
Here is the query I€™ve been trying:
WITH ordered AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY TopicId ORDER BY DayNum) AS RowNum
FROM TopicActivity
)
update ordered set
ordered.LifetimeVisits = ordered.Visits +
isnull((select o2.LifetimeVisits
from ordered as o2
where o2.TopicId = ordered.TopicId
and o2.RowNum = (ordered.RowNum - 1)),0)
The update doesn€™t seem to be happening in order as LifetimeVisits seems to have random results every time I run the query. (Usually the first couple of rows are right. I€™m dealing with thousands of records) I can€™t use a cursor as the query takes way to long.
I'm running SQL Server 2005.
View 6 Replies
View Related
Jan 16, 2014
Installed the Northwind database for data to practice with. I'm trying to combine data from several tables to make the orders table more readable. Meaning, I'm trying to replace the EmployeeID field with the combination of the firstname and lastname fields from the Employees table. Everything works fine until I try to sum the Unit price field from the [Order Details] table. Using just a SUM() function or the Select statement below causes the error and any combination of fields in the Group By command don't correct it. It's clear that I'm doing something wrong, I'm just not sure how to get the data I want or use the group by command properly. Query below:
Select o.OrderID, c.companyName, e.firstname + ' ' + e.lastname EmployeeName, o.orderdate, s.companyName,
o.Freight, o.shipName, o.ShipAddress, (Select Sum(od.UnitPrice) from [Order Details] od where od.OrderID = o.OrderID)as Amount
from orders o, customers c, Employees e, Shippers s, [Order Details] od
where o.CustomerID = c.CustomerID
[Code] ....
Running the first query (with the select statement) works, but returns a row for each of the the items that was ordered for that OrderID and NOT using the Group By. I would like to have the SUM() of the items ordered in one row. Is this possible?
View 6 Replies
View Related
Jul 23, 2005
When a nonunique nonclustered index is built on top of a clusteredindex, is it guaranteed that the bookmark in the nonclustered indexwill be kept in the same order as the clustered index?Here's an example to demonstrate my question:CREATE TABLE indextest (col1 int NOT NULL,col2 int NOT NULL,col3int,col4 int)ALTER TABLE indextest ADD PRIMARY KEY CLUSTERED (col1,col2)CREATE INDEX ix_indextest ON indextest (col1,col3)GOINSERT indextest VALUES (1,2,1,1)INSERT indextest VALUES (1,3,2,1)INSERT indextest VALUES (1,4,2,1)INSERT indextest VALUES (2,1,1,1)INSERT indextest VALUES (1,1,1,1)SELECT col1,col2 FROM indextest WHERE col1=1 AND col3=1DROP TABLE indextestThe select statement above is covered by the nonclustered index, sothat index is used. However, the nonclustered index is defined only toensure the ordering of col1 and col3 within the index; col1 and col2follow within the index as the bookmark to the clustered index. When Irun this query, my desired result is to have the records appear in theorder supported by the clustered index:1,11,2As it happens, the result I got was indeed in that order, but I don'tknow if it was mere coincidence, or if the bookmark in the nonclusteredindex is maintained in the same order as the clustered index. If Iwant to ensure the above order, is it sufficient to have thenonclustered index defined as above, or do I need to define it as:create index ix_indextest on indextest (col1,col3,col2)just to be sure that the results are returned in ascending order forcol1,col2? If the two-column index is sufficient, is it guaranteed tostill be sufficient in SQL2005 and future versions of SQL Server, or amI better off adding the third column just to be safe?Thank you,--Dennis Culley
View 4 Replies
View Related
Mar 7, 2008
How would i find the 3rd date in a sequence of dates, that is ordered?
Example
Date1 = 08-01-01
Date2 = 08-02-01
Date3 = 08-03-01
Date4 = 08-04-01
i need to retrieve Date3
View 4 Replies
View Related
Oct 19, 2007
I'd like the "OUTPUT DELETED.* INTO target table" resulting from a DELETE to occur in the same order as the clustering key of the target table. Is this possible?
View 6 Replies
View Related
Nov 5, 2012
I would like to do a SELECT query and have the return set ordered by the order which the rows are physically stored in the table.
Specifically, something like:
Select a.*, b.normalizedColumn
FROM table a
JOIN table b
(a.id = b.id)
ORDER BY "Physical order of table a rows"
I believe if there is a clustered index on the table, I can just order by that index and it SHOULD be the physical order of the table.
(I would like to see this as I am generating mock data and would like to verify the "randomness" of the data inserted).
View 6 Replies
View Related
May 13, 2008
How do you all recommend storing ordered pairs in SQL Server 2005? I plan to add one record for every data point but this will generate many records and requires an extra field to relate the points together. Are there any better ways to do this? Can the data still be searchable or does it have to be unpacked first?
View 2 Replies
View Related
Jul 23, 2005
Ive been playing with this for a few days and thought I might thow itout for seggestions.I have Several Queries that need counts returnedThe Queries are Mutually Exclusive meaning whatever Query they returnin first they cannot be included in the counts of any queries belowthem.This set of queries for exampleSelect ID From Customers where FIRST_NAME = 'Chris' (would return say150)Select ID From Customers where ST='OH' (This would retunr say 50, BUTRun alone it might return 70, however 20 of those were in the firstQuery so they arent to be retunred in this result set.The total for Bot Queries would be 200But If I reverse it like soSelect ID From Customers where ST='OH' (This now returns 70)Select ID From Customers where FIRST_NAME = 'Chris' (This now returns130)The total of course for BOT Queries is 200 but I dont need that total Ineed the total for EACH Query depending on its orderingWhat I need are the single counts depending on the order in which thequeries are runIt seems like a recursion problem, but It might go past 32 level so Icant use recursive SQL ( I dont think )I've thought of (or tried to think how to use Not In, Not Exist, etcbut still dosent come up with the results....)How Can I grab the counts for each Query ?Chris
View 5 Replies
View Related
May 3, 2006
How can I create a function that returns hierarchical data from a table with this structure:
- CategoryID
- CategoryName
- CategoryFather
I want to bring the result set like this...
CategoryID | CategoryName | CategoryFather | HierarchicalLevel
1 | Video | 0 | 0
2 | DivX | 1 | 1
3 | WMV | 1 | 1
4 | Programming | 0 | 0
5 | Web | 4 | 1
6 | ASP.Net | 5 | 2
7 | ColdFusion | 5 | 2
How can I do this? Does anybody has a sample code? I need this on SQL Server 2000 and if it's possible (but not too necessary) in SQL Server 2005.
Thanks.
View 9 Replies
View Related
May 15, 2008
Greetings,
I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.
How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name, A.index
FROM
...
...
ORDER BY A.[Index], A.Name ASC
END
ALTER PROCEDURE [dbo].[MyProcedure]
AS
BEGIN
SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]
END
Thanks
View 14 Replies
View Related
May 1, 2008
As the topic suggests I need the end results to show a list of shows and their dates ordered by date DESC.
Tables I have are structured as follows:
SHOWS
showID
showTitle
SHOWACCESS
showID
remoteID
VIDEOS
videoDate
showID
SQL is as follows:
SELECT shows.showID AS showID, shows.showTitle AS showTitle,
(SELECT MAX(videos.videoFilmDate) AS vidDate FROM videos WHERE videos.showID = shows.showID)
FROM shows, showAccess
WHERE shows.showID = showAccess.showID
AND showAccess.remoteID=21
ORDER BY vidDate DESC;
I had it ordering by showTitle and it worked fine, but I need it to order by vidDate.
Can anyone shed some light on where I am going wrong?
thanks
View 3 Replies
View Related
Oct 15, 2007
I have created a local user on Report Server Computer and the user has the administrative rights.
When i try to connect Report Server (http://xxx.xxx.xxx.xxx/reportserver) with this user's credantials. (ReportServer directory security is set -only- to Basic Authentication. ).
I get the following error.
Reporting Services Error
--------------------------------------------------------------------------------
The number of requests for "XXXServerXXXUser" has exceeded the maximum number allowed for a single user.
--------------------------------------------------------------------------------
SQL Server Reporting Services
Then i try to login using a different user with administrative rights on the machine, i can logon successfully.
The system is up for a month but this problem occured today?!? What could be the problem?!?
View 2 Replies
View Related
Sep 24, 2007
Hi
I want to enter rows into a table having more number of columns
For example : I have one employee table having columns (name ,address,salary etc )
then, how can i enter 100 employees data at a time ?
Suppose i am having my data in .txt file (or ) in .xls
( SQL Server 2005)
View 1 Replies
View Related
Jun 23, 2015
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @tableÂ
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
View 4 Replies
View Related
Oct 8, 2006
in my sql, i want to change a decimal number to percent format number, just so it is convenient for users. for example there is a decimal number 0.98, i want to change it to 98%, how can i complete it?
thks
View 4 Replies
View Related
Jun 5, 2007
Hi,
I am currently designing a SSIS package to integrate data into a data warehouse fact table. This fact table has about 70 columns among which 17 are foreign keys for dimension tables.
To insert data in that table, I have to make several transformations and lookups. Given the fact that the lookups I have to make are a little complicated, I have about 70 tasks in my Data Flow.
I know it's a lot, but I can't find a way to make it simpler. It seems I really need all these tasks.
Now, the problem is that every new action I try to make on the package takes a lot of time. At design time, everything is very slow. My processor is eavily loaded each time I change a single setting in one of the tasks, and executing the package in debug mode takes for ages. If I take a look at the size of my package file on disk, it's more than 3MB.
Hence my question : Are there any limitations in terms of number of columns or number of tasks that can be processed within a Data Flow ?
If not, then do you have any idea why it's so slow ?
Thanks in advance for any answer.
View 1 Replies
View Related
