How to calculate the sum of a value as a single digit value

Posted by Kirthiga under Sql Server on 6/26/2013 | Points: 10 | Views : 2186 | Status : [Member] | Replies : 2

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

In my Stored Procedure my getting single value as Output in a table.

For Example

Data
====
55

(ie)In my table Data Im getting 55 as output.

My requirement is to get the sum of my output in single digit.

55--> 5+5=10-->1+0-->1

I want 1 as my output.How to achieve this in a query.

Reply | Reply with Attachment

Alert Moderator

Responses

Posted by: Pandians on: 6/26/2013 [Member] [MVP] Silver | Points: 25

0	Hey Kirthiga Check this out... and Let me know Its helpful/not ! Create a Function : CREATE FUNCTION Dbo.ReturnAsSingleDigit ( @Number BIGINT ) RETURNS INT AS BEGIN DECLARE @Len INT, @Start INT, @Result BIGINT StartHere: SELECT @Start=1,@Len = LEN(@Number),@Result=0 ;WITH CTE AS ( SELECT 1 [Start] UNION ALL SELECT [Start] + 1 [Start] FROM CTE WHERE [Start] < LEN(@Number) ), [SUM] AS ( SELECT SUM(CAST(SUBSTRING(CAST(@Number AS VARCHAR),[Start],1) AS INT)) [Data] FROM CTE ) SELECT @Number=[Data] FROM [SUM] IF LEN(@Number) >1 GOTO StartHere ELSE SELECT @Result = @Number RETURN(@Result) END GO Use the Function: SELECT Dbo.ReturnAsSingleDigit(55) GO Cheers www.SQLServerbuddy.blogspot.com iLink Multitech Solutions Kirthiga, if this helps please login to Mark As Answer. \| Alert Moderator

Hey Kirthiga

Check this out... and Let me know Its helpful/not !

Create a Function :

CREATE FUNCTION Dbo.ReturnAsSingleDigit

(

@Number BIGINT

) RETURNS INT

AS

BEGIN

DECLARE @Len INT, @Start INT, @Result BIGINT

StartHere:

SELECT @Start=1,@Len = LEN(@Number),@Result=0

;WITH CTE

AS

(

SELECT 1 [Start]

UNION ALL

SELECT [Start] + 1 [Start] FROM CTE WHERE [Start] < LEN(@Number)

), [SUM]

AS

(

SELECT SUM(CAST(SUBSTRING(CAST(@Number AS VARCHAR),[Start],1) AS INT)) [Data] FROM CTE

)

SELECT @Number=[Data] FROM [SUM]

IF LEN(@Number) >1 

	GOTO StartHere

ELSE

	SELECT @Result = @Number 



RETURN(@Result)

END

GO

Use the Function:

SELECT Dbo.ReturnAsSingleDigit(55)

GO

Cheers
www.SQLServerbuddy.blogspot.com
iLink Multitech Solutions

Kirthiga, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jasminej on: 6/28/2013 [Member] Starter | Points: 25

0	Greate! Thanks Kirthiga, if this helps please login to Mark As Answer. \| Alert Moderator

Latest Posts