How can Convert amount into words in SQL

I think this will solve your problem.

1. Function to Convert one Digit Number to words.

CREATE    Function dbo.fConvertDigit(@decNumber decimal)

returns varchar(6)

as

Begin

declare

@strWords varchar(6)

Select @strWords = Case @decNumber

     When '1' then 'One'

     When '2' then 'Two'

     When '3' then 'Three'

     When '4' then 'Four'

     When '5' then 'Five'

     When '6' then 'Six'

     When '7' then 'Seven'

     When '8' then 'Eight'

     When '9' then 'Nine'

     Else ''

end

return @strWords

end

2. Function to convert 2 digit number to words.

CREATE    Function dbo.fConvertTens(@decNumber varchar(2))

returns varchar(30)

as

Begin

declare @strWords varchar(30)

--Is value between 10 and 19?

If Left(@decNumber, 1) = 1

begin

Select @strWords = Case @decNumber

     When '10' then 'Ten'

     When '11' then 'Eleven'

     When '12' then 'Twelve'

     When '13' then 'Thirteen'

     When '14' then 'Fourteen'

     When '15' then 'Fifteen'

     When '16' then 'Sixteen'

     When '17' then 'Seventeen'

     When '18' then 'Eighteen'

     When '19' then 'Nineteen'

end

end

else  -- otherwise it's between 20 and 99.

begin

Select @strWords = Case Left(@decNumber, 1)

     When '0' then '' 

     When '2' then 'Twenty '

     When '3' then 'Thirty '

     When '4' then 'Forty '

     When '5' then 'Fifty '

     When '6' then 'Sixty '

     When '7' then 'Seventy '

     When '8' then 'Eighty '

     When '9' then 'Ninety '

end

Select @strWords = @strWords + dbo.fConvertDigit(Right(@decNumber, 1))

end

--Convert ones place digit.

return @strWords

end

3. Function to convert amt in numbers to words. (Built with the help of above 2 functions)

CREATE function dbo.fNumToWords (@decNumber decimal(12, 2))
returns varchar(300)
As
Begin
Declare
@strNumber varchar(100),
@strRupees varchar(200),
@strPaise varchar(100),
@strWords varchar(300),
@intIndex integer,
@intAndFlag integer

Select @strNumber = Cast(@decNumber as varchar(100))
Select @intIndex = CharIndex('.', @strNumber)
if(@decNumber>99999999.99)
BEGIN
RETURN ''
END
If @intIndex > 0
begin
Select @strPaise = dbo.fConvertTens(Right(@strNumber, Len(@strNumber) - @intIndex))
Select @strNumber = SubString(@strNumber, 1, Len(@strNumber) - 3)
If Len(@strPaise) > 0 Select @strPaise = @strPaise + ' paise'
end
Select @strRupees = ''
Select @intIndex=len(@strNumber)
Select @intAndFlag=2
while(@intIndex>0)
begin
if(@intIndex=8)
begin
  Select @strRupees=@strRupees +dbo.fConvertDigit(left(@decNumber,1))+' Crore '
  Select @strNumber=substring(@strNumber,2,len(@strNumber))
  Select @intIndex=@intIndex-1
 
end
else if(@intIndex=7)
begin
  if(substring(@strNumber,1,1)='0')
  begin
   if substring(@strNumber,2,1)<>'0'
   begin
    if (@strRupees<>NULL and substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0' and @intAndFlag=2 and @strPaise=NULL)
    begin
     Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
     Select @intAndFlag=1
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Lakh '
    end
   
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
   else
   begin
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
  end
  else
  begin
   if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and substring(@strNumber,7,1)='0'  and @intAndFlag=2 and @strPaise='')
   begin
    Select @strRupees=@strRupees+' and ' + dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
    Select @intAndFlag=1
   end
   else
   begin
    Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Lakhs '
   end
   Select @strNumber=substring(@strNumber,3,len(@strNumber))
   Select @intIndex=@intIndex-2
  end
end
else if(@intIndex=6)
  begin
   if(substring(@strNumber,2,1)<>'0' or substring(@strNumber,3,1)<>'0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and substring(@strNumber,6,1)='0' and @intAndFlag=2 and @strPaise='')
   begin
   
    if len(@strRupees) <= 0
    begin
     if convert(int,substring(@strNumber,1,1)) = 1
     begin
      Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
      Select @intAndFlag=2
     end
     else
     begin
      Select @strRupees=@strRupees+'' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
      Select @intAndFlag=2
     end
    end
    else
    begin
     if convert(int,substring(@strNumber,1,1)) = 1
     begin
      Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
      Select @intAndFlag=1
     end
     else
     begin
      Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
      Select @intAndFlag=1
     end
    end
   end
   else
   begin
    if convert(int,substring(@strNumber,1,1)) = 1
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakh '
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Lakhs '
    end
   end
   Select @strNumber=substring(@strNumber,2,len(@strNumber))
   Select @intIndex=@intIndex-1
  end
else if(@intIndex=5)
  begin
   if(substring(@strNumber,1,1)='0')
   begin
    if substring(@strNumber,2,1)<>'0'
    begin
     if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
     begin
      Select @strRupees=@strRupees+' and ' +dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
      Select @intAndFlag=1
     end
     else
     begin
      Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,2,1))+' Thousand '
     end
     Select @strNumber=substring(@strNumber,3,len(@strNumber))
     Select @intIndex=@intIndex-2
    end
    else
    begin
     Select @strNumber=substring(@strNumber,3,len(@strNumber))
     Select @intIndex=@intIndex-2
    end
   end
   else
   begin
    if(substring(@strNumber,3,1)='0' and substring(@strNumber,4,1)='0' and substring(@strNumber,5,1)='0' and @intAndFlag=2 and @strPaise='')
    begin
     Select @strRupees=@strRupees+' and '+ dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
     Select @intAndFlag=1
    end
    else
    begin
     Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))+' Thousand '
    end
    Select @strNumber=substring(@strNumber,3,len(@strNumber))
    Select @intIndex=@intIndex-2
   end
  end
else if(@intIndex=4)
  begin
   if ( (substring(@strNumber,3,1)<>'0' or substring(@strNumber,4,1)<>'0') and substring(@strNumber,2,1)='0' and  @intAndFlag=2 and @strPaise='')
   begin
    Select @strRupees=@strRupees+' and' + dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
    Select @intAndFlag=1
   end
   else
   begin
   Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Thousand '
   end
   Select @strNumber=substring(@strNumber,2,len(@strNumber))
   Select @intIndex=@intIndex-1
  end
else if(@intIndex=3)
  begin
   if  substring(@strNumber,1,1)<>'0'
   begin
    Select @strRupees=@strRupees+dbo.fConvertDigit(substring(@strNumber,1,1))+' Hundred '
    Select @strNumber=substring(@strNumber,2,len(@strNumber))
   
    if( (substring(@strNumber,1,1)<>'0' or  substring(@strNumber,2,1)<>'0') and @intAndFlag=2 )
    begin
     Select @strRupees=@strRupees+' and '
     Select @intAndFlag=1
    end
    Select @intIndex=@intIndex-1
   end
   else
   begin
    Select @strNumber=substring(@strNumber,2,len(@strNumber))
    Select @intIndex=@intIndex-1
   end
  end
else if(@intIndex=2)
  begin
   if substring(@strNumber,1,1)<>'0'
   begin
    Select @strRupees=@strRupees+dbo.fConvertTens(substring(@strNumber,1,2))
    Select @intIndex=@intIndex-2
   end
   else
   begin
    Select @intIndex=@intIndex-1
   end
  end
else if(@intIndex=1)
  begin
   if(@strNumber<>'0')
   begin
    Select @strRupees=@strRupees+dbo.fConvertDigit(@strNumber)
   end
   Select @intIndex=@intIndex-1
   
  end
continue
end
if len(@strRupees)>0 Select @strRupees=@strRupees+ ' rupees '
IF(len(@strPaise)<>0)
BEGIN
if len(@strRupees)>0 Select @strRupees=@strRupees + ' and '
END
Select @strWords = IsNull(@strRupees, '') + IsNull(@strPaise, '')
select @strWords = @strWords + ' only'
Return @strWords
End

SSRS 2008 Column Chart with Line

If you want to create a column chart and have a line across the top showing the numbers in two different ways, it is easy to do in SSRS 2008. Just drag over the data into the data field twice. Right click on the data field on change the chart type to line and the other to column.

Here is a table I used to show this.

I created a Column chart and dragged over the date and numbers fields as shown.

Notice I placed the Number data in the data field twice. Then I right clicked on the data field and select the change chart type option. Then selected line chart.

After I select line chart and click OK the chart looks like the image below. It now has columns and lines on the same chart.

If you have multiple Series and only want to show for one series in the chart you will need alter the visibility. Here is the chart with a new columns I will use for the series.

Refresh the data set and drag over the rep to the series field.

Right Click on the data field and go to series properties...

Then set the visibility expression. Remember to set it to Not equal to, this is setting who to hide, not who to show.

The Chart will show multiple columns and a single line.

Split Function In Sql server

