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
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.
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 ?
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
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.
Youur the best
ReplyDelete