Help needed - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help needed

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-13 : 09:53:48

Heljeeve writes "Within a range of value,say between 1000000 and 1999999 how many times 00-09,10-19,..90-99 will appear as last two digit and the no. also should be printed.

I need the total count as output

00-09 -10000
10-19 -10000

Input for the procedure is fromno and tono

Fomno and tono will be 7 digit no."

joldham
Wiseass Yak Posting Master

300 Posts

Posted - 2002-05-13 : 12:56:59

I hope this is what you mean. If it isn't, then please reply with sample table definitions and a table of results expected.

Create Table Numbers1
(the_number char(7))
Go

INSERT INTO NUMBERS1 VALUES('1000000')
INSERT INTO NUMBERS1 VALUES('1000002')
INSERT INTO NUMBERS1 VALUES('1000019')
INSERT INTO NUMBERS1 VALUES('1000029')
INSERT INTO NUMBERS1 VALUES('1200029')
INSERT INTO NUMBERS1 VALUES('1200039')
INSERT INTO NUMBERS1 VALUES('1400010')
INSERT INTO NUMBERS1 VALUES('1400029')
INSERT INTO NUMBERS1 VALUES('1999900')
INSERT INTO NUMBERS1 VALUES('1999999')
GO

CREATE PROCEDURE get_the_number
@fromno char(7),
@tono char(7)
AS

Select Case
When (RIGHT(the_number,2)) <= '09' then '00-09 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '19' then '10-19 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '29' then '20-29 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '39' then '30-39 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '49' then '40-49 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '59' then '50-59 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '69' then '60-69 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '79' then '70-79 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '89' then '80-89 - ' + LEFT(the_number,5)
else '90-99 - ' + LEFT(the_number,5)
END group_number, Count(*) Count1
from Numbers1
WHERE LEFT(the_number, 5) BETWEEN LEFT(@fromno,5) and Left(@tono,5)
GROUP BY Case
When (RIGHT(the_number,2)) <= '09' then '00-09 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '19' then '10-19 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '29' then '20-29 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '39' then '30-39 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '49' then '40-49 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '59' then '50-59 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '69' then '60-69 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '79' then '70-79 - ' + LEFT(the_number,5)
When (RIGHT(the_number,2)) <= '89' then '80-89 - ' + LEFT(the_number,5)
else '90-99 - ' + LEFT(the_number,5)
END
GO

exec get_the_number '1000000', '1200000'

Jeremy

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources