Auto Increment

Question

Post reply

Auto Increment

Eswin

SSCrazy Eights

Points: 8320
More actions
September 3, 2009 at 2:48 am

#209580

Hi Experts,
Is it possible create an identity column such as the attached.
i.e if the 1st row is one then second should be 2
third row 3 (1+2)
fourth row 6 (1+2+3)
second row 12 (1+2+3+6) and so on.
Tanx 😀

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

No. An identity can be set to start at any number and increment by any fixed interval.

I do have to ask, why do you want values like this?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 2

I'm curious too, so please answer Gail's question.

You can get the number you want by means of a subquery which sums all the previous IDs, but it will break down if you are doing multiple line inserts (see example below).

If there is a true need for this, I would precalculate those values and store them in a table along with a

conventional identity column (1,2,3,4,5,6.....). Then you can use a normal identity column in your primary table and simply use it to join to the lookup table to get the value you need.

declare @table table (ID int)

declare @loop int

insert into @table

select 1 union all

select 2

select * from @table

insert into @table

select (select SUM(ID) from @table)

select * from @table

insert into @table

select (select SUM(ID) from @table) union all

select (select SUM(ID) from @table)

select * from @table

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Eswin SSCrazy Eights Points: 8320 More actions · Answer 3

Im not able to attach the .xls that i made.

its for calculating the duty hours spend by a person.

The 1st two columns contain the start time and end time of duty.

The third column one contain total time for the day.

The fourth contains total for five days til 60 hrs.

ie in fourth column second row will have the sum of 1st two row values of the third column and

third row of fourth column contains sum of 1st three values of the third column so on till 60 hrs completed.

Date Start time End timeTotal time(14hrs Max)Total Time

01/01/09 06:30:00 AM 06:30:00 PM 12 12

01/02/09 06:30:00 AM 05:30:00 PM11 23

01/03/09 06:30:00 AM 06:30:00 PM12 35

01/04/09 06:30:00 AM 05:30:00 PM11 46

01/05/09 06:30:00 AM 05:30:00 PM11 57

01/06/09 0

01/07/09 0

01/08/09 06:30:00 AM 06:30:00 PM 12 12

01/09/09 06:30:00 AM 05:30:00 PM 11 23

01/10/09 06:30:00 AM 05:30:00 PM 11 34

01/11/09 06:30:00 AM 06:30:00 PM 12 46

Tanx 😀

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 4

Eswin (9/4/2009)
Im not able to attach the .xls that i made.
its for calculating the duty hours spend by a person.
The 1st two columns contain the start time and end time of duty.
The third column one contain total time for the day.
The fourth contains total for five days til 60 hrs.
ie in fourth column second row will have the sum of 1st two row values of the third column and
third row of fourth column contains sum of 1st three values of the third column so on till 60 hrs completed.
Date Start time End timeTotal time(14hrs Max)Total Time
01/01/09 06:30:00 AM 06:30:00 PM 12 12
01/02/09 06:30:00 AM 05:30:00 PM11 23
01/03/09 06:30:00 AM 06:30:00 PM12 35
01/04/09 06:30:00 AM 05:30:00 PM11 46
01/05/09 06:30:00 AM 05:30:00 PM11 57
01/06/09 0
01/07/09 0
01/08/09 06:30:00 AM 06:30:00 PM 12 12
01/09/09 06:30:00 AM 05:30:00 PM 11 23
01/10/09 06:30:00 AM 05:30:00 PM 11 34
01/11/09 06:30:00 AM 06:30:00 PM 12 46

Post the CREATE TABLE statement for a test table and some sample data in the readily consumable format as identified in the article in the first link in my signature below, and one or more of us will show you how to do such a "grouped running total" (which is quite a bit different than what you originally asked).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eswin SSCrazy Eights Points: 8320 More actions · Answer 5

Thanks guys.

I want my result to randomly generate the third and the fourth column.

This is the table that i have

create table test (Name varchar(10), start_time datetime, end_time datetime)

insert into test values ('a','01/01/09 6:30am','01/01/09 6:30pm')

insert into test values ('a','01/02/09 6:30am','01/02/09 5:30pm')

insert into test values ('a','01/03/09 6:30am','01/03/09 6:30pm')

insert into test values ('a','01/04/09 6:30am','01/04/09 5:30pm')

