One of my friend is working in SSRS and he got the
requirement to print the total sales amount in the report. To fulfill this
requirement we have created a user-defined function to convert the total amount
in the words.
Creating function using below script
|
ALTER FUNCTION [dbo].[udf_NumberToHindiWords]
(
@InNumericValue NUMERIC(38,2)
)
RETURNS NVARCHAR(2000)
AS
BEGIN
DECLARE @NumericDetails TABLE
(
Number INT
,Word NVARCHAR(20)
)
INSERT INTO @NumericDetails VALUES
(1,'Ek'),(2,'Do'),(3,'Tin'),(4,'Char'),(5,'Paanch'),(6,'Chheh'),(7,'Saat'),(8,'Aath'),(9,'Nau')
,(10,'Dus'),(11,'Gyarah'),(12,'Barah'),(13,'Terah'),(14,'Choudah'),(15,'Pandrah'),(16,'Solah'),(17,'Satrah')
,(18,'Attharah'),(19,'Unnees'),(20,'Bees'),(21,'Ikkees'),(22,'Baies'),(23,'Teies'),(24,'Choubees'),(25,'Pachchees')
,(26,'Chhabees'),(27,'Sattaies'),(28,'Atthaies'),(29,'Untees'),(30,'Tees'),(31,'Iktees'),(32,'Battees'),(33,'Tetees')
,(34,'Choutees'),(35,'Pentees'),(36,'Chhattees'),(37,'Sentees'),(38,'Adatees'),(39,'Unchalees'),(40,'Chalees'),(41,'Iktalees')
,(42,'Bayalees'),(43,'Teralees'),(44,'Chawalees'),(45,'Pentalees'),(46,'Chhayalees'),(47,'Sentalees'),(48,'Adtalees'),(49,'Unchas')
,(50,'Pachaas'),(51,'Ikyawan'),(52,'Bawan'),(53,'Trepan'),(54,'Chauwan'),(55,'Pachpan'),(56,'Chhappan'),(57,'Sattawan')
,(58,'Atthawan'),(59,'Unsath'),(60,'Saath'),(61,'Iksath'),(62,'Basath'),(63,'Tresath'),(64,'Chausath'),(65,'Pensath')
,(66,'Chhiyasath'),(67,'Sarsath'),(68,'Adsath'),(69,'Unhattar'),(70,'Sattar'),(71,'Ikhattar'),(72,'Bahattar'),(73,'Tehattar')
,(74,'Chauhattar'),(75,'Pachhattar'),(76,'Chhiyattar'),(77,'Satattar'),(78,'Athattar'),(79,'Unyasi'),(80,'Assi'),(81,'Ikyasi')
,(82,'Bayasi'),(83,'Terasi'),(84,'Chaurasi'),(85,'Pachasi'),(86,'Chhiyasi'),(87,'Satasi'),(88,'Athasi'),(89,'Nawasi')
,(90,'Nabbe'),(91,'Inkyanwe'),(92,'Bannawe'),(93,'Terannwe'),(94,'Chaurannwe'),(95,'Pachannwe'),(96,'Chhiyannwe'),(97,'Sattannwe')
,(98,'Atthannvwe'),(99,'Ninnanwe')
DECLARE @DigitDetails TABLE
(
DigitPlace INT
,PlaceName VARCHAR(30)
)
INSERT INTO @DigitDetails VALUES
(1,'Sau'),(2,'Hazar'),(4,'Crore'),(6,'Kharab'),(8,'Padma'),(10,'Ald')
,(12,'Jald'),(14,'Parardha'),(16,'Maha Ant'),(18,'Singhar'),(20,'Adant Singhar'),(3,'Lakh')
,(5,'Arab'),(7,'Neel'),(9,'Shankh'),(11,'Ank'),(13,'Madh'),(15,'Ant')
,(17,'Shisht'),(19,'Maha Singhar')
DECLARE @StrNumber VARCHAR(60)
,@LargeNumber
VARCHAR(60)
,@SmallNumber
VARCHAR(10)
,@DecimalNumber
VARCHAR(10)
,@Chunk
VARCHAR(50)
,@ChunkVal
INT
,@StrFinal VARCHAR(1000)
,@StrLength
INT
,@DigitLoop
INT
SET @DecimalNumber = ''
SET @StrFinal = ''
SET @DigitLoop = 2
SET @StrNumber = ABS(@InNumericValue)
SET @StrNumber = SUBSTRING(@StrNumber,1,CASE
WHEN CHARINDEX('.',@StrNumber)=0 THEN LEN(@StrNumber)
ELSE CHARINDEX('.', @StrNumber)-1 END)
SET @StrLength = LEN(@StrNumber)
IF(@StrLength > 3)
BEGIN
SET @LargeNumber = SUBSTRING(@StrNumber,-2,@StrLength)
SET @StrLength = LEN(@StrNumber)
WHILE LEN(@LargeNumber) > 0
BEGIN
SET @Chunk = ''
SET @ChunkVal = CAST(RIGHT(@LargeNumber,2) AS INT)
BEGIN
SELECT @Chunk = WORD FROM @NumericDetails
WHERE NUMBER = @ChunkVal
SELECT @Chunk=@Chunk+' '+PlaceName FROM @DigitDetails
WHERE DigitPlace = @DigitLoop
IF(@ChunkVal) <> 0
SET @StrFinal = @Chunk + ', ' + @StrFinal
SET @StrLength = LEN(@LargeNumber)
SET @LargeNumber=SUBSTRING(@LargeNumber,-1,@StrLength)
SET @DigitLoop = @DigitLoop + 1
END
END
SET @StrLength = 3
SET @StrFinal = RTRIM(@StrFinal)
SET @StrFinal = STUFF(@StrFinal,LEN(@StrFinal),1,'')
END
IF(@StrLength = 3)
BEGIN
SET @Chunk = ''
SET @SmallNumber = RIGHT(@StrNumber,3)
SET @ChunkVal = CAST(LEFT(@SmallNumber,1) AS INT)
IF (@ChunkVal > 0)
BEGIN
SELECT @Chunk=Word+' '+'Sau' FROM @NumericDetails
WHERE NUMBER = @ChunkVal
IF(@StrFinal <> '')
SET @StrFinal = @StrFinal + ', ' + @Chunk
ELSE
SET @StrFinal = @Chunk
END
SET @StrLength = 2
END
IF(@StrLength < 3 AND @StrLength > 0)
BEGIN
SET @Chunk = ''
SET @SmallNumber = RIGHT(@StrNumber,2)
SET @ChunkVal = CAST(LEFT(@SmallNumber,2) AS INT)
IF(@ChunkVal > 0)
BEGIN
SELECT @Chunk = WORD FROM @NumericDetails
WHERE NUMBER = @ChunkVal
IF(@StrFinal <> '')
SET @StrFinal = @StrFinal + ', ' + @Chunk
ELSE
SET @StrFinal = @Chunk
END
END
SELECT @StrFinal = ISNULL(@StrFinal,'')
RETURN N''+@StrFinal
END
|
Function has
been created.
Now I am
using this function in my sql script.
Here I am
using ‘AdventureWorksDW2012’
Database for this demo.
|
select pc.EnglishProductCategoryName As Product_Category,
CAST(SUM(FIS.SalesAmount) AS INT) AS Total_Sales_Amount,dbo.udf_NumberToHindiWords (CAST(SUM(FIS.SalesAmount) AS INT) ) AS Total_Sales_Amount_In_Word,
CAST(SUM(FIS.TaxAmt) AS INT) AS Total_TaxAmt,dbo.udf_NumberToHindiWords (CAST(SUM(FIS.TaxAmt)AS INT)) AS Total_TaxAmt_In_Word,
CAST(SUM(FIS.Freight) AS INT) AS Total_Freight, dbo.udf_NumberToHindiWords (CAST(SUM(FIS.Freight)As INT)) AS Total_Freight_In_Word
From FactInternetSales FIS
Inner join DimProduct P
on P.ProductKey=FIS.ProductKey
inner join DimProductSubcategory psc
on p.ProductSubcategoryKey=psc.ProductCategoryKey
Inner Join DimProductCategory pc
on pc.ProductCategoryKey=psc.ProductCategoryKey
Group by pc.EnglishProductCategoryName
UNION ALL
Select
'Total',
CAST(SUM(Total_SalesAmount) AS INT) AS Total_SalesAmount,dbo.udf_NumberToHindiWords (CAST(SUM(x.Total_SalesAmount)AS INT)) AS Total_Sales_Amount_In_Word,
CAST(SUM(Total_TaxAmt)AS INT) AS Total_TaxAmt,dbo.udf_NumberToHindiWords (CAST(SUM(x.Total_TaxAmt) AS INT)) AS Total_TaxAmt_In_Word,
CAST(SUM(Total_Freight)AS INT) AS Total_Freight,dbo.udf_NumberToHindiWords (CAST(SUM(x.Total_Freight)AS INT)) AS Total_Freight_In_Word
FROM (
select pc.EnglishProductCategoryName As Product_Category,
SUM(FIS.SalesAmount) AS Total_SalesAmount,
SUM(FIS.TaxAmt) AS Total_TaxAmt,
SUM(FIS.Freight) AS Total_Freight
From FactInternetSales FIS
Inner join DimProduct P
on P.ProductKey=FIS.ProductKey
inner join DimProductSubcategory psc
on p.ProductSubcategoryKey=psc.ProductCategoryKey
Inner Join DimProductCategory pc
on pc.ProductCategoryKey=psc.ProductCategoryKey
Group by pc.EnglishProductCategoryName
)X
|
See the result
Result in excel
