Passing table to Function SQL Server

In SQL Server, there is no direct way of passing table to a function.

In this article, I am sharing how you can do it with the help of user-defined Type as table and Table-valued parameter which were introduced in SQL Server 2008

In SQL Server 2008, we can create user-defined Type as table that represents the definition of a table structure. We can then use this type to declare table-valued parameters for functions or to declare table variables that you want to use in a function.

Let's take an example where we will pass a table with one column and a separator to the function and it will return all table column values with the passed separator as a single value.

Step 1: Create a Type as Table with name TableType that will accept a table having one varchar column

create type TableType
as table ([value] [varchar](100) null)

Step 2: Create a function that will accept above declared TableType as Table-Valued Parameter and String Value as Separator

create function dbo.fn_get_string_with_delimeter (
    @table TableType readonly, @Separator varchar(5)
)
returns varchar(500)
as
begin
    declare @return varchar(500)
    set @return = stuff((select @Separator + value from @table for xml path('')),1,1,'')
    return @return
end

Step 3: Pass table with one varchar column to the user-defined type TableType and '-' as separator in the function

declare @tab TableType
insert into @tab
select 'Amit' union all
select 'Sandeep' union all
select 'Abhay' union all
select 'Ritesh'
select dbo.fn_get_string_with_delimeter(@tab, '-')

OUTPUT

Generate Weekdays in SQL Server

In this post, I am sharing simple script to generate weekdays in SQL server.

select  [DayId] = number
      , [DayName] = datename(dw, convert(datetime, cast(19000100 + number as varchar)))
from master.dbo.spt_values
where type = 'P' and number between 1 and 7

Result

Refer related posts

• generate list of dates in sql
• generate list of months in sql

Subtotal in SQL Query

Generate subtotals on different columns and grand total on a resultset in SQL is a very common requirement

In this article, I will show you how to achieve this easily with the help of Grouping function Rollup provided by Microsoft in SQL Server.

Example

Lets take Sales Table with fields year, month, prod_id, qty

Now, assume we have to find out the quantity of items sold product wise, month wise and year wise and total. Now let's write a query to calculate the same within the single query with the help of Grouping function Rollup.

DECLARE @sales TABLE(
    year INT, month INT, prod_id VARCHAR(10), qty INT
)

INSERT INTO @sales
SELECT 2011, 1, 'P1', 10 UNION ALL
SELECT 2011, 1, 'P1', 50 UNION ALL
SELECT 2011, 1, 'P2', 55 UNION ALL
SELECT 2011, 1, 'P2', 80 UNION ALL
SELECT 2011, 2, 'P1', 50 UNION ALL
SELECT 2011, 2, 'P1', 70 UNION ALL
SELECT 2011, 2, 'P2', 60 UNION ALL
SELECT 2011, 2, 'P2' ,20 UNION ALL
SELECT 2012, 1, 'P1', 50 UNION ALL
SELECT 2012, 1, 'P1', 25 UNION ALL
SELECT 2012, 1, 'P2', 15 UNION ALL
SELECT 2012, 1, 'P2', 20 UNION ALL
SELECT 2012, 2, 'P1', 50 UNION ALL
SELECT 2012, 2, 'P1', 70 UNION ALL
SELECT 2012, 2, 'P2', 60 UNION ALL
SELECT 2012, 2, 'P2', 20

SELECT [Year]   = COALESCE(CAST(year AS VARCHAR), 'Grand Total')
     , [Month]  = CASE WHEN month IS NULL AND year IS NOT NULL
                  THEN 'Total for Year:' + CAST(year AS VARCHAR)
                  ELSE CAST(month AS VARCHAR) END
     , [Product]= CASE WHEN prod_id IS NULL AND month IS NOT NULL
                  THEN 'Total for Month:' + CAST(month AS VARCHAR)
                  ELSE CAST(prod_id AS VARCHAR) END
     , [Qty]    = SUM(qty)       
FROM  @sales
GROUP BY year, month, prod_id
WITH ROLLUP

OUTPUT

In the above query, we have used COALESCE. Refer below post to understand COALESCE in detail

• COALESCE in SQL Server

Cumulative Sum in Sql Server 2012

In this article, I am sharing how cumulative total can be calculated very easily in SQL Server 2012.

Note: Solution will work in Sql Server 2012 onward.

For prior versions, refer related posts

• Cumulative Sum in SQL Server using Row_Number 
• Running Total in SQL Server using recursive CTE

Let's create Marks Table and populate some data in it

create table Marks(
     studid  VARCHAR(20),
     subcode VARCHAR(20),
     marks   INT
)
insert into Marks
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

In SQL Server 2012, with the expansion of the OVER clause to include ORDER BY support with aggregates, it becomes very easy to calculate cumulative sum in SQL Server

Query to calculate cumulative sum for all subjects student wise

SELECT *, SUM(marks) OVER(PARTITION BY studid ORDER BY subcode) [Cumulative Sum]
FROM   Marks

OUTPUT

Generate list of Months in SQL Server

In this article, we will learn how to generate list of months in Sql Server and in which scenario it is required?

Script to generate the list of months

declare @year int
set @year = 2012

select number as mth, DATENAME(MONTH, cast(@year*100+number as varchar) + '01')  as monthname 
from master.dbo.spt_values
where type = 'P' and number between 1 and 12

Result

Now, let's take a scenario where it is required to generate list of months. Suppose, there is a table "sales" that contains sale data Script to create sale table with data

create table sales(
      cust_code varchar(10)
    , prod_code varchar(5)
    , sale_date datetime
    , qty int
)
insert into sales
select 'C001', 'P01', '01/01/2012', 2 union all
select 'C002', 'P01', '02/02/2012', 2 union all
select 'C001', 'P01', '03/03/2012', 5 union all
select 'C002', 'P01', '05/05/2012', 2 union all
select 'C003', 'P01', '05/05/2012', 3

Now, lets assume using the above data, you have to generate a sale report to get total quantity of a product sold each month. Now the problem is, there is no data for some months, so you would not be getting all the months in the result but the requirement is to have all the months with zero quantity

declare @year int
set @year = 2012
select   MONTH(sale_date) as mth, DATENAME(MONTH, sale_date) as monthname, SUM(qty) as total_qty
from     sales
where    YEAR(sale_date) = @year
group by MONTH(sale_date), DATENAME(MONTH, sale_date)
order by MONTH(sale_date)

Result

Now, in order to generate all the months in the sale report, first we are generating a list of all months and then using "Left Join" with the existing query to display all the months in the result.

declare @year int
set @year = 2012

select  m.number as mth, DATENAME(MONTH, cast(@year*100+number as varchar) + '01') as monthname
 , @year as year, isnull(total_qty,0) as total_qty
from (
 select number
 from    master.dbo.spt_values 
 where type = 'P' and number between 1 and 12
) m 
left join (
    select   MONTH(sale_date) as mth,SUM(qty) as total_qty
    from     sales
    where    YEAR(sale_date) = @year
    group by MONTH(sale_date)
) s on m.number = s.mth