insert into test values ('a','01/05/09 6:30am','01/05/09 5:30pm')

insert into test values ('a','01/06/09','01/06/09')

insert into test values ('a','01/07/09','01/07/09')

insert into test values ('a','01/08/09 6:30am','01/08/09 6:30pm')

insert into test values ('a','01/09/09 6:30am','01/09/09 5:30pm')

insert into test values ('a','01/10/09 6:30am','01/10/09 6:30pm')

select * from test

And the result to be generated is something like this

Date Start time End timeTotal time(14hrs Max)Total Time

01/01/09 06:30:00 AM 06:30:00 PM 12 12

01/02/09 06:30:00 AM 05:30:00 PM11 23

01/03/09 06:30:00 AM 06:30:00 PM12 35

01/04/09 06:30:00 AM 05:30:00 PM11 46

01/05/09 06:30:00 AM 05:30:00 PM11 57

01/06/09 0

01/07/09 0

01/08/09 06:30:00 AM 06:30:00 PM 12 12

01/09/09 06:30:00 AM 05:30:00 PM 11 23

01/10/09 06:30:00 AM 05:30:00 PM 11 34

01/11/09 06:30:00 AM 06:30:00 PM 12 46

Please help.

Tanx 😀

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

I guess I don't understand a couple of things... when you say you want to "randomly generate the 3rd and 4th column", do you mean just for the test data or for production (which makes no sense to me at all). If it's just for the test data, don't do that... provide values for the 3rd column in the test data (like you did in the other posts) so you don't confuse folks (heh... especially me).

Also, to be sure, what makes the 4th column reset to zero on the 6th and 7th?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

WayneS SSC Guru Points: 95460 More actions · Answer 7

Jeff Moden (9/6/2009)
I guess I don't understand a couple of things... when you say you want to "randomly generate the 3rd and 4th column", do you mean just for the test data or for production (which makes no sense to me at all). If it's just for the test data, don't do that... provide values for the 3rd column in the test data (like you did in the other posts) so you don't confuse folks (heh... especially me).
Also, to be sure, what makes the 4th column reset to zero on the 6th and 7th?

Hey Jeff,

Earlier, Eswin had stated

The fourth contains total for five days til 60 hrs.

Now, this would explain why it reset to 0 on the 6th, but it doesn't explain the 7th. Eswin, can you elaborate on this business rule a bit more?

Thanks,

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 8

Yeah... I know... which 5 days? What happens if you reach 60 hours before 5 days is complete? I know he didn't say it, but I've got the feeling that this is for a work week yet 01/01/09 was a Thursday. Maybe not. He also didn't include the hours for total time in the test data. People want help but won't take the time to help me (us) help them. I just don't have the time to ask 20 questions to figure out what the op actually wants and I sure don't have the time to write 3 or 4 pieces of code that don't meet the requirements due to guessing.

Speaking of guessing, guess I'll just move on.:Whistling:

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eswin SSCrazy Eights Points: 8320 More actions · Answer 9

Sorry guys.

Jeff Moden (9/6/2009)
I guess I don't understand a couple of things... when you say you want to "randomly generate the 3rd and 4th column", do you mean just for the test data or for production (which makes no sense to me at all). If it's just for the test data, don't do that... provide values for the 3rd column in the test data (like you did in the other posts) so you don't confuse folks (heh... especially me).
Also, to be sure, what makes the 4th column reset to zero on the 6th and 7th?

🙂 i know which post you are talking about.

Sorry it was not 3rd and 4th column.

I wanted to randomly generate 4th and 5th columns in the result of select query on table test.

Now, this would explain why it reset to 0 on the 6th, but it doesn't explain the 7th. Eswin, can you elaborate on this business rule a bit more?
Thanks,

0 on 6th and 7th are considered as off days.

from 8th onwards we again start calculating.

Jeff Moden (9/6/2009)
Yeah... I know... which 5 days? What happens if you reach 60 hours before 5 days is complete? I know he didn't say it, but I've got the feeling that this is for a work week yet 01/01/09 was a Thursday.

had just created a test table which is replica of the original table, but the values entered are not the same .

Sorry, I didn't not cross verify the day of the mentioned dates while inserting the test table.

He cannott spend more than 14hrs a day and cant spend more than 60 hrs a week (5 days)

Tanx 😀