List of RTA numbers based on the lucky number you
Using this procedure you can get the list of 4 digit number and the sum of those 4 digits is equal to your given input number. its helpful when you choose your RTA (Regional Transportation Authority) Vehicle number.
Input For this procedure: Single Digit Number (May be your luck number)
Output From this procedure: it gives the list of 4 digits Number and the sum of the 4 digits is equal to your lucky number
Example:
Input Number: 5
The List of output values:
1 0005
2 0014
3 0023
4 0032
5 0041
6 0050
7 0059
8 0068
9 0077
10 0086
11 0095
12 0104
13 0113
14 0122
15 0131
Like this it gives the output values
/****** Object: StoredProcedure [dbo].[usp_getRTANumber] Script Date: 03/03/2007 15:28:55 ******/
if exists (select * from sys.objects where object_id = object_id(n'[dbo].[usp_getrtanumber]') and type in (n'p', n'pc'))
drop procedure [dbo].[usp_getrtanumber]
GO
/*
Purpose : You can choose the RTA Number based on the lucky number as input
Created Date : 03/03/2007
Created by : Satyanarayana Bommidi
Parameters : Yes
Input : @LuckyNumber
Output : List of RTA numbers based on the lucky number you choose input
Modified Date :
Modified by :
Comments :
execute : exec usp_getRTANumber 5
*/
create procedure usp_getRTANumber(@LuckyNumber int = 0)
as
begin
declare @num tinyint
declare @nm1 tinyint
declare @nm2 tinyint
declare @nm3 tinyint
declare @nm4 tinyint
declare @cnt tinyint
declare @sum int
declare @str varchar(10)
select @num = @LuckyNumber, @nm1 = 0, @nm2 = 0, @nm3 = 0, @nm4 = 0, @str = '', @cnt = 1, @sum = 0
create table #outtable(SLNo int identity(1,1), RTANum varchar(10))
while @nm1 <= 9
begin
set @nm2 = 0
while @nm2 <= 9
begin
set @nm3 = 0
while @nm3 <= 9
begin
set @nm4 = 0
while @nm4 <= 9
begin
if @nm1+@nm2+@nm3+@nm4 = @num
begin
insert into #outtable(RTANum)
select convert(varchar(1), @nm1)+convert(varchar(1), @nm2)+convert(varchar(1), @nm3)+convert(varchar(1), @nm4)
end
else if @nm1+@nm2+@nm3+@nm4 > @num
begin
set @str = convert(varchar, @nm1+@nm2+@nm3+@nm4)
select @sum = 0, @cnt = 1
while @cnt <= len(@str)
begin
set @sum = @sum + convert(int, substring(@str, @cnt, 1))
if @sum = @num
begin
insert into #outtable(RTANum)
select convert(varchar(1), @nm1)+convert(varchar(1), @nm2)+convert(varchar(1), @nm3)+convert(varchar(1), @nm4)
end
set @cnt = @cnt + 1
end
end
set @nm4 = @nm4 + 1
end
set @nm3 = @nm3 + 1
end
set @nm2 = @nm2 + 1
end
set @nm1 = @nm1 + 1
end
select * from #outtable
drop table #outtable
end
GO