CREATE FUNCTION [dbo].[SDF_SplitString]
(
    @sString nvarchar(2048),
    @cDelimiter nchar(1)
)
RETURNS @tParts TABLE ( part nvarchar(2048) )
AS
BEGIN
    if @sString is null return
    declare @iStart int,
      @iPos int
    if substring( @sString, 1, 1 ) = @cDelimiter 
    begin
     set @iStart = 2
     insert into @tParts
     values( null )
    end
    else 
     set @iStart = 1
    while 1=1
    begin
     set @iPos = charindex( @cDelimiter, @sString, @iStart )
     if @iPos = 0
      set @iPos = len( @sString )+1
     if @iPos - @iStart > 0   
      insert into @tParts
      values ( substring( @sString, @iStart, @iPos-@iStart ))
     else
      insert into @tParts
      values( null )
     set @iStart = @iPos+1
     if @iStart > len( @sString ) 
      break
    end
    RETURN

END

Execute: SELECT Part from [dbo].[SDF_SplitString]('CAAB071C-2B5C-4C8C-836C-1EBCDFE6E5D5,318EBFFE-3FE0-4AED-B47F-DCC880ABEAC6',',')A

Difference Between Temporary Table and Table Variable – Summary

Both Temporary Tables (a.k.a # Tables) and Table Variables (a.k.a @ Tables) in Sql Server provide a mechanism for Temporary holding/storage of the result-set for further processing.

Below table lists out some of the major difference between Temporary Table and Table Variable. Each of these differences are explained in-detail with extensive list of examples in the next articles in this series which are listed above.

1. SYNTAX
Below is the sample example of Creating a Temporary Table, Inserting records into it, retrieving the rows from it and then finally dropping the created Temporary Table. -- Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) --Insert Two records INSERT INTO #Customer VALUES(1,'Basavaraj') INSERT INTO #Customer VALUES(2,'Kalpana') --Reterive the records SELECT * FROM #Customer --DROP Temporary Table DROP TABLE #Customer GO	Below is the sample example of Declaring a Table Variable, Inserting records into it and retrieving the rows from it. -- Create Table Variable DECLARE @Customer TABLE (  Id INT,  Name VARCHAR(50)   ) --Insert Two records INSERT INTO @Customer VALUES(1,'Basavaraj') INSERT INTO @Customer VALUES(2,'Kalpana') --Reterive the records SELECT * FROM @Customer GO RESULT:
2. MODIFYING STRUCTURE
Temporary Table structure can be changed after it’s creation it implies we can use DDL statements ALTER, CREATE, DROP. Below script creates a Temporary Table #Customer, adds Address column to it and finally the Temporary Table is dropped. --Create Temporary Table CREATE TABLE #Customer (Id INT, Name VARCHAR(50)) GO --Add Address Column ALTER TABLE #Customer ADD Address VARCHAR(400) GO --DROP Temporary Table DROP TABLE #Customer GO	Table Variables doesn’t support DDL statements like ALTER, CREATE, DROP etc, implies we can’t modify the structure of Table variable nor we can drop it explicitly.
3. STORAGE LOCATION
One of the most common MYTH about Temporary Table & Table Variable is that: Temporary Tables are created in TempDB and Table Variables are created In-Memory. Fact is that both are created in TempDB, below Demos prove this reality.
4. TRANSACTIONS
Temporary Tables honor the explicit transactions defined by the user.	Table variables doesn’t participate in the explicit transactions defined by the user.
5. USER DEFINED FUNCTION
Temporary Tables are not allowed in User Defined Functions.	Table Variables can be used in User Defined Functions.
6. INDEXES
Temporary table supports adding Indexes explicitly after Temporary Table creation and it can also have the implicit Indexes which are the result of Primary and Unique Key constraint.	Table Variables doesn’t allow the explicit addition of Indexes after it’s declaration, the only means is the implicit indexes which are created as a result of the Primary Key or Unique Key constraint defined during Table Variable declaration.
7. SCOPE
There are two types of Temporary Tables, one Local Temporary Tables whose name starts with single # sign and other one is Global Temporary Tables whose name starts with two # signs.Scope of the Local Temporary Table is the session in which it is created and they are dropped automatically once the session ends and we can also drop them explicitly. If a Temporary Table is created within a batch, then it can be accessed within the next batch of the same session. Whereas if a Local Temporary Table is created within a stored procedure then it can be accessed in it’s child stored procedures, but it can’t be accessed outside the stored procedure.Scope of Global Temporary Table is not only to the session which created, but they will visible to all other sessions. They can be dropped explicitly or they will get dropped automatically when the session which created it terminates and none of the other sessions are using it.	Scope of the Table variable is the Batch or Stored Procedure in which it is declared. And they can’t be dropped explicitly, they are dropped automatically when batch execution completes or the Stored Procedure execution completes.

Sqlhints