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

Today, I helped someone in one of the forum about above question. He wanted to find first and last Friday of every month from 1^st Jan 2006 to 31^st 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

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

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