Result

You can also refer related posts to generate list of dates and weekdays

• generate list of dates in sql
• generate weekdays in sql

Difference between Char and Varchar

Recently, one of my colleague asked me the difference between the datatypes char, varchar, nchar and nvarchar in SQL Server.  Also, in the past I have seen the same question being asked in interviews. So, i decided to write a post on the same.

char(n): takes exact "n" bytes regardless of the data you store in it (with trailing spaces, if the data length is less than "n").

varchar(n): takes "x" no. of bytes depending on the data + 2 bytes to store the length of the data

nchar(n) and nvarchar(n) are exactly same as char and varchar respectively but it takes exact double spaces to support multilingual language, 1 byte to store Unicode character for each character

Now question is where to use char over varchar?
Use char dataype only for fixed length column, means, when you know in advance that your data will always be of fixed length.
For example Phone Number, Country code

declare @Ph_No CHAR(10)
declare @Country_Code CHAR(3)

Now, one more question comes to mind even for fixed length columns, what is the difference char(10) and varchar(10), when both can store 10 bytes of data?
char would take 10 bytes while varchar 10+2 bytes
Select operation is fast with char columns as compare to varchar
Sample code for reference

declare @char char(5)
set @char = 'ABC' -- 5 bytes
set @char = 'ABCDE' -- 5 bytes

declare @varchar varchar(5)
set @varchar = 'ABC' -- 3 + 2 = 5 bytes
set @varchar = 'ABCDE' -- 5 + 2 = 7 bytes

declare @nchar nchar(5)
set @nchar = 'ABC' -- 5*2 = 10 bytes
set @nchar = 'ABCDE' -- 5*2 = 10 bytes

declare @nvarchar nvarchar(5)
set @nvarchar = 'ABC' -- 3*2+2 = 8 bytes
set @nvarchar = 'ABCDE' -- 5*2+2 = 12 bytes

Generate List of dates in SQL Server

Generating list of dates is very common requirement in reporting. In this post, we will learn how to generate a list of date range for the given start date and end date.

Below is the simple script using recursive CTE to generate the list of dates

declare @date_from datetime, @date_to datetime
set @date_from = '11/01/2012'
set @date_to = '11/10/2012'
;with dates as(
    select @date_from as dt
    union all
    select DATEADD(d,1,dt) from dates where dt<@date_to
)
select * from dates

OUTPUT

Now, let's take a scenario where it is required to generate range of dates.

Suppose, there is a table "sales" that contains sale data

Lets create a Sales Table and populate some data in it.

create table sales(
      cust_code varchar(10)
    , prod_code varchar(5)
    , sale_date datetime
    , qty int
)
insert into sales
select 'C001', 'P01', '11/01/2012', 2 union all
select 'C002', 'P01', '11/02/2012', 2 union all
select 'C001', 'P01', '11/03/2012', 5 union all
select 'C002', 'P01', '11/05/2012', 2 union all
select 'C003', 'P01', '11/05/2012', 3

Now, lets assume using the above data, you have to generate sale report to get total quantity of a product sold each day. Now the problem is, there is no data for some days between the start and end date, so you would not be getting all the dates in the result, but the requirement is all dates should appear in the result with zero quantity even if there is no sale.

declare @date_from datetime, @date_to datetime
set @date_from = '11/01/2012'
set @date_to = '11/10/2012'
select   sale_date, SUM(qty) as total_qty
from     sales
where    sale_date between @date_from and @date_to
group by sale_date
order by sale_date

OUTPUT

Now, in order to generate all the dates in the sale report, first we are generating a list of all dates between start and end date using recursive CTE and then using Left Join with the existing query to display all the dates in the result.

declare @date_from datetime, @date_to datetime
set @date_from = '11/01/2012'
set @date_to = '11/10/2012'
;with dates as(
    select @date_from as dt
    union all
    select DATEADD(d,1,dt) from dates where dt<@date_to
)
select  d.dt, isnull(total_qty,0) as total_qty
from    dates d 
left join (
    select   sale_date, SUM(qty) as total_qty
    from     sales
    where    sale_date between @date_from and @date_to
    group by sale_date
) s on d.dt = s.sale_date
order by d.dt

OUTPUT

Related posts:

• Generate Weekdays in SQL Server
• Generate List of Months in SQL Server

var in c#

In this post, i am trying to unleash the exact use of the var keyword.

In .Net, when you simply declare a variable with datatype int, it is explicit declaration

int i = 0; //explicit

but when declared with var, compiler looks for the data assigned during declaration and accordingly  assign appropriate datatype to it during compilation process.

var v = 0; //implicit

For example, In above declaration, we are assigning numeric value 0 to var, so var would be replaced to int during the generation of IL Code. var define datatype statically not on run-time. In other words, they are not like objects which can once point to int, then string on run-time. variable declared with var is initialized within the declaration statement and can't be initialized with null.

Why use var?
The most important aspect of var in existence is LINQ with Anonymous Type. Using var makes your code very simple and short.

Let's first take an example code of LINQ with Anonymous Type without using var

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;

namespace ConsoleApplication1
{
    class Program
    {
        class Details
        {
            public int Length;
            public string Value;
        }

        static void Main(string[] args)
        {
            string[] Names = { "Sandeep", "Abhay", "Ritesh" };
            IEnumerable<Details> details = from x in Names select new Details { Length = x.Length, Value = x };
            foreach (Details d in details)
            {
                Console.WriteLine(string.Format("Name : {0}, Length : {1}", d.Value, d.Length));
            }
            Console.Read();
        }
    }
}

In the above code, we have created a strongly typed object which we can get from a LINQ query. But, the problem is that we have written a lot of code, created a class, then put the class in IEnumerable and then we am getting property. Now using the var the same can be achieved simply with less code. The biggest advantage of using var with LINQ and Anonymous time is that we do not need to create class Details anymore.

Example of LINQ with Anonymous Type using var

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string[] Names = { "Sandeep", "Abhay", "Ritesh" };
            var v = from x in Names select new { Length = x.Length, Value = x };
            foreach (var d in v)
            {
                Console.WriteLine(string.Format("Name : {0}, Length : {1}", d.Value, d.Length));
            }
            Console.Read();
        }
    }
}

OUTPUT

Tuple in C#

C# 4.0 included a new feature called Tuple.

A tuple is a data structure that has a specific number and sequence of elements. Tuple can be used in several ways.

In this post, I am sharing an example, how tuple can be used to return multiple values from a method.

In the example, we are passing two int values to the method and method is performing four mathematical operations (add, subtract, multiplication, division) and returning a tuple with 3 int and 1 double values for these operations.

using System;

namespace ConsoleApplication1
{

    class clsMain
    {
        static void Main(string[] args)
        {
            clsTuple objTuple = new clsTuple();
            var tuple = objTuple.Operations(10, 3);
            Console.WriteLine("Sum:{0}, Subtract:{1}, Multiply :{2}, Division:{3}", tuple.Item1, tuple.Item2, tuple.Item3, tuple.Item4);
            System.Threading.Thread.Sleep(2000);
        }
    }

