July 25, 2016

SQL Server Delete Duplicate Rows

There can be two types of duplication of rows in a table

1. Entire row getting duplicated because there is no primary key or unique key.
2. Only primary key or unique key value is different, but remaining all values are same.

Scenario 1: Delete duplicate rows without primary key or unique key.

Let us create the following example.

create table customers1
(CustId Int,
 CustName Varchar(20),
 CustCity Varchar(20),
 Passport_Number Varchar(20))
 go
Insert into customers1 Values(1, 'John', 'Paris', 'P123X78')
Insert into customers1 Values(2, 'Martin', 'London', 'L873X92')
Insert into customers1 Values(3, 'Smith', 'New York', 'N293Y99')
Insert into customers1 Values(1, 'John', 'Paris', 'P123X78')
go
select * from customers1
go

sql-row-duplicate

We want remove one of the duplicate records of John.

By issuing the following summary query, we can see which see which records are duplicate.

select * from customers1
Group by Custid,CustName, CustCity, Passport_Number
Having count(*) > 1

duplicate-row

Now we will add this row to a local temporary table.

Select * into #Temp_customers1 from customers1 where 1 = 2
Insert into #Temp_customers1
select * from customers1
Group by Custid,CustName, CustCity, Passport_Number
Having count(*) > 1

Now the situation is that the duplicate row is in the local temporary table. All we need to now is to delete records from main table customers1 as per matching custid of the local temporary table.

Delete from customers1
where custid in (select Custid from #Temp_customers1)

Will the above query work? Not entirely, as by using the above query, we lost all the duplicate records!! Let us see the table again.

select * from customers1
go

deleting-all-duplicate-rows

Now to keep one record of John, we will take help of the local temporary table again. Let us add the same record from temporary table into customers1 table.

Insert into Customers1
select * from #Temp_customers1
go

Finally we got a single record of John at the end. Let us confirm by seeing the Customers1 table.

select * from customers1
go

removing-duplicate-row

Once done, we can drop the local temporary table.

Scenario 2: Delete duplicate rows where primary key or unique key value is different but remaining values are same.

Let us create the following example.

create table customers2
(CustId Int Primary Key,
 CustName Varchar(20),
 CustCity Varchar(20),
 Passport_Number Varchar(20))
 go
Insert into customers2 Values(1, 'John', 'Paris', 'P123X78')
Insert into customers2 Values(2, 'Martin', 'London', 'L873X92')
Insert into customers2 Values(3, 'Smith', 'New York', 'N293Y99')
Insert into customers2 Values(4, 'John', 'Paris', 'P123X78')
Insert into customers2 Values(5, 'John', 'Paris', 'P123X78')
select * from customers2
go

Here is the same customer’s record, but this time John’s record has been added thrice with different customer ids but same Passport number!

duplicate-rows-different-custids

Scenario 2.a: Delete Duplicate rows but keep one using CTE

We need to use the technique of Self Join initially to check for duplicate records containing different custid but same passport number.

select distinct a.*
from customers2 a join customers2 b
on a.custid <> b.custid and a.CustName =  b.CustName
and a.CustCity = b.CustCity  and a.Passport_Number =  b.Passport_Number

selfjoin-showing-duplicate-rows


Now we have realized that custid 1, 4 & 5 are duplicate. The self-join statement accompanied by delete statement will give us the desired output of keeping the last duplicate record by eliminating all the previous duplicate records. We will use the Common Table Expression (CTE) and put the Self Join query in it.

With Duplicates as
(select distinct a.custid as Customer_ID
from customers2 a join customers2 b
on a.custid <> b.custid and a.CustName =  b.CustName
and a.CustCity = b.CustCity  and a.Passport_Number =  b.Passport_Number
) 
Delete from Customers2
where custid in (select Customer_ID from Duplicates)
and custid <> (select max(Customer_ID) from Duplicates)
Let’s check which rows got deleted.
select * from customers2
go

keeping-last-record

Scenario 2.b: Delete all duplicate records but keep the first original one

Let’s first truncate the customers2 table and add the same rows again.

Truncate Table customers2
go
Insert into customers2 Values(1, 'John', 'Paris', 'P123X78')
Insert into customers2 Values(2, 'Martin', 'London', 'L873X92')
Insert into customers2 Values(3, 'Smith', 'New York', 'N293Y99')
Insert into customers2 Values(4, 'John', 'Paris', 'P123X78')
Insert into customers2 Values(5, 'John', 'Paris', 'P123X78')
go

The only change in the sub query will be that we need to use min(CustomerID) instead of max(CustomerID).

So the query will be as follows.

With Duplicates as
(select distinct a.custid as Customer_ID
from customers2 a join customers2 b
on a.custid <> b.custid and a.CustName =  b.CustName
and a.CustCity = b.CustCity  and a.Passport_Number =  b.Passport_Number
) 
Delete from Customers2
where custid in (select Customer_ID from Duplicates)
and custid <> (select min(Customer_ID) from Duplicates)
Let us confirm this in the customers2 table.
select * from customers2
go

keeping-first-record


And that’s how we can delete duplicate records in SQL Server with tables without primary key, containing primary key and by keeping one original row.

July 17, 2016

SQL Server Update statement with JOIN

In this article, we are specifically going to see a practical usage of the UPDATE statement in SQL Server when the condition is based on JOIN of another table.

Example:

CREATE TABLE EMP
       (EMPNO Integer Primary Key,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE datetime,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer);
go
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902, '12/17/80',
        800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        '5/20/81',1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        '5/22/81', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        '4/2/81',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        '9/28/81', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        '5/1/81',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        '6/9/81',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        '12/9/82', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        '11/17/81', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        '9/8/1981',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        '1/12/83', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        '12/3/81',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        '12/3/81',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        '1/23/82', 1300, NULL, 10);
