Database Simplified Headline Animator

Database Simplified

Thursday, 22 September 2011

CTE - Common Table Expression In Sql Server

Applies To SQL Server 2005+

CTE (Common Table Expression) is very useful construct in sql server and very exciting feature added to sql server. CTE is temporary result set which is not available as data in a table or view. prior to CTE there are various scenario where we used complex join, temp Table and cursor.  Here we will how see CTE is helpful over temptable or Cursor.

Syntax Of CTE



;With <CTEName> (Optional ColumnList)
As
(
Select * From TableName
)
Select * From CTE

Let’s Do Some Task With Help Of CTE

Taks 1 : List All The Dates Of Current Month



With CTE
As
(
    Select GETDATE()-DAY(Getdate())+1 As DateList
    Union All
    Select DateList+1 From CTE
    Where DateList<DateAdd(m,1,GETDATE())-DAY(Getdate())
)
Select CONVERT(Date,DateList) As DateList from CTE


Output : This is recursive use of CTE

image_thumb[2]

Task 2 : Delete Duplicate Records From The Table

Create Table With Following Script



Create Table Orders(OrderNumber Varchar(20),OrderPrice Decimal(20,2))
Go
Insert Into Orders Values
('TR0001',56.70),('TR0002',65.00),('TR0003',35.50),
('TR0002',65.00),('TR0001',56.70),('TR0001',56.70),
('TR0004',36.30),('TR0005',90.20),('TR0003',35.50),
('TR0004',36.30),('TR0006',90.20),('TR0007',35.50)
Go
Select * From Orders

So as you can see we have many duplicate OrderNumbers.

image_thumb[10]

Now Let’s See how can we delelete Duplicate records using CTE.



;With DeleteDuplicate
As
(
    Select *,ROW_NUMBER() Over (Partition By OrderNumber,OrderPrice
Order By OrderNumber) As RowNo
    From Orders
)
Select * From DeleteDuplicate

As You can see the out of above query, Row No Is Given To Every OrderNumber, Each Duplicate OrderNumber Has RowNo in Series. Now Next Is to Delete The Duplicate Rows Found. I can simply say delete All Rows Where RowNo>1. Let’s See How ?

image_thumb[14]

Delete the Data Now



;With DeleteDuplicate
As
(
    Select *,ROW_NUMBER() Over (Partition By OrderNumber,OrderPrice Order By OrderNumber) As RowNo
    From Orders
)
Delete From DeleteDuplicate Where RowNo>1


Now lets see what we have in table now



Select * From Orders
Order By OrderNumber

image_thumb[17]

Task 3 : Month Wise Sale Or Data

Create Table With Following Script



Create Table Sale(SaleId  Int Identity(1,1),SaleDate DateTime,BillAmount Decimal(22,2))
Go
Insert Into Sale Values
('2011-01-07',3457.00),('2011-01-12',876.00),('2011-01-27',2346.00),
('2011-04-01',987.00),('2011-04-13',564.00),('2011-04-23',7865.00),
('2011-08-09',11092.00),('2011-08-12',1243.00),('2011-08-20',6657.00),
('2011-09-04',456.00),('2011-09-16',876.00),('2011-09-21',8876.00)
Go


To Achieve Above Task We Generally Write following Query



Select DateName(Month,SaleDate) MonthName,SUM(BillAmount) As TotalSaleAmount
From Sale
Group By Month(SaleDate),DateName(Month,SaleDate)
Order By Month(SaleDate)

as you can see the output below shows only those month where sale happened

MonthName

TotalSaleAmount

April

9416.00

August

18992.00

January

6679.00

September

10208.00

What if the requirement is to get all the month name irrespective of sale.

Let see use of CTE in this scenario



;With CTE
As
(
    Select DATENAME(Month,'1900-01-01') As MonthName,
    DateAdd(M,1,'1900-01-01') As MonthDate,1 As MonthValue
    Union All
    Select DATENAME(Month,MonthDate) As MonthName,
    DATEADD(M,1,MonthDate) As MonthDate,MonthValue+1 as MonthValue
    From CTE Where Year(MonthDate)<=1900
)
Select MonthName,Isnull(SUM(BillAmount),0) As TotalSale
From CTE
Left Join Sale On Cte.MonthValue=Month(SaleDate)
Group By MonthValue,MonthName
Order By MonthValue

Output :

MonthName

TotalSaleAmount

January

6679.00

February

0.00

March

0.00

April

9416.00

May

0.00

June

0.00

July

0.00

August

18992.00

September

10208.00

October

0.00

November

0.00

December

0.00

You can see above the desired result with the help of CTE.

Hope From Above Examples You Might have got Information and Functionality Of CTE, And Now You Can Utilize CTE in Different Scenario According to Requirement.

No comments:

Post a Comment