    class clsTuple
    {

        public Tuple<int, int, int, double> Operations(int i, int j)
        {
            return Tuple.Create(i + j, i - j, i * j, Convert.ToDouble(i) / j);
        }      
    }
}

OUTPUT

Check all Checkbox in Gridview using JQuery

Selecting all checkboxes by selecting the checkbox in the header of the gridview is a common operation in ASP.NET applications.

We could do the same thing with javascript but with JQuery same can de done with writing less code and less effort.

Markup code (Gridview1.aspx)

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="GridView1.aspx.cs" Inherits="GridView1" %>
<html>
<head runat="server">
    <title></title>
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.7/jquery.min.js" type="text/javascript"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $("#<%=grid.ClientID%> input[id*='chkAll']:checkbox").click(function () {
                if ($(this).is(':checked'))
                    $("#<%=grid.ClientID%> input[id*='chkSelect']:checkbox").attr('checked', true);
                else
                    $("#<%=grid.ClientID%> input[id*='chkSelect']:checkbox").attr('checked', false);
            });

            $("#<%=grid.ClientID%> input[id*='chkSelect']:checkbox").click(CheckUncheckAll);
        });

        function CheckUncheckAll() {
            var totalCheckboxes = $("#<%=grid.ClientID%> input[id*='chkSelect']:checkbox").size();
            var checkedCheckboxes = $("#<%=grid.ClientID%> input[id*='chkSelect']:checkbox:checked").size();

            if (totalCheckboxes == checkedCheckboxes) {
                $("#<%=grid.ClientID%> input[id*='chkAll']:checkbox").attr('checked', true);
            }
            else {
                $("#<%=grid.ClientID%> input[id*='chkAll']:checkbox").attr('checked', false);
            }
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="grid" runat="server" AutoGenerateColumns="False" DataKeyNames="EmpId,EmpName"
            CellPadding="4" ForeColor="#333333" GridLines="None">
            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
            <Columns>
                <asp:TemplateField>
                    <HeaderTemplate>
                        <asp:CheckBox runat="server" ID="chkAll" />
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:CheckBox runat="server" ID="chkSelect" />
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField DataField="EmpId" HeaderText="Employee ID" ItemStyle-Width="100px" ItemStyle-Wrap="true">
                    <ItemStyle Wrap="True" Width="100px"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField DataField="EmpName" HeaderText="Name" ItemStyle-Width="100px" ItemStyle-Wrap="true">
                    <ItemStyle Wrap="True" Width="100px"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField DataField="Gender" HeaderText="Gender" ItemStyle-Width="100px" ItemStyle-Wrap="true">
                    <ItemStyle Wrap="True" Width="100px"></ItemStyle>
                </asp:BoundField>
                <asp:BoundField DataField="DOJ" HeaderText="Date of Joining" ItemStyle-Width="100px"
                    ItemStyle-Wrap="true">
                    <ItemStyle Wrap="True" Width="100px"></ItemStyle>
                </asp:BoundField>
            </Columns>
            <EditRowStyle BackColor="#999999" />
            <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
            <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
            <RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
            <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
            <SortedAscendingCellStyle BackColor="#E9E7E2" />
            <SortedAscendingHeaderStyle BackColor="#506C8C" />
            <SortedDescendingCellStyle BackColor="#FFFDF8" />
            <SortedDescendingHeaderStyle BackColor="#6F8DAE" />
        </asp:GridView>
    </div>
    </form>
</body>
</html>

Code Behind (Gridview1.aspx.cs code)

using System;
using System.Collections.Generic;
public partial class GridView1 : System.Web.UI.Page
{   
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
            BindGrid();
    }

    private void BindGrid()
    {
        List<Emp> EmpList = new List<Emp>();
        EmpList.Add(new Emp { EmpID = "EMP001", EmpName = "Sandeep Mittal", Gender = "Male", DOJ = "01/01/2011" });
        EmpList.Add(new Emp { EmpID = "EMP002", EmpName = "Ritesh Kumar", Gender = "Male", DOJ = "05/01/2011" });
        EmpList.Add(new Emp { EmpID = "EMP003", EmpName = "Abhay Kumar", Gender = "Male", DOJ = "05/03/2011" });
        grid.DataSource = EmpList;
        grid.DataBind();
    }  
}

public class Emp
{
    public Emp()
    {       
    }

    private string _EmpId;
    private string _EmpName;
    private string _Gender;
    private string _DOJ;
    public string EmpID { get { return _EmpId; } set { _EmpId = value; } }
    public string EmpName { get { return _EmpName; } set { _EmpName = value; } }
    public string Gender { get { return _Gender; } set { _Gender = value; } }
    public string DOJ { get { return _DOJ; } set { _DOJ = value; } }     
}

How to check if Page is refreshed in ASP.Net

In this post, i have posted the code to detect if page is refreshed in asp.net. But before that, i would like to share the scenario where it is required.

Question
why there is need to detect if page refreshed in asp.net?.

Answer
Suppose you have a button "Save" on the page and on click of it, you are inserting a record in the database. Now if user click on Save, a record get inserted in the database but now if he/she refreshes the page, the Save Click event gets fire again and as a result same record get inserted again in the database. In order to handle this, we need to detect if page is refreshed, so that we can stop reinserting the record in the database.

PageRefresh.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="PageRefresh.aspx.cs" Inherits="PageRefresh" %>
<html>
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Button ID="btnSave" runat="server" OnClick="btnSave_Click" Text="Save" />
    </div>
    </form>
</body>
</html>

PageRefresh.aspx.cs

using System;
using System.Web.UI;

public partial class PageRefresh : System.Web.UI.Page
{
    protected void Page_PreRender(object sender, EventArgs e)
    {
        ViewState["IsPageRefreshed"] = Session["IsPageRefreshed"];
    }

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            Session["IsPageRefreshed"] = Server.UrlDecode(System.DateTime.Now.ToString());
        }
    }

    protected void btnSave_Click(object sender, EventArgs e)
    {
        if (Session["IsPageRefreshed"].ToString() == ViewState["IsPageRefreshed"].ToString())
        {
            Session["IsPageRefreshed"] = Server.UrlDecode(System.DateTime.Now.ToString());
            ScriptManager.RegisterClientScriptBlock(this, this.Page.GetType(), "abc", "alert('Save Clicked')", true);
        }
        else
        {
            ScriptManager.RegisterClientScriptBlock(this, this.Page.GetType(), "abc", "alert('Page Refreshed')", true);
        }
    }

}

Split Function in SQL

The purpose of split function (table valued function) is to split a delimited value into multiple values based on delimiter and display in tabular form.

Split Function takes two parameters

• Delimited Value: The delimited value to be split in multiple values.
• Delimiter: The delimiter like comma, colon, semicolon, pipe etc. on the basis of which delimited value to be split.

