Tag Archives: cte

Delete Duplicate records or Select Duplicate records in SQL Server 2008/2005/2000

People may face problem in selecting or deleting duplicate records from database. This is not a new problem; every SQL Developer may face this kind of situation at least once in his/her careers. Now a days, especially after SQL Server 2005, it is becoming really easy to find duplicate records and delete it with the help of CTE (Common Table Expression), basically CTE is introduced in SQL Server 2005 version so long way back in SQL Server 2000, there was no CTE and people were using logical trick to overcome this issues.

I have already written articles on that topic which I want to share with you today.

Click here to look at the trick which works in SQL Server 2000/2005/2008.
Click Here to look at the new method with CTE which should work in SQL Server 2005+ version only.

I see many of the .NET developer still now aware with these kinds of techniques and facing problem due to unawareness. This is the main reason I wrote follow up post for my past two articles.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Find first and last Friday of every month in SQL Server 2008/2005

Today, I helped someone in one of the forum about above question. He wanted to find first and last Friday of every month from 1st Jan 2006 to 31st Dec 2020. This is bit a challenging job. There are quite a few ways to do so like temp table, CTE etc. and I have had my hand in CTE to solve this problem. Let us see the script itself.
WITH CTE AS
(
select CONVERT(datetime,’01/06/2006′) as dt,  datename(dw,CONVERT(datetime,’01/06/2006′)) as dy,datename(mm,CONVERT(datetime,’01/06/2006′)) as mn, YEAR(CONVERT(datetime,’01/06/2006′)) AS yr
union all
select s.dt+7 as dts,datename(dw,dt+7) as dy  ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr
from cte s
where s.dt<CONVERT(datetime,’12/31/2020′)
)
select MAX(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
UNION
select MIN(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
ORDER BY dt



Quite a simple to do so in CTE but if you will try to attempt above query, you will be greeted by following error.
Msg 530, Level 16, State 1, Line 1
The statement terminated. The maximum recursion 100 has been exhausted before statement completion.



How to fix that annoying error? It is crossing default recursion limit of SQL Server which is 100 and above CTE will iterate much more time than 100 so obviously you are going to face an error. All you need to do is, just have one clause after above CTE, OPTION (MaxRecursion 0).  So finally query will looks like
WITH CTE AS
(
select CONVERT(datetime,’01/06/2006′) as dt,  datename(dw,CONVERT(datetime,’01/06/2006′)) as dy,datename(mm,CONVERT(datetime,’01/06/2006′)) as mn, YEAR(CONVERT(datetime,’01/06/2006′)) AS yr
union all
select s.dt+7 as dts,datename(dw,dt+7) as dy  ,datename(mm,dt+7) as dy, YEAR(dt+7) AS yr
from cte s
where s.dt<CONVERT(datetime,’12/31/2020′)
)
select MAX(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
UNION
select MIN(dt) as dt,dy,mn,yr from cte
group by dy,mn,yr
ORDER BY dt
OPTION (MaxRecursion 0)

Enjoy!!!
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Date calculation with CTE in SQL Server 2005

One of my close friends is working on one project for Construction Company recently as a free lancer, when I visited his home yesterday, he was calculating days in C#, since I believe to manipulate data more in SQL Server I helped him in this situation and we did calculation in SQL Server itself.

Here is small part of script the script with one small situation. In big construction company, they might have many contractors to work on site; they might have one table which stores information about contractor and their start and end date. Now, I want to calculate which contractor has worked how many days in particular month. Well, it seems pretty easy, isn’t it?

You can use date difference function and sum it up, you are done. Right? NO, it won’t give you proper picture, for example if one contractor has worked from 27-Jan-09 to 5-Feb-09 than date function will display 9 days in January month but what I want is 4 days in Jan and 5 days in FEB. Let’s have a look at how to solve this by CTE, there may other ways also, if any of my reader new that, kindly give it here so that other readers can find few other ways to do the same task.

–create table for demo

create table site

(

contractor varchar(20),

startDate datetime,

endDate datetime

)

–have some date for testing,

INSERT INTO Site

SELECT ‘Ritesh’,‘2009-02-01’,‘2009-02-07’ union all

SELECT ‘Ritesh’,‘2009-02-15’,‘2009-03-15’ union all

SELECT ‘Ritesh’,‘2009-01-01’,‘2009-01-14’ union all

SELECT ‘Rajan’,‘2009-02-25’,‘2009-03-05’ union all

SELECT ‘Rajan’,‘2009-03-11’,‘2009-04-01’

–cte as a powerful solution of this situation.

WITH CTE AS

(

select contractor,startDate, endDate, startdate totDate from site f

union all

select s.contractor,s.startDate, s.endDate, s.totDate+1 from cte s

where s.totdate+1<s.enddate

)

select contractor,year(totdate) as ‘year’,month(totdate) as ‘month’,count(*) as ‘TotalDays’ from cte

group by contractor,year(totdate),month(totdate)

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

More than one CTE in SQL Server 2005

As per Microsoft we can’t create nested CTE, means you cannot create CTE under CTE but you can create sub CTE kind of stuff. Let us see it practically how will it be possible?

–create one table and enter data for first CTE

CREATE TABLE Students(Name VARCHAR(15),Address VARCHAR(50),RollNo INT)

INSERT INTO Students VALUES (‘Rashmi’,‘Ahmedabad’,1)

INSERT INTO Students VALUES (‘Moni’,‘Ahmedabad’,2)

INSERT INTO Students VALUES (‘Ritesh’,‘Ahmedabad’,3)

INSERT INTO Students VALUES (‘Rushik’,‘Ahmedabad’,4)

–create second table and enter data for second CTE

CREATE TABLE StudentDetail(RollNo INT,SUBJECT VARCHAR(20),Marks INT)

INSERT INTO StudentDetail VALUES (1,‘Science’,75)

INSERT INTO StudentDetail VALUES (1,‘Maths’,95)

INSERT INTO StudentDetail VALUES (1,‘English’,65)

INSERT INTO StudentDetail VALUES (2,‘Science’,65)

INSERT INTO StudentDetail VALUES (2,‘Maths’,85)

INSERT INTO StudentDetail VALUES (2,‘English’,75)

INSERT INTO StudentDetail VALUES (3,‘Science’,95)

INSERT INTO StudentDetail VALUES (3,‘Maths’,85)

INSERT INTO StudentDetail VALUES (3,‘English’,45)

–first CTE

;WITH ST_CTE AS (

SELECT RollNo,Name,Address FROM Students

),

–second CTE

–don’t put “WITH” statement in second CTE

STD_CTE AS (

SELECT RollNo,SUM(MARKS) AS Total,Convert(VARCHAR(5),SUM(MARKS)/Count(Rollno))+‘%’ As Percentage

FROM StudentDetail

GROUP BY RollNo

)

SELECT st.RollNo,Name,Address,Total,Percentage

FROM ST_CTE st

INNER JOIN STD_CTE std ON st.RollNo = std.RollNo

Reference: Ritesh Shah/Rashmika Vaghela

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Delete-Select-Duplicate-Records-by-CTE-in-SQL-SERVER 2005

Delete Duplicate records from table with CTE. As I explain in my previous article that selecting and deleting duplicate records, could be done by several ways so this is another way to do the same.
You can refer earlier article by following link.
http://blog.extreme-advice.com/2009/02/28/select-delete-duplicate-records-sql-server/
As this is going to be done by CTE, You can get more information about CTE by following link.
Once you create table given in above link, you can use the below given query for selecting or deleting records.
WITH DeleteDuplicateRecords AS
(
select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum
from SelectDuplicate
)
Select * from DeleteDuplicateRecords where RowNum>1
Or
WITH DeleteDuplicateRecords AS
(
select Fname,id,Row_number() over (partition by Fname,Lname order by Fname,Lname) as RowNum
from SelectDuplicate
)
DELETE from DeleteDuplicateRecords where RowNum>1
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah