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