and returns table of multiple values delimited on the basis of delimiter.

CREATE FUNCTION dbo.split(
    @delimited NVARCHAR(MAX),
    @delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
    DECLARE @xml XML
    SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
    INSERT INTO @t(val)
    SELECT  r.value('.','varchar(MAX)') as item
    FROM  @xml.nodes('/t') as records(r)
    RETURN
END

Step wise explanation of split Function

• Converting the delimited value into XML using replace function, where all delimiters are getting replaced with tag to make it as XML and assigning the same to XML Variable
• Reading the Node from XML variable and storing the values in table variable @t.
• Returning the table variable @t

Let's take an example to split a comma delimited value into multiple values on the basis of delimiter (comma) using split function.

SELECT * FROM dbo.split('val1,val2,val3', ',')

Now let's take another example where you have multiple delimited value stored in a table against an ID and each value needs to split on the basis of delimiter.

We would be using Cross Apply  clause in the example.

DECLARE @TAB TABLE(
    id int, list varchar(100)
)
INSERT INTO @TAB
SELECT 1, 'apple;banana;grapes;orange'
UNION ALL SELECT 2, 'potato;onion;carrot;brinjal'
SELECT * FROM @TAB
SELECT    t.id, s.val
FROM    @TAB t
CROSS APPLY dbo.split(t.list, ';') s

RESULT

SQL Calculate Distance between Two Points

In this post, I am sharing how to calculate distance between two GEO locations given geographical points (latitude and longitude) of the locations.

Below is a function that takes latitude and longitude of the locations and returns the distance between the locations in miles

Parameters:

• lat1: Latitude of Source Location
• long1: Longitude of Destination Locations
• lat2: Latitude of Source Location
• long2: Longitude of Destination Locations

CREATE FUNCTION dbo.udf_GetDistance(@lat1 float, @long1 float, @lat2 float, @long2 float)
RETURNS FLOAT
AS
BEGIN   
    DECLARE @DToR as float
    DECLARE @Ans as float
    DECLARE @Miles as float

    SET @DToR= 57.29577951
    SET @Ans = 0
    SET @Miles = 0

    IF @lat1 IS NULL OR @lat1 = 0 OR @long1 IS NULL OR @long1 = 0 OR @lat2 IS NULL OR @lat2 = 0 OR @long2 IS NULL OR @long2 = 0
    BEGIN
        RETURN ( @Miles )
    END
    SET @Ans = SIN(@lat1 / @DToR) * SIN(@lat2 / @DToR) + COS(@lat1 / @DToR) * COS( @lat2 / @DToR) * COS(ABS(@long2 - @long1 )/@DToR)
    SET @Miles = 3959 * ATAN(SQRT(1 - SQUARE(@Ans)) / @Ans)   
    RETURN ( @Miles )
END

To calculate distance we need geographical points of the locations that we can get from google API's.

For intro refer, https://developers.google.com/maps/documentation/geocoding/intro

Using below link, we would be taking Geo points of North-East and South-West of Delhi and will pass to the function to get the distance between two.

http://maps.googleapis.com/maps/api/geocode/json?address=delhi&sensor=false

Let's pass the latitude and longitude of both that we get using above link to the function and see the result.

SELECT dbo.udf_GetDistance(28.88225340, 77.34169940, 28.42219750, 76.85382840) as Miles

RESULT

Sequence in SQL Server 2012

Microsoft introduced Sequence object in SQL Server 2012 release.

A Sequence is a user defined, schema bound object that will generate a sequence of numeric values (in ascending or descending order) according to specification. Unlike identity columns, a sequence is created independent of any table. A few interesting differences between the two are;

• A Sequence object is independent of any table, whereas the Identity column property is table specific
• Unlike Identity, you can generate a new sequence value before using it in an insert Statement
• You can define both the minimum & maximum values, as well as cycling & cache size options for a sequence
• Unlike Identity, a sequence object will increment its value only when it is explicitly called

Let's create a sequence object and see how it works.

CREATE SEQUENCE dbo.emp_sequence AS INT
START WITH 1
INCREMENT BY 1

We have created a sequence with name emp_sequence where the starting value will be 1 and next value will be incremented by 1.

Now let's fetch first value for the sequence.

SELECT NEXT VALUE FOR dbo.emp_sequence as first_emp

RESULT

Check the output where first value is fetched as 1.

Now let's fetch next value for the sequence

SELECT NEXT VALUE FOR dbo.emp_sequence as next_emp

RESULT

The next value is fetched as 2 which is incremented by 1 from the previous value.

Now let's see how a sequence object can be used with table and how can act as alternative to identity.

For this, we are going to use the same sequence we created in the earlier example, so lets first reset the initial value of sequence

ALTER SEQUENCE dbo.emp_sequence
RESTART WITH 1
INCREMENT BY 1;

Now lets create a temp table employee where we will insert the data in emp_no column using sequence

CREATE TABLE #employee (emp_no int, emp_name VARCHAR(10));
INSERT INTO #employee VALUES
  (NEXT VALUE FOR dbo.emp_sequence, 'Ajay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Vijay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Sanjay')
SELECT * FROM #employee
DROP TABLE #employee

RESULT

Check the output where emp_no is generated sequentially as 1,2,3 using sequence.

In the script we have also dropped the temp table, but the current value of sequence has been incremented to 3.

Let's run the same script again

CREATE TABLE #employee (emp_no int, emp_name VARCHAR(10));
INSERT INTO #employee VALUES
  (NEXT VALUE FOR dbo.emp_sequence, 'Ajay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Vijay')
, (NEXT VALUE FOR dbo.emp_sequence, 'Sanjay')
SELECT * FROM #employee
DROP TABLE #employee

RESULT

Check the output where emp_no is generated as 4,5,6 using the same script. This is because the value of sequence was 3.

Lead and Lag Functions in SQL Server

SQL Server 2012 introduced new analytical function LEAD() and LAG().

These functions accesses data from nth next row and nth previous row in the same result set without the use of a self-join

• LEAD():  Used to access data from nth next row in the same result set without the use of a self-join.
• LAG(): Used to access data from nth previous row in the same result set without the use of a self-join

Syntax

LEAD (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )

Parameters

• scalar_expression: column name for which value to be accessed
• offset: nth previous (for lag) or next (for lead) row  to access of the column
• default: default value to display if nth row not exists

Example - 1

DECLARE @Test_table TABLE(
    Year INT, Sale INT
)

INSERT INTO @Test_table VALUES
('2008',5000000), ('2009',5500000), ('2010',5250000), ('2011',6025000), ('2012',6200000)

SELECT    Year, Sale
        , LEAD(Sale) OVER (ORDER BY Year) AS [Next Year Sale]
        , LAG(Sale) OVER (ORDER BY Year) AS [Prev Year Sale]
        , LEAD(Sale, 2) OVER (ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2) OVER (ORDER BY Year) AS [2nd Prev Year Sale]
        , LEAD(Sale, 2, 0) OVER (ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2, 0) OVER (ORDER BY Year) AS [2nd Prev Year Sale]
FROM    @Test_table

RESULT

Example - 2 (With Partition By)

DECLARE @Test_table TABLE(
    Year INT, Zone VARCHAR(10),  Sale INT
)

INSERT INTO @Test_table VALUES
 ('2009', 'East', 5500000), ('2010', 'East', 5250000), ('2011', 'East', 6025000), ('2012', 'East', 6200000)
,('2009', 'West', 5200000), ('2010', 'West', 5250000), ('2011', 'West', 5525000), ('2012', 'West', 5700000)
,('2009', 'North', 4700000), ('2010', 'North', 4800000),('2011', 'North', 5000000), ('2012', 'North', 5050000)
,('2009', 'South', 7200000), ('2010', 'South', 7500000), ('2011', 'South', 7800000), ('2012', 'South', 8000000)

SELECT    Year, Sale
        , LEAD(Sale) OVER (PARTITION BY Zone ORDER BY Year) AS [Next Year Sale]
        , LAG(Sale) OVER (PARTITION BY Zone ORDER BY Year) AS [Prev Year Sale]
        , LEAD(Sale, 2) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Prev Year Sale]
        , LEAD(Sale, 2, 0) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Next Year Sale]
        , LAG(Sale, 2, 0) OVER (PARTITION BY Zone ORDER BY Year) AS [2nd Prev Year Sale]