go

CREATE TABLE DEPT
       (DEPTNO INTEGER Primary Key,
        DNAME VARCHAR(14),
        LOC VARCHAR(13) );
go
INSERT INTO DEPT VALUES
        (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES
        (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES
        (40,'OPERATIONS','BOSTON');

Let us see the two tables.

select * from emp
go
select * from dept
go

emp-dept-table

Here we can see that this is a typical parent-child table relationship. Many a times, the end user wants to see the Employee Names (Enames) and Department Names (Dnames). But for that, they need to surrender to the inner join technique. Right now, only 14 rows are in emp table and 4 rows in dept.

But in a real time production environment, the number of records will be much higher. So this may result into a performance overhead. Therefore the DBA in such scenarios may decide to add the DName column in Emp table itself to eliminate the a JOIN overhead.

Note: Here we are moving towards an intentional De-Normalization technique.

The following ALTER Table command is used for that.

Alter Table Emp
Add Dname Varchar(40)

But now the problem is that for existing records if we see the DName colum, then it will be Null. Let us check it.

select * from emp

sql-adding-dname-column

So now we want to update each Dname as per the Dname of Dept table. For this we will issue the Update statement with join.

Update Emp 
set Dname  = Dept.DName
From Emp, Dept
where Emp.Deptno = Dept.Deptno

Technique: Here the update technique with join is used in the following sequential steps.

1. Every record of Emp is taken.
2. Its deptno is matched with deptno of Dept table.
3. The corresponding DName of Dept table is assigned to that row.
4. Next row of Emp is taken.
5. Step numbers 2 & 3 are repeated.
6. This process continues till the last row is reached by Emp table.

So now let us see the Emp table again.

select * from emp

after-update-with-sql-join

and that’s how we can use the Update statement with JOIN in SQL Server.

T-SQL Insert Into Table Examples

In T-SQL, to add records into a table (SQL Server) we use a Data Manipulation Language (DML) command known as Insert statement. In this article, we are going to explore the different capabilities of the T-SQL Insert statement.

Let us create a table Table1 with 3 columns A, B and C

create table Table1
(A int,
 B Varchar(10),
 C Date)

Example 1: Add one complete row, date in American format

Here we will mention the actual column values in the Values clause in a sequence that matches the columns mentioned in the CREATE TABLE statement.

Syntax: Insert into Table_Name Values(Col1 Value, Col2 Value….., Coln Value)

Insert into Table1 Values (1,'Aa', '12/13/2014')

Example 2: Add one complete row, date in British format

The date can be entered either in American or British format.

Insert into Table1 Values (2,'Bb', '13-Dec-2014')

Example 3: Add one partial row, specifying Null
Sometimes we do not want to add values in every column. Let us create a new row by specifying values for A and C columns only.

Insert into Table1 Values (3, Null, '14-Dec-2014') 

Example 4: Add one partial row, specifying column names

Consider there are 25 columns in a SQL table, and we want to create a new row by specifying values for only 3 columns. In that case, mentioning Null for the remaining 22 columns multiple times will be cumbersome. So instead of that, we can only mention those 3 columns after the table name, and mention the values for only those 3 columns. The remaining 22 columns by default will get null values. Caution: There should not be primary key or NOT NULL constraint to any of those columns.

Syntax: Insert into Table_Name (Column1, Column3, Column5) Values(Col1 Value, Col3 Value, Col5 Value)

Insert into Table1(A,C) Values (4,'15-Dec-2014')

Note: The columns can be mentioned in any sequence after the table name, but then the values need to mentioned in the same sequence.

Example 5: Ignore the identity column while adding row.

If the table has identity (auto number generation property) column, then by default we cannot add value to that column.

Let us create table Table2 with A as identity column and B, C as integer data type columns

create table Table2
(A int identity,
 B int,
 C int)

To add a record, use the syntax demoed in Example 4.

Insert into Table2 (B,C) Values(100,200)

Example 6: Ignore the computed column while adding row

Let us create table Table3 with a,b and c as 3 columns. C will be the computed column.

create table Table3
(A int,
 B int,
 C as (a + b))
Insert into Table3 Values(200, 300)

The column C will implicitly get value 500!

Example 7: Bulk Insertion – Select along with Union All

If we want to add many records in a table through one statement, then SELECT statement can be associated with INSERT statement instead of values clause.

Let us create table Table4 first.

create table Table4
(a int, b varchar(10)
)

Now if we want add five rows in Table4, we will use SELECT statement along with UNION ALL.

Insert into Table4
Select 1,'A'
Union All
Select 2,'B'
Union All
Select 3,'C'
Union All
Select 4,'D'
Union All
Select 5,'E'

Example 8: Bulk Insertion – Select along with Union All, violation of constraint

If any record violates the constraint, then the entire set gets rejected.

create table Table5
(a int check (a <= 3), b varchar(10)
)
Insert into Table5
Select 1,'A'
Union All
Select 2,'B'
Union All
Select 3,'C'
Union All
Select 4,'D'
Union All
Select 5,'E'

No rows will get added in Table 5.

Example 9: Bulk Insertion – Multiple rows through Values clause itself

From SQL Server 2008 version onwards, the Values clause has been strengthen to add multiple rows.

Syntax:

Insert Into TableName
Values    (……………..),
           (……………..),
           (……………..),
           (……………..)

So in the Table4 now, we will add three records using the Values clause.

Insert into Table4
Values (6,'E'),(7,'F'),(8,'G')

Note: If any one record fails then the entire batch gets rejected.

Example 10: Bulk Insertion – Copying records of another table

Many a times we need to copy records from one table to another.

Insert into Target_Table
Select * from Source_Table

Note: Here it is assumed that the structure of source & target tables are same. That means the number of columns should be same, and positional columns should be compatible as per data type & size.
Let us create table Table6 similar to Table4

create table Table6
(a int, b varchar(10)
)

Now all records of Table4 need to be inserted in Table6.

Insert into Table6
Select * from Table4


Example 11: Bulk Insertion – Copying records of another table, but not in all columns.

Let us create table Table7 similar to Table4

create table Table7
(a int, b varchar(10)
)

Here we need to mention the column names. Suppose we want to add records, but taking values of column B only, then the command will be:

Insert into Table7(B)
Select B from Table4

Conclusion:


In this article we have seen how to add records into an SQL Server table using the T-SQL Insert statement with its multiple examples.

June 27, 2016

SQL Server Alter Table with Add Column

There are many scenarios where we need to do structural changes to a table, i.e. adding a column, modifying data type, dropping a column and so on. This change to an existing table is possible by issuing a SQL Server Data Definition Language (DDL) T-SQL command known as Alter Table.

In this article we are specifically focusing on adding a new column in a table using SQL Server Alter Table.

Scenario 1: Adding columns to empty table

When the table is created and if there are no records in that table, then there is no problem in adding a column or multiple columns to that table.

Let us see the following example:

create table customers1
(
  custid varchar(5),
 custname varchar(50)
)
go

Scenario 1.a: Adding single column:

After creating the table, we realize that date of birth (DOB) column needs to be added. The following command will help us in achieving this.

Alter Table customers1
Add DOB Date
go

Scenario 1.b: Adding multiple columns:

Adding two more columns is also very easy. Let us add nationality and gender as two more columns by just having a comma separator in each definition.

Alter Table customers1
Add Nationality Varchar(40), Gender Char(1)
go

Scenario 1.c: Adding columns with constraints:

Adding a column with constraint such as primary key, unique key, foreign key, not null, check or default is again not challenging in an empty table.

Note – A primary key constraint cannot be added on the new column if there is already a primary key on any other existing column.

Let us add SSN and deposit amount as two columns, SSN having primary key and deposit amount having check constraint.

Alter Table customers1
Add SSN Varchar(10) Primary Key, 
Deposit_Amount Integer Check(Deposit_Amount >= 200)
go

Scenario 2: Adding columns to table having records

When a column is added to a table with existing records, then for those records, by default a null value is assigned by SQL Server.

Adding column on table having existing records.

Let us create table customers2 and add 2 records in it.

create table customers2
(custid varchar(5),
 custname varchar(50)
 )
 Go
Insert into customers2 Values('C1','John')
Insert into customers2 Values('C2','Martin')
go

Scenario 2.a: Adding columns with default null values on existing records.

Alter Table customers2
Add DOB Date
go

Let us see the table Customers2

select * from customers2

sqlserver-nulls-new-column
Image2-No-Default-Value

Scenario 2.b: Adding columns with default value

It might happen that you want to add a column specifying some default value and there are existing records in the table. But in the default setup, those existing records will not get that value.
Let us add token amount as a column to this Customers2 table with default value as 100.

Alter Table customers2
Add Token_Amount Integer Default 100
go

Let us see the table Customers2

select * from customers2

sql-no-default-value
Image2-No-Default-Value

Note -The default value will be effective when new record is added.

Scenario 2.c: Adding columns with default value and applying those values on existing records.

Let us add another column tax with default value as 50. This value needs to be added to the existing records as well. We need to explicitly specify the clause With Values.


Alter Table customers2
Add Tax Integer Default 50 With Values
go

default-value-withclause
Image3-Default-Value-WithClause

Scenario 2.d: Adding columns with Not Null constraint on existing records.

Let us add a column Country with Not Null constraint. Now this will be contradictory because the earlier two records cannot have null value for Country.

Let us try.

Alter Table customers2
Add Country Varchar(50) Not Null
go

It will throw the following error.

ALTER TABLE only allows columns to be added that can contain nulls

To rectify this, we need to mention the default value which will safeguard the earlier records.

Scenario 2.e: Adding columns with not null constraint and default value on existing records

Let’s have India as the default value.

Alter Table customers2
Add Country Varchar(50) Default 'India' Not Null
go

Now this works fine. Let us see the table again.

select * from customers2

default-value-notnull
Image4-Default-Value-NotNull


Conclusion:

In this article, we have seen the following scenarios of using ALTER table to add column to a SQL Server table:

1. Adding one or multiple columns to an empty table.
2. Adding column to table having records. Those records get default value as null.
3. Adding column with default value to table having records
  • In absence of With Values clause
  • In presence of With Values clause
4. Adding column with not null constraint to table having records
  • In absence of Default clause
  • In presence of Default clause

June 18, 2016

SQL Server ISNULL() With Multi Column Names

Many a times we come across null values within tables in SQL Server. Null values are the values with no data; that means the data is missing or unknown. In this article, initially we will understand the SQL Server IsNull function, then we will move towards extending the IsNull functionality using Coalesce function.

Let us see the following example.

create table Identification
(empid Integer,
ename varchar(30) NOT NULL,
passport_number char(15) ,
license_number char(15) ,
pan char(15) ,
credit_card_number char(15) ,
account_number char(15) 
)
insert into identification values(1,'John',null,null,'PN-78654','CC-12345','AN-3456')
insert into identification values(2,'Martin','PS-566774',null,null,null,null)
insert into identification values(3,'Smith',null,null,null,null,null)
insert into identification values(4,'Roger',null,null,null,null,'AN-9876')
insert into identification values(5,'King',null,null,null,'CC-8787','AN-9878')
go
select * from identification
go

sql1-identification-table
Image1-Identification-Table

In the above table, every employee has an identity proof which is either a passport number, license number, pan, credit card or account number.

SQL Server ISNULL() Function Example

Syntax:
IsNull(Parameter1, Value if null)

IsNull function returns the first parameter if it’s not null. If the first parameter is null, then it returns the second parameter.

Suppose we want to see if an employee has a passport or not, here the IsNull function can help us.

See the following example of using SQL Server ISNULL in a Select Statement:

select empid, ename, IsNull(Passport_Number, 'Not Found') 
as 'Passport Status' from identification

sql2-isnull-with-single-column
Image2-IsNull-With-Single-Column

Limitation of IsNull() function:

IsNull function can check only if one value is null. It cannot check null for multiple values. That means it is not capable of handling the functionality of checking if the first parameter is null and then move on to check the next parameter for null.

Now assume that for report generation, we want to get the passport number or license number or pan number etc. for reference purpose. If passport number is null, then we need to extract the license number. If license number is null then pan, if pan is null then account number. If all are null then we need to flag a message ‘Invalid’.

The problem is that IsNull function here needs to be used in a nesting manner.

Let us see the ISNULL being used in a select statement in a nested fashion:

select empid, ename, 
IsNull(Passport_Number,IsNull(License_Number, 
IsNull(PAN, IsNull(Credit_Card_Number, IsNull(Account_Number,'Invalid'))))) 
as "Status"
from identification

sql3-isnull-multiple-columns
Image3-IsNull-Multiple-Columns

In the above select statement, we have used IsNull function 5 times to get the desired output.

ISNULL vs Coalesce Function:

There is an alternative way using another SQL Server function known as Coalesce.

Syntax of Coalesce:
COALESCE( parameter1, parameter2, parameter3,……. parameter_n , default_parameter)

Coalesce can take multiple parameters. It returns the first not null parameter. If all the parameters are null, then it will return the default parameter.

In other words, we can say that coalesce behaves as per the following syntax as well:

CASE
   WHEN (parameter1 IS NOT NULL) THEN expression1
   WHEN (parameter2  IS NOT NULL) THEN expression2
   ...
   ELSE expressionN
END

So instead of nesting IsNull function multiple times, we can use a single coalesce function and get the same output as shown here:

select empid, ename, 
Coalesce(Passport_Number,License_Number,PAN,Credit_Card_Number,Account_Number,'Invalid')
as "Using Coalesce" from identification

sql4-coalesce
Image4-Coalesce

Conclusion:

We have seen how the SQL Server IsNull function is suitable for checking one null value and how usage of coalesce function can eliminate the need of using IsNull function redundantly.

June 09, 2016

SQL Server T-SQL DateDiff Example

How do we find the difference between two dates in SQL Server. Not just the difference in the number of days, but also number of weeks, months.

The answer is by using DateDiff in SQL Server. Datediff is also suitable for getting the time elapsed between the start and end of a process.

Here are some real life examples of how t-sql datediff can be used to get a date difference:

1. Calculating the number of days remaining for a postpaid mobile service before it expires? Here an Automated system can calculate the date difference in sql and send an SMS to a customer informing him/her of the number of days remaining.

2. Particularly suitable in embedded systems to note the time taken for each process while manufacturing a product.

3. In library management automation to keep track of the number of days a book had been issued to a customer.

4. For daily login-logout required in an electronic attendance software system of any company. The tsql datediff function can be used to calculate the exact working hours of every employee.

The signature of this function is as follows:

DATEDIFF( interval, date1, date2 )

where date 1 & date 2 can be the actual date or a part of date like an year.

Note: date1 should be greater than or equal to date2.

Interval here can be any of the following as shown in the following chart:

t-sql-date-diff
image1-t-sql datediff interval

Let us see some examples to make it more clear and interesting.

select datediff (yy, '1984', '1997')  -- 13 years difference

select datediff (dd, '1984', '1986') -- 731 days difference

select datediff (mm, '1984', '1986') -- 24 months difference

select datediff (qq, '1984', '1986') -- 8 quarters difference

select datediff (hour,'2016/05/02 11:00', '2016/05/02 14:45' 
-- 3 hours difference

Database Oriented Example


The DateDiff SQL function can also be used in a where clause.

Let us create a sample database for the same in our SQL Server instance.

Use Master
go
create database DateDiff_Demo
go
Use DateDiff_Demo
go
create table Compaints_Details
(ComplaintID Integer Identity,
 CustId Varchar(7),
 Complaint_Description Varchar(100),
 Engineer_ID Varchar(4),
 Date_of_Complaint date,
 Date_of_Resolve date
 )
 Insert into Compaints_Details 
(CustiD, Complaint_Description, Engineer_ID, 
 Date_of_Complaint,Date_of_Resolve)
 Values
 ('C1', 'Modem problem', 'E1', '21-Apr-2016', '24-Apr-2016')

 Insert into Compaints_Details 
(CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C2', 'Wire Connection problem', 'E1', '22-Apr-2016', '22-Apr-2016')

 Insert into Compaints_Details 
 (CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C3', 'Socket problem', 'E1', '23-Apr-2016', '28-Apr-2016')

 Insert into Compaints_Details 
 (CustiD, Complaint_Description, Engineer_ID, 
  Date_of_Complaint,Date_of_Resolve)
 Values
 ('C5', 'LAN problem', 'E1', '29-Apr-2016', '29-Apr-2016')
 GO
 select* from Compaints_Details
 go

sql datediff sample data
Image2-SampleData to run a sql datediff query

Let us say we want to see how many days were elapsed for every complaint to get resolved.

Here we will use the Tsql DateDiff function.

SELECT  [ComplaintID]
      ,[CustId]
      ,[Complaint_Description]
      ,[Engineer_ID]
      ,[Date_of_Complaint]
      ,[Date_of_Resolve]
      , DateDiff(dd, [Date_of_Complaint]
      , [Date_of_Resolve]) as [Days taken to resolve]
FROM [DateDiff_Demo].[dbo].[Compaints_Details]

tsql datediff days
Image3-DaysTaken before a complaint was resolved

Conclusion:


And that’s how we can use the SQL Server T-SQL DateDiff function to calculate the day, month, year, and time part differences between two specified dates.

June 01, 2016

T-SQL Date Format with Convert

In this article we are going to see a very handy SQL Server T-SQL function for formatting date called Convert(). It is generally used to show date values in different styles or formats.

Normally any date value when entered in a SQL Server table gets displayed in the default format as YYYY-MM-DD. Many a times, in your client application you may need to change the output of date & time for display purpose. For doing this the T-SQL Convert function is used.


Convert Function Example

Let us create a sample database Convert_Demo in our SQL Server database instance.

Use Master
go
Create Database Convert_Demo
go
Use Convert_Demo
go
CREATE TABLE EMP
       (EMPNO Integer Primary Key,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE date,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer);
go
INSERT INTO EMP VALUES
        (7369, 'SMITH',  'CLERK',     7902, '12/17/80',
        800, NULL, 20);
INSERT INTO EMP VALUES
        (7499, 'ALLEN',  'SALESMAN',  7698,
        '5/20/81',1600,  300, 30);
INSERT INTO EMP VALUES
        (7521, 'WARD',   'SALESMAN',  7698,
        '5/22/81', 1250,  500, 30);
INSERT INTO EMP VALUES
        (7566, 'JONES',  'MANAGER',   7839,
        '4/2/81',  2975, NULL, 20);
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN',  7698,
        '9/28/81', 1250, 1400, 30);
INSERT INTO EMP VALUES
        (7698, 'BLAKE',  'MANAGER',   7839,
        '5/1/81',  2850, NULL, 30);
INSERT INTO EMP VALUES
        (7782, 'CLARK',  'MANAGER',   7839,
        '6/9/81',  2450, NULL, 10);
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',   7566,
        '12/9/82', 3000, NULL, 20);
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL,
        '11/17/81', 5000, NULL, 10);
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN',  7698,
        '9/8/1981',  1500,    0, 30);
INSERT INTO EMP VALUES
        (7876, 'ADAMS',  'CLERK',     7788,
        '1/12/83', 1100, NULL, 20);
INSERT INTO EMP VALUES
        (7900, 'JAMES',  'CLERK',     7698,
        '12/3/81',   950, NULL, 30);
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST',   7566,
        '12/3/81',  3000, NULL, 20);
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',     7782,
        '1/23/82', 1300, NULL, 10);
go

Now let us see the default presentation style of the hiredate column by executing the following SQL statement.

select ename, hiredate from emp

sql-default-date-format


Image1-Default-date-format


Syntax of Convert function

CONVERT(data_type(length),data_to_be_converted,style)

Data_type(length) - varchar data type particulary for displaying dates in different format.

data_to_be_converted - The date value which needs to be converted.

Style - There are some predefined style ids like 101 for American date format, 103 in British format and so on. The style chart has been shown next –

sql-date-formats

Now let us see the employee names & hiredate in American as well as British formats using T-SQL Convert function.

select ename, hiredate as [Default Date Format],
convert(varchar, hiredate, 101) as [American],
convert(varchar, hiredate, 103) as [British] 
from emp

sql-changed-date-formats
Image2-Changed-Date-Formats


Convert without StyleID:

Convert is also useful to convert from one data type to another – For example changing Float to Int, Number to Float or Number to Varchar.

Using Convert to change from Float to Int


SELECT CONVERT(int, 14.85)

sql-changing-float-to-int


Image3-Changing-Float-To-Int

Using Convert to change from Number to Float


SELECT CONVERT(float, 14.0989654)    

sql-number-to-float
Image4-Number-to-Float

Using Convert to change from Number to Varchar

SELECT CONVERT(varchar(4), '12.3456')

