Get Tesla Stock worth ₹1000. Enter code UMU2BOEITSL while signing up 🥳
Click to claim today 👉🏼
On Mobile https://indmoney.onelink.me/rmhc/pszcd5o0
On Web https://www.indmoney.com

Dec 4, 2011

Catch Browser Close Event Javascript

In this post, I am sharing the code to catch the browser close event and write code to perform some action when the browser is closed.

Ajax is used to request the webpage when the browser is closed.

Default1.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default1.aspx.cs" Inherits="Default1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
    <script language="javascript" type="text/javascript">
        function window.onbeforeunload() {
            $.ajax({
                type: "POST",
                url: "exit.aspx/OnClose",
                data: "{}",
                contentType: "application/json; charset=utf-8",
                dataType: "json",
                success: function (msg) {
                    // Do something interesting here.
                }
            });
        }
    </script>
</head>
<body>
    <form id="form1" runat="server">
    </form>
</body>
</html>
Exit.aspx.cs
using System;
using System.Web.Services;
public partial class exit : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    } 

    [WebMethod]
    public static void OnClose()
    {
        //write code here
    }
}

Nov 30, 2011

Generate Random Number for each row in SQL Server

In this post, I am sharing how  to generate random values for each row in a result set in SQL Server

Idea here is to generate another column in SELECT statement that gives you random values for each row each time the query is executed.

Let's create a Table and populate some data in it.
CREATE TABLE TAB (
    ID VARCHAR(10), NAME VARCHAR(10)
)
GO
INSERT INTO TAB
SELECT 1, 'Anil' UNION ALL
SELECT 2, 'Sunil' UNION ALL
SELECT 3, 'Amit' UNION ALL
SELECT 4, 'Sumit' UNION ALL
SELECT 5, 'Rajesh'

In SQL Server, there is in-built function RAND() to generate random values, but when it is used in SELECT statement, it generates a static value for each row, so this is not going to help us in this scenario. However, NEWID() generates a random value for each row. Let's execute a script to see the difference

SELECT  RAND() AS RAND, NEWID() AS NEWID, ID, NAME
FROM    TAB
OUTPUT

Check the result, you will find RAND() function is giving static value for each row but NEWID() is giving random values, so NEWID() is going to help us in getting random row numbers. Now, Lets see how we can use NEWID() to generate the random values

SELECT  ABS(CHECKSUM(NEWID())%100) AS NEWID, ID, NAME
FROM    TAB
OUTPUT

Check the above result, here is a new column NEWID in which there is random values for each rows. Try running the script multiple times, you will find that this will generate different values each time for each row

Nov 23, 2011

SQL Server Column Level Encryption Example using Symmetric Keys

In this article, I have posted step by step procedure to apply encryption on columns in SQL Server using symmetric keys.

Step 1 - Create a sample SQL Server table
Let’s use an example where we create the dbo.Customer_data table which contains credit card details for customers. Our task is to protect this data by encrypting the column, which contains the credit card number. I will populate it will some sample data as shown below.
CREATE TABLE dbo.Customer_data(
      Customer_id            INT CONSTRAINT Pkey3 PRIMARY KEY NOT NULL
    , Customer_Name            VARCHAR(100) NOT NULL
    , Credit_card_number    VARCHAR(25) NOT NULL
)
INSERT INTO dbo.Customer_data
SELECT 1,'Cust1','1111-2222-3333' UNION ALL
SELECT 2,'Cust2','1452-2563-1526' UNION ALL
SELECT 3,'Cust3','2147-4526-4587'

SELECT * FROM dbo.Customer_data
OUTPUT

Step 2 - SQL Server Service Master Key
The Service Master Key is the root of the SQL Server encryption hierarchy. It is created during the instance creation. Confirm it's existence using the query below. If it does not exist we need to manually create it.
USE master
GO
SELECT  *
FROM    sys.symmetric_keys
WHERE   name = '##MS_ServiceMasterKey##'