FROM    @Test_table

RESULT

Auto Generate Employee Id in SQL Server

In this post, I have explained how Employee Id can be auto generated very simply with an identity and a computed column.

Suppose, we have to generate employee id's in the format AA00001 where

• 1st character - 1st character of First Name
• 2nd character - 2st character of Last Name
• Next 5 characters - Sequential Numerical value

To generate sequential values take an identity column that would do the auto increment for you.
Now take a Computed Column to generate employee id and set the formula to generate employee id in the desired format like below.

Now you may have the requirement to make the employee id column as Primary Key. For this, make the Computed Column as Persisted, as primary key can be created on Computed Column only if the column is Persisted.

CREATE TABLE tblEmployee(
    Id INT identity
    , EmpId AS (LEFT(EmpFirtsName,1) + LEFT(EmpLastName,1)
      + REPLICATE('0', 5-LEN(Id)) + CAST(Id AS VARCHAR)) PERSISTED PRIMARY KEY
    , EmpFirtsName VARCHAR(50) NOT NULL
    , EmpLastName VARCHAR(50) NOT NULL
)

INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Ajay', 'Anand')
INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Sanjay', 'Singh')
INSERT into tblEmployee(EmpFirtsName, EmpLastName) values('Vijay', 'Kumar')

SELECT * FROM tblEmployee

RESULT

Merge two DataTables in C#

In this post, I am sharing how Merge() method is used to merge two DataTables in C# / ASP.Net

There are certain points need to be considered when using Merge() method to merge datatables.

Let's assume there are 2 datatables and we have to merge both the datatables on the basis of Employee Id then,

• Both the DataTable should have a column with the same name say "EmpId".
• Values in columns "EmpId" of both the tables should be unique.

MergeTable.aspx Code

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="MergeTable.aspx.cs" Inherits="MergeTable" %>
<html>
<head runat="server">
    <title>Merge Two DataTables</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    </div>
    </form>
</body>
</html>

MergeTable.aspx.cs

using System;
using System.Data;

public partial class MergeTable : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            DataTable dataTable1 = null, dataTable2 = null, dataTable3 = null;
            dataTable1 = DataTable1();
            dataTable2 = DataTable2();
            dataTable1.PrimaryKey = new DataColumn[] { dataTable1.Columns["EmpId"] };
            dataTable2.PrimaryKey = new DataColumn[] { dataTable2.Columns["EmpId"] };
            dataTable3 = dataTable1.Copy();
            dataTable3.Merge(dataTable2, false, MissingSchemaAction.Add);
            dataTable3.AcceptChanges();
            GridView1.DataSource = dataTable3;
            GridView1.DataBind();
        }
    }

    private DataTable DataTable1()
    {
        DataRow dataRow = null;
        DataTable dt1 = new DataTable();
        dt1.Columns.Add("EmpId");
        dt1.Columns.Add("EmpName");
        dataRow = dt1.NewRow();
        dataRow["EmpId"] = "EMP001";
        dataRow["EmpName"] = "Ajaj Kumar";
        dt1.Rows.Add(dataRow);
        dataRow = dt1.NewRow();
        dataRow["EmpId"] = "EMP002";
        dataRow["EmpName"] = "Sanjay Gupta";
        dt1.Rows.Add(dataRow);
        dt1.AcceptChanges();
        return dt1;
    }

    private DataTable DataTable2()
    {
        DataRow dr = null;
        DataTable dt2 = new DataTable();
        dt2.Columns.Add("EmpId");
        dt2.Columns.Add("Salary");
        dr = dt2.NewRow();
        dr["EmpId"] = "EMP001";
        dr["Salary"] = "50000";
        dt2.Rows.Add(dr);
        dr = dt2.NewRow();
        dr["EmpId"] = "EMP002";
        dr["Salary"] = "45000";
        dt2.Rows.Add(dr);
        dt2.AcceptChanges();
        return dt2;
    }
}

Find nth Occurrence of Character SQL Server

CHARINDEX function provided by Microsoft in SQL Server is used to find the index of a character or substring in the main string, but it only returns the first occurrence.

Oftentimes one needs to find the Nth Occurrence of a character or sub-string. In this post I am sharing a function to achieve the same.

The function accept 3 parameters and returns the position of Nth Occurrence of character or sub-string in the main string.

1. string: input value i.e main string
2. occurrence_val: character or sub-string to be find in the main string
3. occurrence_no: nth occurrence to be find in the string

CREATE FUNCTION dbo.udf_GetNthOccurrence(@string VARCHAR(MAX), @occurrence_val VARCHAR(MAX), @occurrence_no INT)
RETURNS INT AS
BEGIN
    DECLARE @ctr INT, @pos INT, @len INT
    SET @ctr = 0
    SET @pos = 0
    SET @len = DATALENGTH(@occurrence_val)
    WHILE @ctr<@occurrence_no
    BEGIN       
        SET @pos = CHARINDEX(@occurrence_val, @string, @pos) + @len
    IF @pos = @len
        BEGIN
            RETURN -1
        END
        SET @ctr = @ctr+1           
    END
    RETURN @pos - @len
END

Let's use above created function and try to find the nth occurrence in main string

DECLARE @String VARCHAR(MAX), @Occ_No INT, @Occ_String VARCHAR(5)
SET @String = 'Ajay\Vijay\Amit\Sanjay'

SET @Occ_No = 2
SET @Occ_String = '\'
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1) AS Left_Part
        , SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No), LEN(@String)) AS Right_Part
SET @String = 'Ajay\\Vijay\\Amit\\Sanjay'