sql-number-to-varchar
Image5-Number-to-Varchar

Conclusion:

 
We have seen in this article that using T-SQL function Convert, we can display date in different formats and also can type cast one data type into another.

May 15, 2016

Nested Case Statement in SQL Server

This article is a continuation of SQL Server CASE Statement and CASE WHEN Examples . Today we will learn about Nested Case Statement in SQL Server.

We can nest CASE statements similar to nested ifs that we find in most programming languages.

Let us see an example.

select ename, job, sal, case  -- Outer Case
    when ename like 'A%' then
    case when sal >= 1500 then 'A' -- Nested Case
    end
    when ename like 'J%' then
    case when sal >= 2900 then 'J' -- Nested Case
    end
end as "Name-Grade"            
From Emp

7-nested-case
Image 7-Nested-Case

Limit of nesting a CASE function is up to 10 levels only.

In the following example, the limit of 10 is completely utilized.

Declare @x int
set @x = 1
Select 
case when @x <= 100 then  -- Level 1
 case when @x <= 90 then -- Level 2
    case when @x <= 80 then -- Level 3
        case when @x <= 70 then -- Level 4
            case when @x <= 60 then -- Level 5
                case when @x <= 50 then -- Level 6
                    case when @x <= 40 then -- Level 7
                       case when @x <= 30 then --Level 8
                         case when @x <= 20 then--Level 9
                           case when @x<= 10 then--Level 10
                                100