Step 3 - SQL Server Database Master Key
The next step is to create a database master key. This is accomplished using the CREATE MASTER KEY method. The "encrypt by password" argument is required and defines the password used to encrypt the key. The DMK does not directly encrypt data, but provides the ability to create keys that are used for data  encryption. It is important that you keep the encryption password in a safe place and/or keep backups of your SQL Server Database Master Key.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pwd@123'

Step 4 - Create a Self Signed SQL Server Certificate
The next step is to create a self-signed certificate that is protected by the database master key. A certificate is a digitally signed security object that contains a public (and optionally a private) key for SQL Server. An optional argument when creating a certificate is ENCRYPTION BY PASSWORD. This argument defines a password protection method of the certificate's private key. In our creation of the certificate we have chosen to not include this argument; by doing so we are specifying that the certificate is to be protected by the database master key.
CREATE CERTIFICATE Certificate1 WITH SUBJECT = 'Secure Data'

Step 5 - SQL Server Symmetric Key
A symmetric key is one key that is used for both encryption and decryption. Encryption and decryption by using a symmetric key is fast, and suitable for routine use with sensitive data in the database

CREATE SYMMETRIC KEY SymmetricKey1
WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE Certificate1

Step 6 - Schema changes
An Encrypted column can only be of datatype varbinary and since the column we want to encrypt is of datatype varchar, we have to create a new column and populate it with encrypted values.
ALTER TABLE Customer_data
ADD Credit_card_number_encrypt VARBINARY(MAX) NULL

Step 7 - Encrypting the newly created column
To encrypt the data we will use the EncryptByKey Command. Below is a sample code which can be used to encrypt the data. Please note that symmetric key needs to opened before we can encrypt data and be sure you manually close the key else it will remain open for the current session.
OPEN SYMMETRIC KEY SymmetricKey1
DECRYPTION BY CERTIFICATE Certificate1;
GO
UPDATE  Customer_data
SET     Credit_card_number_encrypt = EncryptByKey (Key_GUID('SymmetricKey1'),Credit_card_number)
FROM    dbo.Customer_data;
GO
CLOSE SYMMETRIC KEY SymmetricKey1;
OUTPUT

Step 8 - Remove old column
To finalize this process, let's remove the old column so that the table is left only with the encrypted data.
ALTER TABLE Customer_data DROP COLUMN Credit_card_number;

Step 9 - Reading the SQL Server Encrypted Data
Let's take a look at an example of reading data using the decrypt bykey option. As we indicated before, make sure you open and closesymmetric key as shown earlier.
OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1;
GO
SELECT  Customer_id, Credit_card_number_encrypt AS 'Encrypted Credit Card Number'
        , CONVERT(VARCHAR, DecryptByKey(Credit_card_number_encrypt)) AS 'Decrypted Credit Card Number'
FROM    dbo.Customer_data;
CLOSE SYMMETRIC KEY SymmetricKey1;
OUTPUT

Step 10 - Adding Records to the Table
Below is the sample code to insert values into the newly created encrypted column.
OPEN SYMMETRIC KEY SymmetricKey1 DECRYPTION BY CERTIFICATE Certificate1;
INSERT INTO dbo.Customer_data (Customer_id, Customer_Name, Credit_card_number_encrypt)
VALUES (4, 'Cust4', EncryptByKey( Key_GUID('SymmetricKey1'), CONVERT(varchar,'2539-2222-1245')))
OUTPUT

Nov 18, 2011

Dynamic Pivot in SQL Server

Pivot in SQL Server is used to transform row values of a column as the columns of the output.

What is the need of dynamic pivot?
When the row values of the column on which you are going to pivot are static i.e you already know what all set of values exist in the column, then static pivot would work for you. The need of dynamic pivot arises when the row values for the column are not static i.e you do not know what all values would be there in the column

Example
CREATE TABLE #tblitems(
  item    VARCHAR(10),
  parameter  VARCHAR(10),
  value    INT
)

INSERT INTO #tblitems
SELECT 'item1', 'param1', 10 UNION ALL
SELECT 'item1', 'param2', 20 UNION ALL
SELECT 'item1', 'param3', 30 UNION ALL
SELECT 'item2', 'param1', 15 UNION ALL
SELECT 'item2', 'param2', 20

