Generate numbers from 0001 to 9000 in column and loop for another column

Question

1.00/5 (3 votes)

See more:

hello i have table with 2 column i want one to generate numbers from 0001 to 9000 at this format number and second one based on first column like every 50 numbers from first column this 1 and second 50 this is 2

Book No Receipt No
1 0001-0050
2 0051-0100
3 0101-0150

like this ofc it will be huge data but i need it

thank u

What I have tried:

still don't have idea how can i do it

Posted 25-Apr-17 2:27am

Learn.net37

Updated 25-Apr-17 2:56am

v4

Add a Solution

Comments

CHill60 25-Apr-17 8:57am

Btw it won't be huge data - it will only be 180 rows - 9000 / 50

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Accepted Answer · 2017-04-25T02:56:00

Solution 1

To generate a sequence see Generating a Sequence in SQL[^]
For the first column [Book No] use an identity column:

SQL

CREATE TABLE Book
(
	[Book No] int identity(1,1),
	[Receipt No] varchar(9)
)

For the second column use the means of generating a sequence, format it (this is poor practice by the way) and insert into the table like this

SQL

;WITH q AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 50
    FROM    q
    WHERE num < 9000 - 49
)
INSERT INTO Book
SELECT  FORMAT(num,'000#') + '-' + FORMAT(num + 49, '000#') -- SQL 2012 or greater 
--SELECT RIGHT('0000'+CAST(num AS VARCHAR(4)),4) + '-' + RIGHT('0000'+CAST(num + 49 AS VARCHAR(4)),4)		-- SQL 2008 or earlier
FROM    q
OPTION (MAXRECURSION 180) -- 900 / 50

Note if you are using a version of SQL earlier than 2012 you will need to swap out the last SELECT for the one in the comment.

[Edited to ensure max number is 9000 not 9049]

[EDIT 2] OP has restated their requirements

SQL

CREATE TABLE Book
(
	[Book No] int,
	[Receipt No] int
)

;WITH q AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 1
    FROM    q
    WHERE num < 9000 
), q2 as
(
	SELECT 1 as num2
	UNION ALL
	SELECT num2 + 1
	FROM q2
	WHERE num2 < 50
)
INSERT INTO Book
SELECT  Q.num, Q2.num2
FROM    q CROSS JOIN q2
OPTION (MAXRECURSION 9000)


SELECT * from Book
order by [Book No], [Receipt No]

[EDIT 3] ... a slightly better description of the requirements has come forth:

SQL

;WITH q AS
(
    SELECT  1 AS num
    UNION ALL
    SELECT  num + 1
    FROM    q
    WHERE num < 9000 
)
SELECT  num, FORMAT(NTILE(9000/50) OVER(ORDER BY num),'000#') AS Receipt
FROM    q 
ORDER BY num
OPTION (MAXRECURSION 9000)

Posted 25-Apr-17 2:56am

CHill60

Updated 25-Apr-17 4:08am

v4

Comments

Learn.net37 25-Apr-17 9:38am

thank u sir but i need to access like 45 and etc
ex: ur code book recp
1 1-50

i want recp book
1 1
1 2
1 3
1 4
1 -
1 -
1 -
1 -
- -
1 50
2 51
and so on

Learn.net37 25-Apr-17 9:43am

i know it's not good but plz i need this , and the insert will depend on recp no so its fine

CHill60 25-Apr-17 9:50am

I've updated my solution. If this still isn't what you need then describe the problem clearly.

Learn.net37 25-Apr-17 10:00am

sir when its reach book 2
bok rec
2 1

the rec go 1 again and not 51

and the format number 1 not 0001 in recp

CHill60 25-Apr-17 10:10am

I've updated my solution again. ... as to the format, I've already shown you how to do that.
A question for you to consider though... what happens when you sell 51 copies of Book 1 ... you won't be able to invoice for it

Learn.net37 25-Apr-17 10:13am

it's like book of receipt every book have 50 receipt so what do i do if i have 33 book with start of 1 until now 1600 receipt and i want to try to bind one with some other records or find what is missing, u tell me if there is good idea beside this?

CHill60 25-Apr-17 10:16am

Now I understand what you have been trying to achieve!
You are correct, by querying this table with a LEFT OUTER JOIN to the table that has the "used" records you can determine what is missing by looking for NULL values returned for the joined table.

CHill60 25-Apr-17 10:20am

I doubt you can afford my daily rate, but in any event I do not share my private email on this forum.

CHill60 25-Apr-17 10:25am

You might want to remove that before any spam bots find it

Learn.net37 25-Apr-17 10:16am

the last edit tell me format in invalid function because maybe i use 2008

CHill60 25-Apr-17 10:19am

Then use the alternative I showed you earlier

RIGHT('0000'+CAST(FORMAT(NTILE(9000/50) OVER(ORDER BY num)AS VARCHAR(4)),4)

Maciej Los 25-Apr-17 17:11pm

5ed!