End End End End End End End End End 
End as "Nested Case" 
--Ending all levels!

8-nested-case-upto-10-levels
Image 8-Nested-Case-Upto-10-Levels

If nesting is exceeding 10 levels, then SQL Server throws an error.
Declare @x int
set @x = 1
Select 
case when @x <= 100 then  -- Level 1
case when @x <= 90 then -- Level 2
case when @x <= 80 then -- Level 3
    case when @x <= 70 then -- Level 4
        case when @x <= 60 then -- Level 5
            case when @x <= 50 then -- Level 6
                case when @x <= 40 then -- Level 7
                   case when @x <= 30 then --Level 8
                     case when @x <= 20 then--Level 9
                       case when @x<= 10 then--Level 10
                          case when @x <= 11 then -- 11
                             100
End End End End End End End End End End 
End as "Nested Case"

Msg 125, Level 15, State 4, Line 14
Case expressions may only be nested to level 10.

April 22, 2016

SQL Server CASE Statement and CASE WHEN Examples

Many a times there’s a need to create a derived column in an output, based on some condition. The condition is similar to the typical if construct we use if many programming languages.

In SQL scripting, we can use Case expressions or Case Statements as you may call them, to create a derived column based on a condition.

Also read about Nested Case Statements in SQL Server.

Let us first execute a sample database creation script that we will be using in our examples:

Use Master
go
Create Database Case_Demo
go
Use Case_Demo
go
CREATE TABLE EMP
       (EMPNO Integer,
        ENAME Varchar(15),
        JOB Varchar(9),
        MGR Integer,
        HIREDATE datetime,
        SAL Integer,
        COMM Integer,
        DEPTNO Integer)
go
INSERT INTO EMP VALUES
        (7369, 'SMITH', 'CLERK', 7902, '12/17/80', 800, NULL, 20)
INSERT INTO EMP VALUES
        (7499, 'ALLEN', 'SALESMAN', 7698, '5/20/81', 1600, 300, 30)
INSERT INTO EMP VALUES
        (7521, 'WARD', 'SALESMAN', 7698, '5/22/81', 1250, 500, 30)
INSERT INTO EMP VALUES
        (7566, 'JONES', 'MANAGER',   7839, '4/2/81', 2975, NULL, 20)
INSERT INTO EMP VALUES
        (7654, 'MARTIN', 'SALESMAN', 7698, '9/28/81', 1250, 1400, 30)
INSERT INTO EMP VALUES
        (7698, 'BLAKE', 'MANAGER',   7839, '5/1/81', 2850, NULL, 30)
INSERT INTO EMP VALUES
        (7782, 'CLARK', 'MANAGER',   7839, '6/9/81', 2450, NULL, 10)