SET @Occ_No = 3
SET @Occ_String = '\\'
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1) AS Left_Part
        , SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No), LEN(@String)) AS Right_Part

SET @Occ_No = 4
SELECT    dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) As Position
        , CASE  WHEN dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) = -1
                THEN 'Occurent Not found'
                ELSE LEFT(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)-1)
          END AS Left_Part
        , CASE  WHEN dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No) = -1
                THEN 'Occurent Not found'
                ELSE SUBSTRING(@String, dbo.udf_GetNthOccurrence(@String, @Occ_String, @Occ_No)+1, LEN(@String))
          END AS Right_Part

OUTPUT

Required Field Validator for Checkboxlist in ASP.Net

RequiredField Validator does not directly work with CheckBoxList Control.
In this post, I am providing an alternate way to achieve the same with CustomValidator and some JQuery code

<script src="Scripts/jquery-1.4.1.min.js" type="text/javascript"></script>
<script language="javascript" type="text/javascript">
        function ValidateCheckBoxList(sender, args) {
            args.IsValid = false;
            jQuery(".CheckBoxList").find(":checkbox").each(function () {
                if (jQuery(this).attr("checked")) {
                    args.IsValid = true;
                    return;
                }
            });
        }
</script>

<asp:CheckBoxList ID="cblItems" runat="server" RepeatDirection="Horizontal" CssClass="CheckBoxList">
    <asp:ListItem Text="Item 1" Value="1"></asp:ListItem>
    <asp:ListItem Text="Item 2" Value="2"></asp:ListItem>
    <asp:ListItem Text="Item 3" Value="3"></asp:ListItem>
</asp:CheckBoxList>
<asp:CustomValidator ID="cvEventsValidator" Display="Dynamic" ValidationGroup="Submit"
    runat="server" ClientValidationFunction="ValidateCheckBoxList">*</asp:CustomValidator>
<br />
<asp:Button ID="btn" Text="Submit" runat="server" ValidationGroup="Submit" />

Delete data from all Tables in SQL Server

Whenever, the data is deleted or truncated from the tables in SQL Server, the only issue encountered is due to relationships between the tables. To overcome this issue, I have used below approach to delete the data from all the tables form a database in one go in SQL Server.

Steps

1. First identify all the tables having primary keys and store these tables name in a temp table.
2. Truncate all the tables excluding the tables name stored in temp table.
3. Now all the data from the referencing table has been deleted, so now we can delete all the data from the tables having Primary Key without any issue. So, delete or truncate all the tables that have Primary Key stored in temp table

DECLARE @QUERY NVARCHAR(MAX)
--===========================================
-- Getting all tables with primary key
--===========================================
SELECT OBJECT_NAME(ic.OBJECT_ID) as TBLNAME INTO #TEMP
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

