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 : 935 | Status : [Member] | Replies : 3
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.




Responses

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

Up
0
Down
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

Up
0
Down
Greate!

Thanks

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

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

Up
0
Down
Great its working fine


Thanks,
Bagesh Kumar Singh
Mark as answer if you have been satisfied

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

Login to post response