INSERT INTO EMP VALUES
        (7788, 'SCOTT',  'ANALYST',  7566, '12/9/82', 3000, NULL, 20)
INSERT INTO EMP VALUES
        (7839, 'KING',   'PRESIDENT', NULL, '11/17/81', 5000, NULL, 10)
INSERT INTO EMP VALUES
        (7844, 'TURNER', 'SALESMAN', 7698, '9/8/1981',1500,    0, 30)
INSERT INTO EMP VALUES
        (7876, 'ADAMS', 'CLERK', 7788, '1/12/83', 1100, NULL, 20)
INSERT INTO EMP VALUES
        (7900, 'JAMES', 'CLERK', 7698,  '12/3/81', 950, NULL, 30)
INSERT INTO EMP VALUES
        (7902, 'FORD',   'ANALYST', 7566, '12/3/81', 3000, NULL, 20)
INSERT INTO EMP VALUES
        (7934, 'MILLER', 'CLERK',  7782, '1/23/82', 1300, NULL, 10)
go

There are two types of Case expressions:

1. Case acting as a Switch Case construct.
2. Case acting like an If…Else If….Else construct.

Switch Case Construct

Let us see an example of Case acting as a Switch Case construct.

The syntax is as follows:

Case Expression or Column Name
    When Value1 Then Statement1 or Expression 1
    When Value2 Then Statement2 or Expression 2
    When Value3 Then Statement3 or Expression 3
    .......................................................
    .......................................................
    When Value m Then Statement m or Expression m    
    Else
        Statement n or Expression n
End

Note: The Value above will be the actual value from a column, i.e. string or numeric or date value.

The Statement can be a string , numeric or date value. Expression can be either a derived value such as [Column Name] * 12 or using a T-SQL function Lower([Column Name])

Let us see an example to make things clearer.

Let us display the employee name, job and job_points for each record. Now job_points will be a derived column based on a condition that says - If job is of Analyst then job_points will be 100, if it is clerk then 200, else for any other job type it will be 300.

select ename, job,  case job    
    when 'Analyst' then 100
    when 'Clerk' then 200
    else 300
         end as "Job Points"
from emp

1-case-as-switch-case
Image 1: Case-As-Switch-Case


Else part is optional. If it is not mentioned, then NULL will be returned for the default values.
Let us see the same

select ename, job, case job    
    when 'Analyst' then 100
    when 'Clerk' then 200
    end as "Job Points"
from emp

2-case-without else
Image 2: Case-Without Else

All the statements mentioned in the then clause should be of the same data type.

select ename, job, case job    
    when 'Analyst' then 100
    when 'Clerk' then 'Two Hundred'
    end as "Job Points"
from emp

The above query throws an error since at first the when clause is 100 i.e. a numeric value, but the next when clause has 'Two Hundred' as a value of string type.

The error message is as follows:

Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value 'Two Hundred' to data type int.

As I mentioned earlier, All the statements mentioned in the then clause should be of the same data type. So to fix this error, we need to enclose 100 in single quotes to make it a Varchar value.

select ename, job, case job    
    when 'Analyst' then '100'
    when 'Clerk' then 'Two Hundred'
    end as "Job Points"
from emp

Now we will get the desired output -

3-case-after-rectification
Image 3: Case-After-Rectification


IF ELSE Construct


Let us now see how to use the Case expression as an If…Else If….Else construct.
The syntax will be as follows:

Case 
    When Boolean Condition 1 Then Statement1 or Expression 1
    When Boolean Condition 2 Then Statement2 or Expression 2
    When Boolean Condition 3 Then Statement3 or Expression 3
    ........................................................
    ........................................................
    When Boolean Condition m Then Statement m    or Expression m
    Else
        Statement n or Expression n