-- Creating a list of all distinct row values that would be going to become columns
DECLARE @paramList VARCHAR(MAX)
SET @paramList = STUFF((
                     SELECT DISTINCT ',[' + parameter + ']'
                     FROM #tblitems FOR XML PATH('')
                    )
                 ,1,1,'')
PRINT @paramList
-- OUTPUT : [param1],[param2],[param3]

DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT item, ' + @paramList + ' FROM( SELECT * FROM #tblitems )src
PIVOT(SUM(value) FOR parameter IN (' + @paramList + ')) pvt'

EXEC sp_executesql @query
DROP TABLE #tblitems
OUTPUT

Oct 15, 2011

Conditional Unique Constraint in SQL Server

This post will explain you the way, how conditional unique constraint can be applied in SQL Server.

Example
Suppose you have a table that holds Student Id, Test Id, Pass Status (yes/no) and date.
Now, you want to make sure no student can pass a test twice, means you can have multiple entries for a student and a test with 'no' value in passed column, but only 1 value with  'yes' in passed.

Step 1: Let's first create a Test Table
CREATE TABLE tblTest (
    studentid INT, testid INT, passed VARCHAR(3), date DATE
)
Step 2: Now lets create a function that will return the count of pass records against a Test for a Student
CREATE FUNCTION CheckCount(
    @studentid INT, @testid INT)
RETURNS INT
AS
BEGIN
    DECLARE @ret INT;
    SELECT @ret = COUNT (1) FROM tblTest WHERE studentid = @studentid AND testid = @testid AND passed = 'yes';
    RETURN @ret;
END;
Step 3: Now lets add a constraint to the table Test using the CheckCount function created above to ensure that if count of pass records against a Test for a students exceeds 1, the constraint should not allow to insert  the
ALTER TABLE tblTest
ADD CONSTRAINT ChkConstratint
CHECK (NOT (dbo.CheckCount(studentid,testid) > 1));

Step 4: Now lets populate the data in the table to test the constraint
INSERT INTO tblTest VALUES (1, 1, 'no', getdate()); --success
INSERT INTO tblTest VALUES (1, 1, 'no', getdate()); --success
INSERT INTO tblTest VALUES (1, 1, 'yes', getdate()); --success
INSERT INTO tblTest VALUES (1, 1, 'yes', getdate()); --fail (duplicate studentid and testid with passed = 'yes' 
OUTPUT

Jun 7, 2011

Date Difference in Years, Months and Days in SQL Server

Recently, I had a requirement where I had to calculate the difference in Year(s), Months(s) and Day(s) between the two dates. Unfortunately there is no in-function in SQL Server to calculate the same. So, I wrote a user-defined function for this and thought of sharing the same on my blog. So, in this post, we will see how to calculate Date difference in Years, Months and dates between two dates.

SQL Server has in-built function DATEDIFF to find the difference between two dates, but, it works only on DATEPART provided in the first parameter of the function.

Let's take a look how it works
DECLARE @date1 DATETIME, @date2 DATETIME
SET @date1='12/31/2010'
SET @date2='01/01/2011'
SELECT    datediff(YEAR,@date1,@date2) as years
        , datediff(MONTH,@date1,@date2) as months
        , datediff(DAY,@date1,@date2) as days
OUTPUT

The actual difference between the two dates in only 1 day but look at the output it returns 1 year, 1 month and 1 day, which is incorrect as per our requirement. As per our requirement, it should be 0 year, 0 month and 1 day.

As a solution to above, I have created a user defined function to find the exact date difference in Year(s), Month(s) and Day(s) between two dates.
CREATE FUNCTION [dbo].[udfDateDiffinYrMonDay] (@datefrom DATETIME, @dateto DATETIME)
RETURNS VARCHAR(MAX)
AS
BEGIN
    DECLARE @Years INT, @Months INT, @Days INT
    SET @Years = DATEDIFF(YEAR, @datefrom, @dateto)
    IF DATEADD(YY, @Years, @datefrom) > @dateto
    BEGIN
        SET @Years = @Years - 1
    END

    SET @datefrom = DATEADD(YY, @Years, @datefrom)
    SET @Months = DATEDIFF(MM, @datefrom, @dateto)

    IF DATEADD(MM, @Months, @datefrom) > @dateto
    BEGIN
        SET @Months = @Months - 1
    END

    SET @datefrom = DATEADD(MM, @Months, @datefrom)
    SET @Days = DATEDIFF(DD, @datefrom, @dateto)

    RETURN CAST(@Years AS VARCHAR) + ' Years '
            + CAST(@Months AS VARCHAR) + ' Months '
            + CAST(@Days AS VARCHAR) + ' Days'
END

Let's test the above created function
SELECT dbo.udfDateDiffinYrMonDay('12/31/2010','01/01/2011')
OUTPUT
SELECT dbo.udfDateDiffinYrMonDay('06/08/2006','01/01/2011')
OUTPUT

Related post: calculate date difference in Year(s), Month(s) and Day(s) in C#

May 25, 2011

Delete Duplicate Records in Sql Server 2005

In this post, we will learn how to delete duplicate records in SQL Server using CTE.

In the earlier version of SQL Server, if you need to delete duplicate records you had to the following steps
  • Store the distinct of duplicate records in some temp tables
  • Delete the duplicate records from the main table
  • Move the temp table data to main table
But, from SQL Sever 2005 onward, it becomes easy and more efficient to delete duplicate records from the table using CTE and Row_Number.

NOTE: CTE was introduced in SQL Server 2005, so the solution would work on SQL Server 2005 onward

Let's first create a table
CREATE TABLE tblduplicate(
    id       INT,
    VALUE    VARCHAR(10)
)

Now let's populate the table with duplicates records
insert into tblduplicate
select 1, 'value 1' union all
select 1, 'value 1' union all
select 1, 'value 2' union all
select 2, 'value 2' union all
select 2, 'value 2' union all
select 3, 'value 3'

Now let's check the data in the table
select * from tblduplicate
OUTPUT

Now lets write script to delete duplicates records from the table using CTE. In the solution first we would generate a sequential no. for duplicate records using Row_Number.

Suggested article to understand Row_Number in detail: Row_Number in SQL Server

Once row number is generated we would delete all the records where row number is greater than 1 as there are duplicate records.
;with cte AS (
    SELECT  id, VALUE
            , Row_number() OVER (PARTITION BY id, VALUE ORDER BY id) AS rowno
    FROM    tblduplicate
)
DELETE cte WHERE rowno > 1
Now let's again check the data in the table
select * from tblduplicate
OUTPUT

Check the output. Duplicate records are deleted from the table

Apr 22, 2011

Columns to Rows in SQL Server

In this article, you will learn how to convert columns to rows in SQL Server.

We will use unpivot feature for the transformation. Lets' take an example

NOTE: PIVOT/UNPIVOT feature was introduced in SQL Server 2005, so solution will work in SQL Server 2005 onward only.

Let's create a table for Key Value pair and populate some data in it
CREATE TABLE tblkeyvalpair(
    KEY1 INT, KEY2 INT, KEY3 INT, KEY4 INT, KEY5 INT
)
INSERT INTO tblkeyvalpair VALUES (10, 20, 30, 40, 50)

SELECT * FROM tblkeyvalpair
OUTPUT

Here, the keys and values are stored in column form, and our requirement is to display the key and value pair in row form.

Below is the code snippet to achieve the same using unpivot feature
SELECT KeyName, Value
FROM   (
    SELECT * FROM   tblkeyvalpair
) AS t1 
UNPIVOT (
    Value FOR KeyName IN (key1, key2, key3, key4, key5)
) AS t2
If we know all key columns then we can hard code the values as we do in the above solution

But now assume we don't all possible values, in that case we will have to dynamically construct the list of keys and pass it dynamically to the query.
DECLARE @ColumnList VARCHAR(MAX),
        @Query      VARCHAR(MAX),
        @ColumnName VARCHAR(100),
        @TableName  NVARCHAR(100)

SET @TableName = 'tblkeyvalpair'

SELECT @ColumnList = Stuff((SELECT ',' + name
                            FROM   syscolumns
                            WHERE  id = Object_id(@TableName)
                            FOR XML PATH(''))
                     ,1,1,'')

SET @Query = 'SELECT keyname, value FROM(SELECT * FROM ' + @TableName + ') AS T1'
SET @Query = @Query + ' UNPIVOT (value FOR keyname IN (' + @ColumnList + ')) AS T2'

EXEC(@Query)
OUTPUT

Apr 17, 2011

Display Comma Separated Value Row Wise in SQL Server

In this post, we will learn how to display comma separated value row wise in SQL Server

Example-1: Comma separated values of a variable to rows
In the first Example we will take a variable which holds some values with comma separator and we will display each value in a row.

Step 1: Replace comma with tag from comma separated value list to make it as XML
Step 2: Read each node from XML and display value in rows

DECLARE @xml XML, @s NVARCHAR(MAX)
SELECT  @s = N'a,b,c,d,e'
SET     @xml = N'<t>' + REPLACE(@s,',','</t><t>') + '</t>'
SELECT  t.value('.','varchar(5)') as [delimited items]
FROM    @xml.nodes('/t') as a(t)
OUTPUT

Example-2: Comma separated values of a column to rows
In this example, we have two columns Col1 & Col2 in a table and Col2 stores comma separated values and our requirement is to display each value of Col2 in row form against Col1

Step 1: Replace comma with tag from comma separated value list to make it as XML
Step 2: Reading each node from the XML
Step 3: Cross Apply to generated each node value as row.

Suggested article to understand Cross Apply: APPLY Operator in SQL Server

DECLARE @TAB TABLE (col1 varchar(10), col2 varchar(10))
INSERT INTO @TAB
SELECT 'A' as col1, '1,2,3' as col2
UNION
SELECT 'B' as col1, '4,5,6' as col2

SELECT col1, Tags.val.value('.', 'VARCHAR(MAX)') AS col2
FROM(
    SELECT  col1, CAST('<t>' + REPLACE(col2, ',', '</t><t>') + '</t>' AS XML) AS TAG
    FROM    @TAB
) T CROSS APPLY TAG.nodes('/t') as Tags(val)
OUTPUT

Now let's transform the above code into a table valued function to make it reusable and flexible.

CREATE FUNCTION 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

Now, In the above table valued function we have to just pass delimited string and delimiter and it will return you a table with two columns id (sequential number) and val (all delimited value).
Delimiter could be any : Comma, Colon, Semi-Colon etc.

Suggested article to understand split function in detail: Split Function in SQL

Now, let's execute the table valued function with different delimiters

SELECT * FROM dbo.Split(N'a,b,c', ',')
SELECT * FROM dbo.Split(N'p:q:r', ':')
OUTPUT

Mar 30, 2011

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.


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

Mar 29, 2011

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

Mar 26, 2011

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

Mar 11, 2011

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
Cumulative Sum 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

Jan 11, 2011

Custom Paging in GridView in ASP.Net

In this article, you will learn what is Custom Paging and how it is implemented in a Grid-view.

First of all, I would like to share the benefit of using Custom Paging over Normal Paging.

The purpose for using Custom Paging over Normal Paging is performance. The key point in Custom Paging is that it only retrieves "n" no. of records from the database that are to be displayed on the page.

Suppose, there are 100K records in the database and you want to display 10 records on a page. In Normal Paging on each page change, all 100K records are retrieved from the database or is maintained in the viewstate, but in Custom Paging we implement it in such a way that it retrieves only 10 records from database on each page change.

Let's have an example code to show how custom paging is implemented

Suppose we have a Employee Table and we are going to bind employee details to Grid-view.
The code has below listed functionalities for navigation of pages
  • First, Previous, Next & Last Buttons to navigate between pages
  • Link Buttons to navigate between pages
First create a Table and populate some data in it.
CREATE TABLE dbo.tblEmployee(
    EmpID    char(8),
    EmpName varchar(50),
    DOB        datetime,
    DOJ        datetime,
    Gender    char(1)
)
INSERT INTO tblEmployee
SELECT    'EMP' + RIGHT('00000' + cast(number as varchar),4)
        , 'EMP' + RIGHT('00000' + cast(number as varchar),4)       
        , CONVERT(VARCHAR, DATEADD(D, (-10000 + ABS(CHECKSUM(NEWID()))%1000), GETDATE()),101)
        , CONVERT(VARCHAR, DATEADD(D, (-1000 + ABS(CHECKSUM(NEWID()))%1000), GETDATE()),101)
        , CASE WHEN ABS(CHECKSUM(NEWID()))%100 > 50 THEN 'M' ELSE 'F' END       
FROM    master..spt_values
WHERE   type='P'
AND     number between 1 and 35
Now let's create a stored procedure that would retrieve only 10 records on records based on the selected page. We have used Row_Number (window function) to generate a sequential no. to the result and based on the which we would fetch the records.

Stored Procedure returns employee details based on current page
CREATE PROCEDURE [dbo].[uspGetEmployees] (
    @iPageNo INT
    , @iPageRecords INT
)
AS
BEGIN   
    DECLARE @ROWCNT INT, @PAGECNT INT
    SELECT EmpID, EmpName, DOB, DOJ, Gender
    FROM (
        SELECT  ROW_NUMBER() OVER(ORDER BY EmpID)  AS RowNum
                , EmpID, EmpName
                , CONVERT(VARCHAR, DOB, 101) AS DOB
                , CONVERT(VARCHAR, DOJ, 101) AS DOJ
                , CASE Gender WHEN 'M' THEN 'Male' ELSE 'Female' END as Gender
        FROM    tblEmployee
    ) TAB WHERE RowNum BETWEEN @iPageRecords * (@iPageNo - 1 ) + 1 AND @iPageNo * @iPageRecords
 
    SELECT @ROWCNT = COUNT(1) FROM tblEmployee
    SELECT @ROWCNT/@iPageRecords + CASE WHEN @ROWCNT%@iPageRecords = 0 THEN 0 ELSE 1 END
END
Now let's add a Page to the ASP.Net application

CustomPaging.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="CustomPaging.aspx.cs" Inherits="CustomPaging" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title>Custom Paging</title>
    <style type="text/css">
        tr{
            background-color: ButtonFace;
        }
        td{
            font-family:Verdana;
            font-size:12px;
        }
    </style>
</head>

<body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="ScriptManager1" runat="server"></asp:ScriptManager>
    <asp:UpdatePanel ID="UpdatePanel1" runat="server">
        <ContentTemplate>
            <table style="width: 100%; border-color: Gray;">
                <tr align="center">
                    <td colspan="2">
                        Employees Details
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
                            ForeColor="#333333" GridLines="Horizontal" Width="100%">
                            <AlternatingRowStyle BackColor="White" ForeColor="#284775" />
                            <Columns>
                                <asp:BoundField HeaderText="Employee ID" DataField="EmpID" />
                                <asp:BoundField HeaderText="Employee Name" DataField="EmpName" />
                                <asp:BoundField HeaderText="Date of Birth" DataField="DOB" />
                                <asp:BoundField HeaderText="Date of Joining" DataField="DOJ" />
                                <asp:BoundField HeaderText="Gender" DataField="Gender" />
                            </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>
                    </td>
                </tr>
                <tr>
                    <td>
                        <asp:Button ID="btnFirst" runat="server" Text="<<" OnClick="btnFirst_Click"/>
                        <asp:Button ID="btnPrev" runat="server" Text="<" OnClick="btnPrev_Click" />
                        Page
                        <asp:TextBox ID="txtPageNo" runat="server" Text="1" Width="45px"></asp:TextBox>
                        of
                        <asp:Label ID="lblPages" runat="server" Text="1"></asp:Label>
                        <asp:Button ID="btnNext" runat="server" Text=">" OnClick="btnNext_Click" />
                        <asp:Button ID="btnLast" runat="server" Text=">>" OnClick="btnLast_Click"/>
                        <asp:RequiredFieldValidator ID="rfvPageNo" runat="server" ControlToValidate="txtPageNo"
                            ErrorMessage="*" ValidationGroup="grpGo">
                        </asp:RequiredFieldValidator>
                        <asp:RangeValidator ID="rvPageNo" runat="server" ControlToValidate="txtPageNo" Type="Integer"
                            MinimumValue="1" MaximumValue="1" ValidationGroup="grpGo"></asp:RangeValidator>                       
                    </td>
                    <td align="right">
                        <asp:Label ID="lblStatus" runat="server"> Displaying 1 of 1</asp:Label>
                    </td>
                </tr>
                <tr>
                    <td colspan="2">
                        <asp:Panel ID="pnl" runat="server"></asp:Panel>
                    </td>
                </tr>
            </table>
        </ContentTemplate>
    </asp:UpdatePanel>
    </form>
</body>
</html>
CustomPaging.aspx.cs
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web.UI.WebControls;

public partial class CustomPaging : System.Web.UI.Page
{
    enum PageNav { First, Previous, Next, Last, None}
    private int iPageRecords;

    #region Events
    protected void Page_Load(object sender, EventArgs e)
    {
        iPageRecords = 10;
        if (!IsPostBack)
        {
            BindGrid(1, iPageRecords);
        }
        generateLinkBtns();
    }

    protected void lnkButton_Click(object sender, EventArgs e)
    {
        ViewState["currentPage"] = ((LinkButton)sender).Text;
        BindGrid(Convert.ToInt32(ViewState["currentPage"]), iPageRecords);
        txtPageNo.Text = ViewState["currentPage"].ToString();
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.None);
    } 

    protected void btnFirst_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.First);
    }

    protected void btnPrev_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Previous);
    }

    protected void btnNext_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Next);
    }

    protected void btnLast_Click(object sender, EventArgs e)
    {
        PageChange(Convert.ToInt32(ViewState["currentPage"]), PageNav.Last);
    }
    #endregion

    #region Methods
    string GetConnectionString()
    {
        //Mofify connection string       
        return "Data Source=SERVERNAME;Initial Catalog=DBNAME;User Id=UserName;Password=Pwd; Connect Timeout=0";       
    }

    private void generateLinkBtns()
    {
        LinkButton lnkButton = null;
        Label lbl = null;
        for (int i = 1; i <= Convert.ToInt32(ViewState["PageCount"]); i++)
        {
            lnkButton = new LinkButton();
            lnkButton.Click += new EventHandler(lnkButton_Click);
            lnkButton.ID = "lnk" + i.ToString();
            lnkButton.Text = i.ToString();
            pnl.Controls.Add(lnkButton);
            lbl = new Label();
            lbl.Text = " ";
            pnl.Controls.Add(lbl);
        }
    }

    private void BindGrid(int iPageNo, int iPageRecords)
    {
        DataSet ds = new DataSet();
        ds = getEmployees(iPageNo, iPageRecords);
        GridView1.DataSource = ds.Tables[0];
        GridView1.DataBind();
        if (!IsPostBack)
        {           
            int iPageCount;
            iPageCount = Convert.ToInt32(ds.Tables[1].Rows[0][0]);           
            rvPageNo.MaximumValue = iPageCount.ToString();
            ViewState["PageCount"] = iPageCount;           
            btnFirst.Enabled = false;
            btnPrev.Enabled = false;
            ViewState["currentPage"] = "1";
        }          

        txtPageNo.Text = iPageNo.ToString();
        lblPages.Text = ViewState["PageCount"].ToString();
        lblStatus.Text = "Displaying Page : " + iPageNo.ToString() + " of " + ViewState["PageCount"].ToString();
    }

    public DataSet getEmployees(int iPageNo, int iRecords)
    {
        SqlParameter[] param;
        param = new SqlParameter[2];
        param[0] = new SqlParameter("@iPageNo", iPageNo);
        param[1] = new SqlParameter("@iPageRecords", iRecords);
        SqlConnection conn = new SqlConnection(GetConnectionString());
        conn.Open();
        SqlCommand cmd = new SqlCommand("uspGetEmployees", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddRange(param);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        da.Fill(ds);
        conn.Close();
        conn.Dispose();
        return ds;
    }

    private void PageChange(int currentPage, PageNav pg)
    {
        int pageCount;
        pageCount = Convert.ToInt32(ViewState["PageCount"]);
        btnFirst.Enabled = true;
        btnPrev.Enabled = true;
        btnNext.Enabled = true;
        btnLast.Enabled = true;       
        switch (pg)
        {
            case PageNav.First:
                currentPage = 1;
                btnFirst.Enabled = false;
                btnPrev.Enabled = false;
                break;
            case PageNav.Previous:
                if (currentPage == 2)
                {
                    btnFirst.Enabled = false;
                    btnPrev.Enabled = false;
                }
                currentPage--;
                break;
            case PageNav.Next:
                if (currentPage == pageCount - 1)
                {
                    btnNext.Enabled = false;
                    btnLast.Enabled = false;
                }
                currentPage++;
                break;
            case PageNav.Last:
                btnNext.Enabled = false;
                btnLast.Enabled = false;
                currentPage = Convert.ToInt32(ViewState["PageCount"]);
                break;
            case PageNav.None:
                if (currentPage == 1)
                {
                    btnFirst.Enabled = false;
                    btnPrev.Enabled = false;
                }
                else if (currentPage == pageCount)
                {
                    btnNext.Enabled = false;
                    btnLast.Enabled = false;
                }
                break;
        }
        BindGrid(currentPage, iPageRecords);
        ViewState["currentPage"] = currentPage;
    }
    #endregion
}
OUTPUT
Custom Paging Output

