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

Rate

Share

Share

Rate