End


Note: In this syntax, after the CASE keyword there is no Expression or Column Name. We directly have a When clause. The When clause will have a Boolean condition in which the column name or expression will be included.

Let us see an example of this.

Let us create a query which will show employee name, salary and salary class. If the salary is greater than or equal to 5000 then salary class will be A, if it is between 2500 and 4999 then it will be B and for remaining salaries, values will be C.

select ename, sal, case 
    when sal >= 5000 then 'A'
    when sal >= 2500 then 'B'
    else
    'C'
           end as "Salary Class"
From Emp

4-case-if-else
Image 4: Case-If-Else

Note: The sequence of Boolean conditions will matter a lot. Whichever condition is true, its statement or expression gets executed and it comes out of the CASE expression for that record.

So if we swap the first two conditions in the above example, the output will go haywire.

Let us see an example

select ename, sal, case 
    when sal >= 3000 then 'B'
    when sal >= 5000 then 'A'
    else
    'C'
           end as "Salary Class"
From Emp

5-case-logically-wrong
Image 5: Case-Logically-Wrong

Note: To avoid such logical errors, it will be better to avoid relational operators like >,>=. Instead use the between operator, mention the exact range and after doing this, if the conditions are given in any sequence, the output will be logically correct. Alternatively if you are going to continue with >,>= operators, then strictly all the conditions should be descending or all should be ascending.

We can mention multiple conditions in each WHEN clause using logical and/or operators.

The following example will depict this point.

select ename, job, sal, case 
    when job = 'clerk' and sal < 1000 then '1'
    when job = 'clerk' and sal > 1000 then '2'
    when job = 'manager' and sal > 2900 then '3'
               end as "Bonus Grade"
From Emp

6-case-multiple-conditions-in-when
Image 6-Case-Multiple-Conditions-In-When

We explored the SQL Server CASE statement and also saw the CASE WHEN example. In the next article, we will explore Nested Case Statements in SQL Server. and use CASE in Having, Order By and UPDATE statements.

April 12, 2016

PIVOT and UNPIVOT in SQL Server

PIVOT and UNPIVOT are aggregate operators which are available from SQL Server version 2005 onwards.

PIVOT can be used to transform rows into columns while UNPIVOT can be used to transform columns into rows. We will see how to use PIVOT and UNPIVOT in the following examples

Let us create the following table which has sales details of products for each country.

create table #sales (country_name varchar(100),product_name varchar(100), 
sales_date datetime, sales_amount decimal(12,2))

insert into #sales (country_name,product_name,sales_date,sales_amount)
select 'India','Television','2012-01-10',35000 union all
select 'India','Mobile','2012-12-19',22000 union all
select 'India','Laptop','2012-04-11',62500 union all
select 'India','Laptop','2013-06-23',45000 union all
select 'India','Television','2012-03-20',45000 union all
select 'India','Television','2013-05-30',56000 union all
select 'India','Mobile','2013-02-22',71200 union all
select 'USA','Television','2012-02-20',3500 union all
select 'USA','Mobile','2012-11-01',2700 union all

select 'USA','Laptop','2012-08-19',6500 union all
select 'USA','Laptop','2013-06-23',5000 union all
select 'USA','Television','2012-02-12',4560 union all
select 'USA','Television','2013-06-30',5100 union all
select 'USA','Mobile','2013-006-06',2200 

SQL Server PIVOT table Example

Suppose you want to find out the total sales by each product and summarised for each year (ie columns are years). You can use the following code

SELECT product_name,[2012],[2013] from
(select year(sales_date) as sales_year,product_name,sales_amount FROM #sales)
as t 
PIVOT(SUM(sales_amount) 
      FOR sales_year IN ([2012],[2013])) AS pivot_table
pivot-product

If you want to group it by multiple columns, you can simply include that column in the query. For example the following query will do pivot group by country_name and product_name

SELECT country_name,product_name,[2012],[2013] from
(select year(sales_date) as sales_year, country_name, product_name, 
sales_amount FROM #sales) 
as t 
PIVOT(SUM(sales_amount) 
      FOR sales_year IN ([2012],[2013])) AS pivot_table
pivot-country-product

SQL Server UnPivot Table Example

To see how unpivot works, let us create the following table

create table #marks(student_name varchar(100), English smallint, 
Mathematics smallint, Tamil smallint, Science smallint)
insert into #marks(student_name , English, Mathematics, Tamil, Science)
select 'Sankar', 78,91,79,60 union all
select 'Nilesh', 81,90,66,89 union all
select 'Murugan', 94,88,72,90 
Suppose you want to transform the columns into rows i.e. subjects become rows, use the following query:

select student_name, subject_name, mark
from #marks s
unpivot
(
  mark
  for subject_name in (English,Mathematics, Tamil,Science)
) t;
sqlserver-unpivot

Note : PIVOT in SQL Server basically produces a denormalised dataset whereas UNPIVOT produces normalised dataset.