--=========================================================
-- Truncate data from all tables without having Primary Key
--=========================================================
SET @QUERY = ''
SELECT    @QUERY = @QUERY + ';TRUNCATE TABLE ' + name
FROM    sysobjects
WHERE    xtype = 'U' AND name not in (SELECT TBLNAME FROM #TEMP)
EXEC sp_executesql @QUERY

--=========================================================
-- Delete data from tables with Primary Keys
--=========================================================
SET @QUERY = ''
SELECT    @QUERY = @QUERY + ';DELETE FROM ' + name
FROM    sysobjects
WHERE xtype = 'U' AND name in (SELECT TBLNAME FROM #TEMP)
EXEC sp_executesql @QUERY
DROP TABLE #TEMP

Check all Checkbox in Gridview

JavaScript function that check or uncheck all the check-boxes of the Grid-view Data-rows depending on the state of check-box of the Grid view Header-row

<script type="text/jscript" language="javascript">
    function SelectAllCheckboxes(chkAll, chkCtrlId) {
        checked = chkAll.checked;
        elm = chkAll.form.elements;
        for (i = 0; i < elm.length; i++)
            if (elm[i].type == "checkbox" && elm[i].id.indexOf(chkCtrlId) != -1) {
                if (elm[i].checked != checked)
                    elm[i].click();
            }
    }
</script>

Add this Template-field to the Grid-view that creates check-boxes in Header-row and Data-rows and also call the above JavaScript function on the click of check-box of the Grid-view Header-row

<asp:TemplateField HeaderText="Select">
    <ItemTemplate>
        <asp:CheckBox ID="chkSelect" runat="server" />
    </ItemTemplate>
    <HeaderTemplate>
        <input id="chkAll" onclick="javascript:SelectAllCheckboxes(this, 'chkSelect');" runat="server"
            type="checkbox" />
    </HeaderTemplate>
</asp:TemplateField>

Calculate Working Hours SQL Server

Calculating work hours is a very frequent need in scheduling, billing, payroll, and time and attendance applications. When the data is fetched from some punching device, you just know the timings of the punches of the employees but don't know whether it is In Time or Out Time.

In this post, I am providing the solution to calculate working hours of the employees assuming that odd entries of an employee on a particular day on Time basis are for In Time and even entries are for Out Time .

In the below example, we have used Row_Number to generate sequential number for each employee on each day to find out odd and even entries for the day.

Refer related post,  Row_Number in SQL to learn about it

DECLARE @TAB TABLE( empid VARCHAR(10), recdate DATE, rectime TIME )

INSERT INTO @TAB
SELECT 'EMP0001', '01/01/2012' ,'9:00 AM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'01:05 PM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'01:33 PM'
UNION ALL SELECT 'EMP0001', '01/01/2012' ,'06:10 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'08:55 AM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'01:02 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'01:35 PM'
UNION ALL SELECT 'EMP0002', '01/01/2012' ,'06:05 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'9:03 AM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'01:08 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'01:42 PM'
UNION ALL SELECT 'EMP0001', '01/02/2012' ,'06:16 PM'

SELECT * FROM @TAB

;WITH CTE AS(
    SELECT    empid, recdate, datepart(hh, rectime) * 60 + datepart(mi, rectime) as mins
            , ROW_NUMBER() OVER ( PARTITION BY empid, recdate ORDER BY rectime) as id
    FROM    @TAB
)

SELECT empid, recdate, mins/60 As Hrs, mins%60 as Mins
FROM (
    SELECT A.empid, A.recdate,  SUM(B.mins - A.mins) as mins
    FROM (
        SELECT * FROM CTE WHERE id%2 = 1
    ) A INNER JOIN (   
        SELECT    * FROM CTE WHERE    id%2 = 0
    ) B ON A.empid = B.empid and A.recdate = B.recdate and A.id + 1 = B.id   
    GROUP BY A.empid, A.recdate
) TAB

RESULT

Validate Alphanumeric Javascript

Validating the input for alphanumeric value only is a very frequent requirement in the applications to ensure special characters are not

In this post, I am sharing a small Javascript Function that ensures that the input can contain alpha or number characters only.

Note: Space or punctuation also are not allowed since those characters are not alpha numeric

On key press the function will validate the character (based on ASCII value) and will not allow you to type if the character is not alphanumeric. If you want to allow any other character then tweak the function accordingly.

<script type="text/javascript">
  function validate(key) {
    var keycode = (key.which) ? key.which : key.keyCode;
    if ((keycode >= 65 && keycode <= 90) || (keycode >= 97 && keycode <= 122) || (keycode >= 48 && keycode <= 57))
      return true;
    else
      return false;
  }
</script>
<asp:TextBox ID="txt1" onkeypress="return validate(event)" runat="server"></asp:TextBox>

Running Total in Sql Server

This post is related with sharing my thoughts on calculating Running Total in Sql Server.

In this post I am sharing how running total can be calculated using recursive Common Table Expression (CTE).

NOTE: Solution would work with SQL Server 2005 onward

Example
Suppose we have Employee Table and we would calculate running total of Salary Column

Let's first create Employee Table and populate some data in it

CREATE TABLE TBLEMPLOYEE ( 
    EMPID        CHAR(6)
    , EMPNAME    VARCHAR(50)
    , SALARY    INT
)
INSERT INTO TBLEMPLOYEEINSERT INTO TBLEMPLOYEE
SELECT 'EMP001', 'SANDEEP MITTAL', 30000
UNION ALL SELECT 'EMP002', 'RITESH KUMAR', 25000
UNION ALL SELECT 'EMP003', 'ABHAY KUMAR', 25000

Now lets write script to calculate Running Total of Salary Column from the Employee Table using recursive CTE.

DECLARE @TAB TABLE(
    EMPID            CHAR(6)
    , EMPNAME        VARCHAR(50)
    , SALARY        INT
    , RUNNING_TOTAL INT
)
INSERT INTO @TAB (EMPID, EMPNAME, SALARY)
SELECT  EMPID, EMPNAME, SALARY
FROM    TBLEMPLOYEE

DECLARE @RUNNING_TOTAL INT = 0
;WITH RUNNING_TOTAL AS (
    SELECT    EMPID, EMPNAME, SALARY, RUNNING_TOTAL
    FROM    @TAB
)
UPDATE RUNNING_TOTAL
SET  @RUNNING_TOTAL = RUNNING_TOTAL = @RUNNING_TOTAL + SALARY

SELECT * FROM @TAB

OUTPUT

Refer related post to calculate cumulative sum using row_number,
Cumulative Sum in SQL Server

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

Difference in Years, Months and Days in C#

In an earlier post, I have shared the function of calculating Date difference in Year(s), Month(s) and Day(s) in SQL Server.

Recently one of my friend had a requirement to calculate the same in ASP.Net, so I created a function to achieve the same in ASP.Net / C# and thought of posting the same on my blog.

Example:
Calculate Age in Years, Months and Days.

Solution:
Function DateDiffInYearMonthDay take two arguments of date datatype and returns the result in text format
Ex - 18 years, 2 months, 3 days

private string DateDiffInYearMonthDay(DateTime fromDate, DateTime toDate)
{
    int Years=0, Months=0, Days=0;
    DateTime newDate;
    newDate = fromDate;
    while (newDate <= toDate)
    {
        Years++;
        newDate = newDate.AddYears(1);
    }
    Years--;
    fromDate = fromDate.AddYears(Years);
    newDate = fromDate;
    while (newDate <= toDate)
    {
        Months++;
        newDate = newDate.AddMonths(1);
    }
    Months--;
    fromDate = fromDate.AddMonths(Months);
    Days = toDate.Subtract(fromDate).Days;
    return Years.ToString() + "years," + Months.ToString() + " months," + Days.ToString() + " days";      
}

Format Function in Sql Server 2012

Finally, Microsoft introduced Format function in SQL Server 2012, that is very handy to format dates, times and currency in different cultures and custom format

In prior versions, we have to create our own function to format number.

Refer related article for prior versions : Format Number in SQL

In this post, I am sharing some examples of how to format date, time, currency and number with format function.

Format Date with culture

DECLARE @DATE DATE = GETDATE()
SELECT   FORMAT(@DATE, 'd', 'en-US') AS [Date (US)]
       , FORMAT(@DATE, 'd', 'en-IN') AS [Date (India)]
       , FORMAT(@DATE, 'd', 'en-GB') AS [Date (Great Britain)]
       , FORMAT(@DATE, 'd', 'de-DE') AS [Date (Denmark)]

RESULT

Format Date with custom format

DECLARE @DATE DATETIME = GETDATE()
SELECT    FORMAT(@DATE, 'dd/MM/yyyy') AS [Date (DD/MM/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy') AS [Date (MM/DD/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy ') AS [Date (MM/DD/YYYY)]
        , FORMAT(@DATE, 'MM/dd/yyyy hh:mm:ss tt') AS [Date (MM/DD/YYYY HH:MM:SS)]
        , FORMAT(@DATE, 'MM/dd/yyyy HH:mm:ss') AS [Date (MM/DD/YYYY H24:MM:SS)]
        , FORMAT(@DATE,'ddd') AS [Week Day]
        , FORMAT(@DATE,'MMM') AS [Short Month Name]
        , FORMAT(@DATE,'MMMM') AS [Full Month Name]
        , FORMAT(@DATE,'yyyy') AS [Year]

RESULT

Format Date with short codes

DECLARE @DATE DATETIME = GETDATE()
SELECT    FORMAT(@DATE,'d') AS [Short date pattern]
        , FORMAT(@DATE,'D') AS [Long Date pattern]       
        , FORMAT(@DATE,'t') AS [Short Time pattern]
        , FORMAT(@DATE,'T') AS [Long Time pattern]

RESULT

Format Currency with culture

DECLARE @Amount MONEY = 210525.52;   
SELECT    FORMAT(@Amount,'c','en-US') [Money (US)]
        , FORMAT(@Amount,'c','en-IN') [Money (India)]
        , FORMAT(@Amount,'c','en-GB') [Money (Great Britain)]
        , FORMAT(@Amount,'c','fr') [Money (France)]
        , FORMAT(@Amount,'c','de-DE') [Money (Denmark)]
        , FORMAT(@Amount,'c','ru-RU') [Money (Russia)]

RESULT

Format Percentage

DECLARE @Per DECIMAL(4,4) = 0.5545;
SELECT    FORMAT(@Per,'p0') [Percentage (Without decimal)]
        , FORMAT(@Per,'p1') [Percentage (With 1 decimal)]
        , FORMAT(@Per,'p2') [Percentage (With 2 decimal)]

RESULT

Format Number

DECLARE @num FLOAT=1234567.89
SELECT    FORMAT(@num,'N') AS 'Format Number Default'
        , FORMAT(@num,'#,#.00') AS 'Format Number with 2 decimal'
        , FORMAT(@num,'#,#.0') AS 'Format Number with 1 decimal'

RESULT

Get Numeric Value from String in SQL Server

In SQL Server, there are in-built functions like CharIndex, PatIndex to find the position of Alpha or Numeric or any special character value from the string. Replace and Stuff functions are there to replace some part of the string. But there are no-inbuilt functions to get only Numeric, Alpha or Alpha Numeric values from the string. In this Post, I am sharing User Defined Functions to get these using the in-built functions in SQL Server.

Function to get Only Numeric Values from the string

CREATE FUNCTION dbo.NumericOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^0-9]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.NumericOnly('abcd!@#ABCD#$%123%^%^')

Output

Function to get Only Alpha Values from the string

CREATE FUNCTION dbo.AlphaOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^a-zA-Z]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.AlphaOnly('abcd!@#ABCD#$%123%^%^')

Output

Function to get Only AlphaNumeric Values from the string

CREATE FUNCTION dbo.AlphaNumericOnly(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX) AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^a-zA-Z0-9]%', @string)
    WHILE @intAlpha > 0
    BEGIN
        SET @string = STUFF(@string, @intAlpha, 1, '' )
        SET @intAlpha = PATINDEX('%[^a-zA-Z0-9]%', @string)
    END
    RETURN     @string
END
GO
SELECT dbo.AlphaNumericOnly('abcd!@#ABCD#$%123%^%^')

Output

First character of string in SQL Server

In this post, we will see how to to fetch the first character of each word from the string and concatenate and return the new string

Example:

String: "How Are You?"

There are 3 Words in the string H-How, A-Are, Y=You? and you want to return HAY.

Just pass How Are You? to the function, it would return HAY.

CREATE FUNCTION dbo.FirstCharFromString(
    @string VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
AS
BEGIN
  DECLARE @XML XML, @RESULT VARCHAR(MAX)
  SET @RESULT = ''
  SELECT  @XML = cast('<d>' + replace(@string, ' ', '</d><d>') + '</d>' as xml)
  SELECT  @RESULT = @RESULT + LEFT(T.split.value('.', 'NVARCHAR(MAX)'), 1)
  FROM    @XML.nodes('/d') T (split)
  RETURN  @RESULT
END

Now lets execute the function and check the result

SELECT dbo.FirstCharFromString('How Are You?')

OUTPUT

Find Nth Highest Salary in SQL Server

"Find nth Highest Salary?" is a very common question being asked in interviews.

There are no. of ways to find the nth highest value from the table.

In this post, I am sharing some of the ways to find out the same.

Lets first create a Salary table and populate some data in it.

create table tblSalary (salary INT)
insert into tblSalary
select 5000 union all
select 5000 union all
select 9000 union all
select 8000 union all
select 7000 union all
select 7000 union all
select 8000 union all
select 3000

In the table, highest value is 9000 and 2nd highest value is 8000 which is duplicate so 3rd distinct highest value is 7000.

Now lets find out 3rd highest salary with different ways

declare @nHighest int
set @nHighest = 3 --Set value here to get nth highest value

-- Method 1
select  min(salary) as salary
from (
  select  distinct top (@nHighest) salary as salary
  from  tblSalary order by salary desc
) tab

-- Method 2
select  top 1 (salary)
from (
  select  distinct top (@nHighest) salary as salary
  from  tblSalary order by salary desc
) tab order by salary

-- Method 3
select  distinct (a.salary)
from    tblSalary a
where    @nHighest = (select count (distinct (b.salary))
from    tblSalary b where a.salary<=b.salary)

-- Method 4
select  distinct salary
from (
  select  dense_rank() over (order by salary desc) as rnk, salary
  from  tblSalary
) tab where rnk = @nHighest

OUTPUT

New Date and Time Functions in SQL Server 2012

SQL Server 2012, code named Denali, introduced some new DATE and TIME Functions. In this post, we will discuss on how these new functions are used.

Here is the list of New DATE and TIME Functions

• DATEFROMPARTS
• TIMEFROMPARTS
• DATETIMEFROMPARTS
• DATETIME2FROMPARTS
• SMALLDATETIMEFROMPARTS
• DATETIMEOFFSETFROMPARTS
• EOMONTH

DATEFROMPARTS 
The DATEFROMPARTS function, returns a date value with the date part set to the specified year, specified month and the specified day, and the time portion set to the default as shown in the below query result.

DECLARE @YEAR  INT = 2012,
        @MONTH INT = 1,
        @DAY   INT = 1

SELECT DATEFROMPARTS (@YEAR, @MONTH, @DAY) AS [Result]

OUTPUT

TIMEFROMPARTS
The TIMEFROMPARTS function, returns a full time value as shown in the below query result.
It is important to note that the fractions argument actually depends on the precision argument.

For example:

• When fractions have a value of 5 and precision has a value of 1, then the value of fractions represents 5/10 of a second.
• When fractions have a value of 50 and precision has a value of 2, then the value of fractions represents 50/100 of a second.
• When fractions have a value of 500 and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

DECLARE @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59
SELECT TIMEFROMPARTS (@HOUR, @MINUTE, @SECONDS, 500, 3) AS [Result]

OUTPUT

DATETIMEFROMPARTS
The DATETIMEFROMPARTS function,  returns a full datetime value as shown in the below query result.

DECLARE @YEAR         INT = 2012,
        @MONTH        INT = 1,
        @DAY          INT = 9,
        @HOUR         INT = 11,
        @MINUTE       INT = 59,
        @SECONDS      INT = 59,
        @MILLISECONDS INT = 0
SELECT DATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, @MILLISECONDS) AS [Result]

OUTPUT

DATETIME2FROMPARTS
The DATETIME2FROMPARTS function, returns a full datetime2 value as shown in the below query result.

DECLARE @YEAR    INT = 2012,
        @MONTH   INT = 1,
        @DAY     INT = 1,
        @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59
SELECT DATETIME2FROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 3) AS [Result]

OUTPUT

SMALLDATETIMEFROMPARTS
The SMALLDATETIMEFROMPARTS function, which is available in SQL Server 2012, returns a full smalldatetime value as shown in the below query result.

DECLARE @YEAR   INT = 2012,
        @MONTH  INT = 1,
        @DAY    INT = 1,
        @HOUR   INT = 11,
        @MINUTE INT = 59
SELECT SMALLDATETIMEFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE) AS [Result]

OUTPUT

DATETIMEOFFSETFROMPARTS
The DATETIMEOFFSETFROMPARTS function, returns a full datetimeoffset data type as shown in the below query result. The OFFSET argument is basically used to represent the time zone offset value hour and minutes.

DECLARE @YEAR    INT = 2012,
        @MONTH   INT = 1,
        @DAY     INT = 1,
        @HOUR    INT = 11,
        @MINUTE  INT = 59,
        @SECONDS INT = 59
SELECT DATETIMEOFFSETFROMPARTS (@YEAR, @MONTH, @DAY, @HOUR, @MINUTE, @SECONDS, 500, 5, 30, 3) AS [Result]

OUTPUT

EOMONTH
The EOMONTH function, calculates the last date of the month based on the date which is passed as an input parameter.

DECLARE @STARTDATE DATETIME = GETDATE()
SELECT EOMONTH (@STARTDATE) AS [Last Date of Month]

OUTPUT