Jan 4, 2011

Pivot with Dynamic Columns in SQL Server

In this post, you will learn about dynamic pivot in SQL Server and where it is required with an example.

Pivot is basically transpose used to convert unique row values of a column as columns of the resultset.

If we already know all unique values of the column and are static then we can use static pivot where we can pass the unique values in the query, but if we are not sure of all the unique values we will require dynamic pivot where we will have to dynamically create a list of values to pass for pivot.

Let's take an example of dynamic pivot

Lets create a sales table and populate some data

CREATE TABLE tblSales (
      CustCode  VARCHAR(50)
    , InvAmt    INT
    , InvDate    DATETIME
)

INSERT INTO tblSales
SELECT 'C0001', 1500, '10/01/2010' UNION ALL
SELECT 'C0002', 2000, '10/01/2010' UNION ALL
SELECT 'C0002', 1100, '10/25/2010' UNION ALL
SELECT 'C0001', 1200, '10/20/2010' UNION ALL
SELECT 'C0001', 1600, '11/05/2010' UNION ALL
SELECT 'C0002', 1300, '11/17/2010'

SELECT * from tblSales
OUTPUT
Sales Table Data

Now, suppose we want to use pivot to display Invoice Amount for each Customer code on the basis of Period (Month/Year) and Period will be dynamically construct as column on the basis of Invoice Period i.e (Invoice from date and to date)

DECLARE   @dtFrom DATETIME
        , @dtTo DATETIME
        , @sPeriodList VARCHAR(100)
        , @sQuery NVARCHAR(500)

SET @dtFrom = '2010-10-01'
SET @dtTo = '2010-11-30'

SELECT @sPeriodList = STUFF((
    SELECT  distinct ',[' + CONVERT(VARCHAR(7), InvDate, 111) + ']' AS [data()]
    FROM    tblSales
    WHERE  InvDate BETWEEN @dtFrom AND @dtTo    
    FOR XML PATH('')
),1,1,'')

SET @sQuery='SELECT CustCode,' + @sPeriodList + ' FROM (
  SELECT CustCode, InvAmt, CONVERT(VARCHAR(7), InvDate, 111) AS Period 
  FROM   tblSales) src
  PIVOT  (SUM(InvAmt) FOR Period IN (' + @sPeriodList + ')
) pvt'
EXEC sp_executesql @sQuery
OUTPUT
Dynamic Pivot