New Unique Number In History Table
May 23, 2008
Hello,
I am using SQL Server 2005 and am having trouble with making a history table like mentioned in my earlier thread:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=102811
This is the table "People" I have created:
|PersonId (PK)|DateFrom (PK)|DateTo|PersonName|Other Attributes....
Each change to a person's attributes results in a new row formed with the same PersonId as in the row with old attributes and the Date these new attributes are valid (DateFrom). So as shown above the Primary Key is a combination of the PersonId and DateFrom as a change to a person's attributes should never happen at the same time twice.
My problem is when I want to create a new person, how do I get a new unique id? Ideally I want the a new incremented id, so that all peoples' ids are in a sequential order.
As always, thanks for the help!
View 4 Replies
ADVERTISEMENT
Apr 28, 2008
need help
how to archiv table to another table with unique number for all rows once + date time (not the second only day time +minute)
i need whan i insert to the another table add 2 more fields (unique number , date_time )
this is the table 1 i select from
ID fname new_date val_holiday
----------------------------------------------------
111 aaaa 15/03/2008 1
111 aaaa 16/03/2008 1
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1
111 aaaa 20/03/2008 1
111 aaaa 21/03/2008 1
222 bbb 02/05/2008 3
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3
222 bbb 05/05/2008 3
222 bbb 06/05/2008 3
222 bbb 07/05/2008 3
222 bbb 08/05/2008 3
222 bbb 09/05/2008 3
333 ccc 03/04/2008 4
333 ccc 04/04/2008 4
this is the table 2 i insert into
----------------------------------
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 666 15/04/2008 17:03
111 aaaa 16/03/2008 1 666 15/04/2008 17:03
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 666 15/04/2008 17:03
111 aaaa 20/03/2008 1 666 15/04/2008 17:03
111 aaaa 21/03/2008 1 666 15/04/2008 17:03
222 bbb 02/05/2008 3 666 15/04/2008 17:03
222 bbb 03/05/2008 3
222 bbb 04/05/2008 3 666 15/04/2008 17:03
222 bbb 05/05/2008 3 666 15/04/2008 17:03
222 bbb 06/05/2008 3 666 15/04/2008 17:03
222 bbb 07/05/2008 3 666 15/04/2008 17:03
222 bbb 08/05/2008 3 666 15/04/2008 17:03
222 bbb 09/05/2008 3 666 15/04/2008 17:03
333 ccc 03/04/2008 4 666 15/04/2008 17:03
333 ccc 04/04/2008 4 666 15/04/2008 17:03
for evry archiv table to another table (insert) i need to get a unique number + date time (not the second only day time +minute)
next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 667 15/04/2008 17:15
111 aaaa 16/03/2008 1 667 15/04/2008 17:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 667 15/04/2008 17:15
.........................
.....................................................................667 15/04/2008 17:15
next insert ......
ID fname new_date val_holiday unique number date_time
--------------------------------------------------------------------------------------------------------------------
111 aaaa 15/03/2008 1 668 15/04/2008 08:15
111 aaaa 16/03/2008 1 668 15/04/2008 08:15
111 aaaa 18/03/2008 1
111 aaaa 19/03/2008 1 668 15/04/2008 08:15
.........................
.....................................................................668 15/04/2008 08:15
TNX
View 3 Replies
View Related
Oct 13, 2004
As part of the credit card process I have to supply a unique transaction id.
I thought the best way to do this would be to have a file which just holds a number which I grab and increment each time some one registers. Then if everyting is alright I will write the user's details a long with the transaction id to the User file.
My question is, if two people try to register and both try to grab the next number from the file at the same time, what will happen. Will one get a record lock message, or will it wait untill the other one has finished, or could two people end up with the same number under these circumstances?
I am of course open to suggestions on the best way of approaching this, but please bare in mind that I am only using Web Matrix.
Thanks
Mark
View 1 Replies
View Related
Nov 21, 2001
Hi,
I am new to sql server, in oracle one can get a unique number as:
create sequence my_number
increment by 1 start with 1;
select my_number.nextval from dual;
Is there any similiar mechanism in sql server? if so, how to do it?
THanks in advance,
bb
View 3 Replies
View Related
Jul 23, 2005
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
View 3 Replies
View Related
Jan 23, 2008
Hi to alll of you, i'm working in a project to save cars information, when the user who adds on the new record enter all the data, this will need to be printed with a particulary number, which needs to be unique, (lets take a passport number as an example) this generated number will takes some info from the filling fields, for example:
Cars Brand: BMW (catalog number: 120)
Cars Model: 325 (catalog number: 30)
Year: 2008 (catalog number: 18)
So the unique certification number will takes from the catalog numbers * 20 / 5 for example for cars Brand
for Cars Modelo: catalog number *3 + 15
CERTIFICATE NUMBER: 480-105
I'm not sure if this is possible.
I will realy thanks for your help in this issue.
Best Regards
Gian
View 2 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
Dec 11, 2007
I need to generate a random 10 digit alphanumeric string that is also unique within a table. My application will be calling a stored procedure to insert this number into the table. This number will be associated with a id from another table. Is it better to generate the random number within sql (and perform the lookup at the same time), then just pass the number back to the calling application ?
If the calling application generates the number, it will also need to make a call to check if its unique. So im thinking it would be best to simply have sql generate this random number, check the number against the table and then insert the new record.
thoughts ?
View 5 Replies
View Related
Dec 1, 2015
Lets say we have a table (tblProducts)
ID Item RandomNumber
-------------------------------------------
1 JEANS 1234567
2 SHIRT 72813550
3 HOOD Null
4 TROUSER 72191839
5 BLAZER 0
I want to perform a query so that SQL should look for RandomNumber Values and set a Unique Random Number Where RandomNumber Value is Null or 0.So I have got a solution as one of the MSDN Member shared the below query
select id,item,RandomNumber=Case when RandomNumber=0 then (select floor(rand()*100000000-1))
when RandomNumber is null then (select floor(rand()*100000000-1))
else RandomNumber end from tblProducts
So, can you all confirm me, that performing this query ensures that if a Value is assigned to one of the Item in RandomNumber Column, that value will not be assignend to any other Item in RandoNumberColumn.
View 15 Replies
View Related
Nov 1, 2007
My team at work has spent the past week troubleshooting performance issues experienced by users of our asp.net 2.x web application. We've got a probe running on one of the web servers that has identified a frequently occuring error that no one has seen before and I can't find anywhere online. MSSQL error "system.data.oledb.oledbcommand.executenonquery(Maximum number of unique SQL exceeded) Has anyone here ever seen this error before?The web server, application, SQL servers and databases all seem to be configured properly, but users are experiencing latency and this frequently occurring error is a mystery to us.
View 3 Replies
View Related
Oct 14, 2004
What I need to do is re-populate a unique number into multiple fields,
Let me explain, An employee can appear in the first table only once but can be in the second table multiple times with multiple employee numbers .There is a field called TFN that is unique and we are using it to create a unique id called KRid so what I have done is created 2 tables namely TEST_TBL and TEST2_TBL . In TEST_TBL I am populating a KRid with a unique no being produced by the TFN field only once i.e 12345 being the resulting unique id number. If an employee has 2 employee numbers i.e empno 1 and empno 1000,only employee no 1 will have the unique KRid created but nothing for 1000 because the record already exists , so what has me stumped is that the TFN for employee empno 1 and the TFN for empno 1000 are the same. How do I get the KRid (12345 from empno 1) to populate empno 1000 in TEST2_TBL , The second table has all records in so I can group the second table by TFN id but how do I populate employee 1000 in the second table with the KRid 12345.
Please help!!!!! Below are how the tables are set up and an example of the result.
TABLE 1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST_TBL]
GO
CREATE TABLE [dbo].[TEST_TBL] (
[Empl_Num] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[First_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Mid_Name] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Hours_Day] [numeric](18, 2) NULL ,
[Hours_Wk] [numeric](18, 2) NULL ,
[KR_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[Date_Term] [datetime] NULL ,
[Empl_Type] [int] NULL ,
[Cost_Centre] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Empl_Status] [int] NULL
) ON [PRIMARY]
GO
TABLE 2
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TEST2_TBL]
GO
CREATE TABLE [dbo].[TEST2_TBL] (
[EmpNumber] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[TFN] [char] (32) COLLATE Latin1_General_CI_AS NULL ,
[KR_ID] [char] (10) COLLATE Latin1_General_CI_AS NULL ,
[EmpStatus] [int] NULL ,
[EmpType] [int] NULL ,
[CommonName] [char] (32) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
GO
Query goes as follows for table 1:
SELECT NPE000.EmpNumber, NPET00.RecordStatus, NPE000.KR_ID, NPE000.Surname, NPE000.FirstName, NPE000.SecondName, NPE000.Class, NPE000.DateEmployed, NPE000.DateOfBirth, NPE000.HoursPerDay, NPE000.HoursPerWeek, NPE000.PassportNo, NPE000.AwardCode, NPE000.EmailPayslipTo, NPE000.Location, NPE000.Grade, NPE000.DateTerminated, NPE000.EmploymentType, NPE000.DistCode, NPE000.EmpStatus, NPET00.TaxRefNo FROM NPE000 NPE000, NPET00 NPET00 WHERE NPET00.RecordStatus = 0 and NPET00.TaxRefNo <> ' 111111111' and NPET00.TaxRefNo <> ' 000000000' AND LENGTH(NPET00.TaxRefNo) >= 9 AND LENGTH(NPE000.KR_ID) >= 0 AND NPE000.EmpNumber = NPET00.EmpNumber
Query goes as follows for table 2:
SELECT NPE000.EmpNumber, NPE000.FirstName, NPE000.Surname, NPE000.Class, NPE000.Location, NPE000.EmploymentType, NPE000.EmpStatus, NPET00.TaxRefNo, NPE000.Paypoint, NPE000.KR_ID, FROM NPE000, NPET00 WHERE Recordstatus = 0 and (EmploymentType = 1 AND EmpStatus = 1 AND NPE000.EmpNumber = NPET00.EmpNumber
From this you can see that in table 1 it will only create 1 KR_ID for only one employee number but in table 2 I am bringing through all employee records. In table 2 I can group by NPET00.TaxRefNo which will bring all NPET00.TaxRefNo's togeather. From that I would like to populate the other employee numbers with the unique KR_ID.
Example:Table 1
000001,Jackson,James,Sam,7.6,38,12345,475431212
Example:Table 2
000001,Jackson,James,Sam,7.6,38,12345,475431212
001000,Jackson,James,Sam,7.6,38,,475431212
I hope this helps
Thanks in advance
View 10 Replies
View Related
Aug 22, 2007
Hi
I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.
How do I generate 13 digit UNIQUE random in sql procedure?
Advance thanks
View 20 Replies
View Related
Nov 15, 2015
Lets say I have a table - tblProducts
View 4 Replies
View Related
Aug 27, 2007
can someone pls show me a way to get an unique sequence at below senario:
PC1 & PC2 using their own local client progam to access to Database Server at SERVER1.
In the SERVER1, there is a table SEQUENCE in a database DATABASE1.
And the table's structure of SEQUENCE are SeqType & SeqNo.
Here is the sample data:
SeqType SeqNo
Invoice 100
DeliveryOrder 200
Now, how to prevent PC1 & PC2 to get a same Invoice No. if they request the Invoice No. at the same time?
Is it possible to lock the record Invoice when i perform a SELECT statement, then i update the Invoice to 101, lastly release the lock for Invoice?
pls advise. thanks.
View 1 Replies
View Related
May 19, 2015
How to Generate the Unique 8 Digit Number in SQL SERVER.And also am trying with below methods which is not working as expected.
Methode-1:
SELECT CONVERT(VARCHAR(5),GETDATE(),112) +CONVERT(VARCHAR,DATEPART(MS,GETDATE())) AS '8DigitUniqNum'
Methode-2:
DECLARE @UniqueID uniqueidentifier
SET @UniqueID = NEWID()
SELECT LEFT(@UniqueID,8) AS '8DigitUniqNum'
View 9 Replies
View Related
Sep 23, 2006
I have a situation where for a given customer, their invoices need to be sequentially numbered, without gaps.
Customer A Invoice 1,2,3,4 ...
A-1
A-2
A-3
A-4
Customer B:
B-1
B-2
etc.
The issue is 2 people creating an invoice for the same customer at the same time. Currently I don't assign the Invoice number until the user hits 'Save'. At that time I query for max(invoiceno) against the customer key and simply add 1. it's the last operation prior to saving against the backend.
If 2 users hit Save at the exact time, I'm getting the same (duplicated) Invoice Number.
What suggestions/techniques do you have to resolve this? Would "locking" of the customer record and storing the last highest invoice there play a part?
Thanks,
Peter
View 5 Replies
View Related
Aug 6, 2014
I'm trying to do the following and haven't been able to figure it out.
Say there's a table with these records:
Col1 Col2 Col3
a b c
a b c
a b d
e f g
e f g
I want to generate a number that represents the groups of columns like this:
Col1 Col2 Col3 MyNumber
a b c 1
a b c 1
a b d 2
e f g 3
e f g 3
So that each grouping gets its own identifier. I've tried this:
SELECT Col1, Col2, Col3
row_number() OVER (PARTITION BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3) AS MyNumber
FROM MyTable
But I get this:
Col1 Col2 Col3 MyNumber
a b c 1
a b c 2
a b d 1
e f g 1
e f g 2
View 9 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
Nov 25, 2013
create table #temp_Alpha_num (
[uniquenum] [int] Not NULL,
[Somenum] [int] Not NULL,
)
Insert into #temp_Alpha_num(uniquenum,Somenum)
values
(1,121)
[Code] ....
For the somenum column I need to add alphabets to make them unique. for example
121a,121b,121c....121z,121aa,121ab,101a and so on...
View 4 Replies
View Related
Apr 30, 2015
Below is my sample data. I can't figure out how to select Unique phonenumber contacts for the same Ranked values from the set.
Basically the table is a mix of contactIDs. Some of them have duplicate phone numbers and through a separate mechanism we have ranked them.
It's easier then to pull out max(ranked) CLI_Numbers and their counterpart contactID(s). But I am also getting 2 or more records where the rank happens to be the same. I don't want that. Any one of the contactID will do for me.
The table has also same cliNumbers with different rank values, which are then correctly being picked up in the query below.
Note: ContactId is a unique value for each person in the table.
RecordID is simply RowID.
( I have attempted to populate a sample data suited for this forum - not sure how it comes out on the browser)
if object_id('tempdb..#MyData') is not null
drop table #MyData
create table #MyData
(
RecordID int,
contactID int,
forename varchar(25),
surname varchar(25),
[Code] ....
This is my query attempt
With RankedmobileDuplicateSet
as(
select cliNumber, max(ranked_value) as ranked_max_value
from #temp_UK_mobiledata
group by cliNumber)
[Code] .....
View 2 Replies
View Related
Jan 11, 2008
I have a database design question. There're lots of ways to rome they say, and I want to hear what you think of this way.
Government supplies wheel chairs (and thinks alike) to people who need them. They stay in possesion of the (local) government and are distrubuted by a company X.
So we have Tools (Wheelchairs) and Users (of wheel chairs). The life-cycle of a wheel chair is that more than one user while use it over time.
I want to keep track of which users used a instance of a wheelchair.
No there's a developer who likes to put this in one table. (the chair and it's user) in a way like this
UID, WheelChairId, UserId, OwnerId, SerialNumber, BeginDateTime, EndDateTime, SomeOtherColumns
The UID is unique, the WheelChairId is a GUID which is Unique per wheelchair, but can have mutliple records in the table with no overlap.
If one of the values of the columns is changed a new record is made with the same wheelchair and a new begin date (the closed record gets an Enddate). So history is made automaticaly. By using the right query's I can see what users uses the chair in what period of time. But also changed ownerships and other changes in Someother columns overtime.
Is this a good or a common practice? Why use it, or stay away from it?
Henri
~~~~
There's no place like 127.0.0.1
View 1 Replies
View Related
Jan 22, 2008
Hi there!
I'm working on an application designed like this:
There's a table "DailyTransations" (DT) containing daily transactions...
Then there's an archive table "TransationsArchive" (TA) with the exact same structure.
When a record is inserted in DT, it is also in TA (via a trigger) and the reporting is done against TA.
Now for performance issues, we delete from DT the records older than 2 days since they are not needed for processing.
First, what do you think of that implementation?
We thought about using partitions based on the transaction date and completely eliminate TA, but it seems once a record is assigned to a partition, it is not moved automatically...
What is the common solution for this need?
Thanks
Frantz
View 4 Replies
View Related
Nov 15, 2007
Hi all,
this is more of a design issue for a History table.
Suppose if i have a transaction table and then based on the transactions i want to keep a history of those do i need to define Primary Key and Foreign Key for history table.
Regards,
General Problem
View 1 Replies
View Related
Feb 8, 2007
I am running a website of crossword puzzle and Sudoku games. The website is designed to be:
There are 20-30 games onlines each day.
Every registered user could play and submit the game to win scores.
For each game, every registered user could get the score for ONLY one time. i.e., No score will be calculated if the user had finished the game before.
To avoid wasting time on a game finished before, user will be notified with hint message in the page when enter a already finished game.
The current solution is:
3 tables are designed for the functions mentioned above.
Table A: UserTable --storing usering information, userid
Table B: GameList --storing all the game information.
Related fields:
GameID primary key
FinshiedTimes recording how many times the game has been finished
Table C: FinishHistory --storing who and when finished the game
Related fields:
GameID ID of the game
UserID ID of the user
FinishedDate the time when the game was finshied
PS: Fields listed above are only related ones, not the complete structure.
Each time when user enters the game, the program will read Table B(GameList), listing all the available game and the times games have been finished. User could then choose a desired game to play.
When user clicks the link and enter a page showing the detail content of the game, the program will read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page.
When user finishes the game and submit, the program will again read Table C(FinishHistory) to check whether user has finished this game before. If yes, hint message will be shown in the page. If no, user will get the score.
Existing Problems:
With the increase of game and users, the capacity of Table C(FinishHistory) grows rapidly. And each time when a game is loaded, the Table C will be loaded to check, and when a game is submitted, the Table C will be loaded to check again. So it is only a time question to find out Table C to become a bottleneck.
Does any one here have any good suggestions to change / re-invent a new structure or design to avoid this bottleneck?
View 5 Replies
View Related
Jun 28, 2014
I have a table history of Employee data.
id | EmpNo | EmpName | MobileNo | Email | EmpSSS | UpdateDate | UpdateUser
I have to make a stored procedure that will show the history and changes made to a given EmpNo, with the UpdateDate, UpdateUser and indicate which field is modified. Ex. Employee Mobile number was changed from '134151235' to '23523657'.
Result must be:
EmpNo | UpdateDate | UpdateUser | Field changed | Change from | change to
View 4 Replies
View Related
Mar 25, 2015
how to get products on which SalesPerson changed. Here is table with data.
View 9 Replies
View Related
Nov 3, 2015
I have order history table that tracks who worked on a order
ID. ColA. ColB. ColC
1. Process 1234
2. Work. 7666
3. Return. 6789
4. Work. Null Role1
5. Return. 6538
I want a query to return recent colB or ColC where colA or colB are not null. In this case row 4
I have order history table that tracks who worked on a order
Another example
ID. ColA. ColB. ColC
1. Work. 1234
2. Process. 7666
3. Return. 6789
I want a query to return most recent colB or ColC where colB or ColC are not null. In this case row 2
View 1 Replies
View Related
May 21, 2008
I work for a college and have recently been working on our enquiries and applications process (getting it onto our big enrollment db rather than standalone). It has all been going well but now they have asked for a report of students where it has taken more than x days or weeks to progress to the next stage code.
For stage codes they basically follow something like application, guidance interview, programme area interview, conditional/ unconditional offer... Although they could skip a stage code.
Any ideas how to do this bearing in mind I can't guarantee them to go to every stage so really I need to look in the history table and find records more than x days apart where one is the next progression date of the other. Hope I explained that ok.
history table is similar to:
StudentID CourseCode StageDate InputDate Stage
View 1 Replies
View Related
May 6, 2008
I've recently finished an application for a small company with perhaps two hundred employees. Each employee was set up in a Users table in the database, against which application logins were processed.
For just about every other table in the database, other than pure lookup tables, we created columns to indicate the user who created the entry, and the user who last modified the entry. This was done using FK references back to the Users table. Each table contains two references back to the Users table, and there are over 150 tables now that follow this scheme. At first I was not concerned, other than the fact that it makes a visual picture of the data model look very confusing (almost every table has a pair of links back to the Users table), until I encountered an issue where I could no longer delete from the Users table. Upon surpassing 253 FK references to Users, I can no longer delete users, as the Query Optimizer can't complete the query.
Now, all of that so far is really not a big deal. Deleting users was never my intent anyway. The only real question I have is whether this is the standard way of maintaining history for table records. Have others used this method? Is there a better way?
View 1 Replies
View Related
Mar 25, 2008
Hi guys,
acutally i have setup a Disaster Recovery plan for my database.. i m taking a full back once in a week,. i dont' know when i right click on the job and trying to check the view history option to check when was last backup has been taken, it's showing nothing..but when i check on acutall location the backup was taken there.. i don't know y it's not writing any info in view history table.. or is it clear once in a week and i cann't see that...
Can any one tell's me about this...
View 3 Replies
View Related
Oct 22, 2015
i have a table and i would like to know what are the queries that were executed referring to that table in the past 5 days. is it possible?
View 4 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
May 7, 2014
I have a table of users including: UserName, Password (comuted col), FirstName, LastName, Address and other details....
I have to keep 10 Recent passwords , so I created another table "ut_Password " (Table2)
This table contains the following columns : Username, Password , and Password_Date.
I searched a lot but could not find something similar in my opinion need SP for it.
- 10 row Max for Password History in table 2
- when user change password it's need to be uniqe and it should not appear last 10 passwords
- Each user can have a maximum of 10 lines containing history password table
- Most old password deleted and replaced with a new password will enter the correct date (FIFO method first in first out).
View 9 Replies
View Related
