how to generate next #

Question

Post reply

how to generate next #

NEAL-464478

SSC-Addicted

Points: 461
More actions
September 10, 2008 at 8:48 am

#212432

1 3202306000
2 3202307500
3 3202307503
how can i generate example 1's next upto four # such as 3202306001 to 3202306004 next example 2 and 3 since fourth # is example 3 already so need only inbetween #'s

Viewing 7 posts - 1 through 6 (of 6 total)

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

bcronce SSCrazy Points: 2597 More actions · Answer 1

I don't know if you figured it out already or you still need help, but I know I don't quite understand the question.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 2

Very little code to work with here, but have you looked at the ROW_NUMBER function in Books Online? I think that might help you do what you want.

If not, post a bit more code & structure to help us understand the problem better.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

NEAL-464478 SSC-Addicted Points: 461 More actions · Answer 3

1 3202306000 (already in db)

3202306001 (generated #)

3202306002 (generated #)

3202306003 (generated #)

3202306003 (generated #, since requirment upto 4, it stop here and see next # )

2 3202307500 (already in db)

3202307501 (generated #)

3202307502 (generated #)

3202307503 (generated #)

3202307504 (generated #, since requirment upto 4, it stop here and see next # )

3 3202307503 (already in db)

3202307504 (generated #)

3202307505 (generated #)

3202307506 (generated #)

3202307507 (generated #, since requirment upto 4, it stop here and see next # )

how can i generate example 1's next upto four # such as 3202306001 to 3202306004 next example 2 and 3 since fourth # is example 3 already so need only inbetween #'s

Lowell SSC Guru Points: 323518 More actions · Answer 4

ok, I'm making a lot of assumptions, like ther emust be 3 more items added for EVERY item that's in your sample table. if an item overlaps where id_3, you are out of luck

this works;

CREATE TABLE TMP(TMPID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,NEALSID BIGINT)

INSERT INTO TMP(NEALSID)

SELECT 3202306000 UNION

SELECT 3202307500 UNION

SELECT 3202307503

SELECT * INTO #SAVED FROM (

SELECT NEALSID FROM TMP UNION

SELECT NEALSID + 1 FROM TMP UNION

SELECT NEALSID + 2 FROM TMP UNION

SELECT NEALSID + 3 FROM TMP)X

INSERT INTO TMP(NEALSID )

SELECT #SAVED.NEALSID FROM #SAVED

LEFT OUTER JOIN TMP ON #SAVED.NEALSID=TMP.NEALSID

WHERE TMP.TMPID IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

DECLARE@Sample TABLE

(

ID BIGINT

)

INSERT@Sample

SELECT3202306000 UNION ALL

SELECT3202307500 UNION ALL

SELECT3202307503

SELECT DISTINCTs.ID,

s.ID + x.Number AS Yak

FROM@Sample AS s

CROSS JOIN(

SELECT0 AS Number UNION ALL

SELECT1 UNION ALL

SELECT2 UNION ALL

SELECT3 UNION ALL

SELECT4

) AS x

N 56°04'39.16"
E 12°55'05.25"

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

With only a little modification, you could use the code in this article:

http://www.sqlservercentral.com/articles/T-SQL/61461/[/url]

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning