Increase number by 1

Hello all,
I have, what i think, is a unique problem that i'm hoping some of you can help me on.

I need to create a record number that is incremented by 1 whenever someone adds a new record to the database. For example, records numbering 1,2,3 are in the database. When the users adds a new record, SQL takes the last recordno, 3 in this case, and adds 1 to it thus producing 4.

Also, i need to have the ability to replace deleted record numbers with new ones. Using the example above, say a user deletes record number 2. Whenever someone adds a new record, sql would see the missing number and assign the new record that number.

I hope i'm making sense here. Does anyone have any ideas about this? Any articles on the web that someone could point me to?

Thanks.
Richard M.hi richard,
i guess you need to do this by coding urself. u can use the feature in Sql server to increment the number by one but i don't think its possible to replace the deleted number.

so the better solution will be to add the incrementing number programatically. first declare int data type in sql server and assign 1 for the first record. for new records, check whether any number is missing and try to add new into that.

for eg, if you have 10 records, then no of 10th record should be 10 else some record is deleted. so u can use loop to check which number is missing.

i hope u can do the coding.

Incorrect Date Conversion - Help Please

Dear Group

Some but not all of the dates are incorrect converted (incremented by 1
day) with the following SQL statement.

SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
ORDER BY ReturnedByDate ASC

'ReturnedByDate' is of type smalldatetime in the table. I need the
result set to return me the ReturnByDate once as actual date values
(for the value of a dropdown) and once as text values (for the text of
the same dropdown). It all works fine, it's just that some actual date
values are incremented by one day. The third column in the result set
sample represents the actual database value as a reference to give you
an idea of the original value.

ReturnedByDate LabelReturnedByDate ReturnedByDate
-------- ------ ------
2004-11-05 00:00:00.000 04.11.2004 2004-11-04 15:22:00
2004-11-11 00:00:00.000 11.11.2004 2004-11-11 00:00:00
2004-11-12 00:00:00.000 12.11.2004 2004-11-12 00:29:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 20:23:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 20:24:00
2004-11-22 00:00:00.000 21.11.2004 2004-11-21 21:10:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 14:50:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:02:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:03:00
2004-11-23 00:00:00.000 22.11.2004 2004-11-22 15:09:00
2004-12-09 00:00:00.000 09.12.2004 2004-12-09 08:09:00
Any help is very appreciated!

Thanks for your time & efforts!

MartinDECLARE @.ReturnedByDate smalldatetime
SET @.ReturnedByDate = '2004-11-04 15:22:00'

SELECT DISTINCT cast(@.ReturnedByDate AS real) AS Y,
cast(@.ReturnedByDate AS int) AS X,
CONVERT(datetime,cast(@.ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),@.ReturnedByDate,104) AS
LabelReturnedByDate, @.ReturnedByDate
-- FROM i2b_keytransactionhistory ORDER BY ReturnedByDate ASC

select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)|||DECLARE @.ReturnedByDate smalldatetime
SET @.ReturnedByDate = '2004-11-04 15:22:00'

SELECT DISTINCT cast(@.ReturnedByDate AS real) AS Y,
cast(@.ReturnedByDate AS int) AS X,
CONVERT(datetime,cast(@.ReturnedByDate AS int)) AS
ReturnedByDate, CONVERT(varchar(10),@.ReturnedByDate,104) AS
LabelReturnedByDate, @.ReturnedByDate
-- FROM i2b_keytransactionhistory ORDER BY ReturnedByDate ASC

select CAST(38293.641 as datetime)
select CAST(38293.641 as smalldatetime)
select CAST(38294 as datetime)

-- Happy Holidays!
-- Kurt|||(theintrepidfox@.hotmail.com) writes:
> Some but not all of the dates are incorrect converted (incremented by 1
> day) with the following SQL statement.
> SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
> ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
> LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
> ORDER BY ReturnedByDate ASC

Converting datetime to int is not a very bright idea. For some reason,
that comes with rounding, so hours in the afternoon gets round up to
the next day.

Stick to convering your datetime to character.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On 28 Dec 2004 18:26:55 -0800, theintrepidfox@.hotmail.com wrote:

>Some but not all of the dates are incorrect converted (incremented by 1
>day) with the following SQL statement.
>SELECT DISTINCT CONVERT(datetime,cast(ReturnedByDate AS int)) AS
>ReturnedByDate, CONVERT(varchar(10),ReturnedByDate,104) AS
>LabelReturnedByDate, ReturnedByDate FROM i2b_keytransactionhistory
>ORDER BY ReturnedByDate ASC

Hi Martin,

What Erland says :-)

But if you somehow REALLY need the number of days since 19000101 in your
frontend, the following should prove more reliable:

SELECT DATEDIFF(day, '19000101', ReturnedByDate)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)