Format Number in SQL Server

To format a number in custom format like 99,99,999.00 there is no inbuilt function prior to SQL Server 2012, however same is available in SQL Sever 2012 onwards.

• Format Function in SQL Server 2012

In this article, I am sharing a user defined function to format the number in custom format for prior versions.

The function takes two parameters

• Number to be formatted.  Ex : (123456789.99999)
• Format. Ex : (99,99,99,9999,99)

and returns

• formatted number as output Ex : (12,34,56,789.99)

CREATE FUNCTION Fnformat(
      @pNumber NUMERIC(16, 5)
    , @pFormat VARCHAR(50)
)
RETURNS VARCHAR(50)
AS
BEGIN
    DECLARE   @lStr1      VARCHAR(50)
            , @lCtrS      SMALLINT
            , @lCtrE      SMALLINT
            , @lChr       CHAR
            , @lFormat    VARCHAR(50)
            , @lPosStr    SMALLINT
            , @lPosFormat SMALLINT
            , @lAfterDot  VARCHAR(10)
            , @lBeforeDot VARCHAR(50)
              
    SET @lStr1 = CAST(@pNumber AS VARCHAR)
    SET @lPosStr = Charindex('.', Reverse(@lStr1)) - 1
    SET @lPosFormat = Charindex('.', Reverse(@pFormat))
    IF @lPosFormat > 0
        SET @lPosFormat = @lPosFormat - 1
    IF @lPosStr < @lPosFormat
        SET @lAfterDot = RIGHT(@lStr1,@lPosStr)+Replicate('0', @lPosFormat-@lPosStr)
    ELSE
        SET @lAfterDot = LEFT(RIGHT(@lStr1, @lPosStr), @lPosFormat)
    IF @lPosStr > 0
        SET @lBeforeDot = Reverse(LEFT(@lStr1, Charindex('.', @lStr1) - 1))
    ELSE
        SET @lBeforeDot = Reverse(@lStr1)
    IF @lPosFormat > 0
        SET @lFormat = Reverse(Substring(@pFormat, 1, Charindex('.', @pFormat)- 1))
    ELSE
        SET @lFormat = Reverse(@pFormat)

    SET @lCtrS = 0
    SET @lCtrE = Len(@lFormat)
    WHILE @lCtrS < @lCtrE
    BEGIN
        SET @lCtrS = @lCtrS + 1
        SET @lChr = Substring(@lFormat, @lCtrS, 1)
        IF @lChr = ',' AND Len(@lBeforeDot) >= @lCtrS
            SET @lBeforeDot = LEFT(@lBeforeDot, @lCtrS - 1) + ',' + Substring(@lBeforeDot, @lCtrS, 100)
    END

    IF @lPosStr > 0 AND @lPosFormat > 0
        SET @lStr1 = Reverse(@lBeforeDot) + '.' + @lAfterDot
    ELSE
        SET @lStr1 = Reverse(@lBeforeDot)

    RETURN @lStr1
END

Let's test the function

SELECT dbo.fnFormat(12345.99, '99,999.99') as [Formatted Value]
SELECT dbo.fnFormat(1234567.99, '9,999,999.99') as [Formatted Value]
SELECT dbo.fnFormat(123456789.99, '99,99,99,999.99') as [Formatted Value]
SELECT dbo.fnFormat(123456789.99, '999,999,999.99') as [Formatted Value]

OUTPUT

Hierarchy of Employees in Sql Server with CTE

In this post, I am sharing the solution to find the hierarchy of employees using recursive CTE.

The solution will list all the employees coming under the hierarchy of an employee.

create table #tblemployee  (
      empid   varchar(10) primary key
    , empname varchar(10)
    , mgrid   varchar(10)
  )

insert into #tblemployee
select 'Emp001', 'Satish', NULL union all
select 'Emp002', 'Amit', 'Emp001'  union all
select 'Emp003', 'Sumit', 'Emp001' union all
select 'Emp004', 'Anil', 'Emp002' union all
select 'Emp005', 'Tarun', 'Emp003' union all
select 'Emp006', 'Sandeep', 'Emp003' union all
select 'Emp007', 'Abhay', 'Emp006' union all
select 'Emp008', 'Deepak', 'Emp002' union all
select 'Emp009', 'Suman', 'Emp007' union all
select 'Emp010', 'Raman', 'Emp007'

;with reportees as (
    select empid, empname, mgrid, 1 as level
    from   #tblemployee    
    where  mgrid = 'Emp001'
    union all
    select #tblemployee.empid, #tblemployee.empname, #tblemployee.mgrid, level + 1
    from   #tblemployee

    inner join reportees on #tblemployee.mgrid = reportees.empid
)

select  a.empid, a.empid, a.mgrid, b.empname as mgrname, level
from    reportees a
left join #tblemployee b on a.mgrid = b.empid
order by level

drop table #tblemployee

OUTPUT

Convert Number to Roman Numerals in SQL Server

In this post, I am sharing small function to convert Number to Roman Numeral in SQL Server

CREATE FUNCTION fnConvertIntToRoman(@i INT)
RETURNS VARCHAR(100)
AS
BEGIN
    RETURN    Replicate('M', @i/1000)
            + REPLACE(REPLACE(REPLACE(
                  Replicate('C', @i%1000/100),
                  Replicate('C', 9), 'CM'),
                  Replicate('C', 5), 'D'),
                  Replicate('C', 4), 'CD')
             + REPLACE(REPLACE(REPLACE(
                  Replicate('X', @i%100 / 10),
                  Replicate('X', 9),'XC'),
                  Replicate('X', 5), 'L'),
                  Replicate('X', 4), 'XL')
             + REPLACE(REPLACE(REPLACE(
                  Replicate('I', @i%10),
                  Replicate('I', 9),'IX'),
                  Replicate('I', 5), 'V'),
                  Replicate('I', 4),'IV')
END

Let's test the function

select dbo.fnConvertIntToRoman(1) as 'Roman Value'
select dbo.fnConvertIntToRoman(15) as 'Roman Value'
select dbo.fnConvertIntToRoman(118) as 'Roman Value'
select dbo.fnConvertIntToRoman(1008) as 'Roman Value'

OUTPUT

Cumulative sum in Sql Server

There are numerous ways to compute cumulative sum in SQL Server.

In this post, we will see how to compute cumulative sum in SQL Server using Row_Number and self join.

NOTE: This would work on SQL Server 2005 onwards

Let's take an example where we will calculate student wise cumulative sum of marks.

First create a Marks table and populate some data in it

create table tblMarks (
    studid  varchar(20)
    , subcode varchar(20)
    , marks   int
)

insert into tblMarks
select 'Stud1', 'English', 60 union all
select 'Stud1', 'History', 70 union all
select 'Stud1', 'Maths', 80 union all
select 'Stud1', 'Science', 75 union all
select 'Stud2', 'English', 55 union all
select 'Stud2', 'History', 60 union all
select 'Stud2', 'Maths', 57 union all
select 'Stud2', 'Science', 65

Now let write query to calculate cumulative sum for all subjects student wise

;with cte as (
    select row_number() over (order by studid, subcode) as rownum,*
    from tblMarks
)
select a.studid, a.subcode, a.marks, SUM(b.marks) AS [Cumulative Sum]
from cte a
left join cte b on a.studid = b.studid AND b.rownum <= a.rownum
group by a.studid, a.rownum, a.subcode, a.marks
order by a.studid, a.subcode

OUTPUT

Refer related post to calculate cumulative sum using recursive CTE,
Running total in SQL Server

Refer related post for SQL Server 2012,
Cumulative Sum in SQL Server 2012