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
–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
–cte as a powerful solution of this situation.
WITH CTE AS
select contractor,startDate, endDate, startdate totDate from site f
select s.contractor,s.startDate, s.endDate, s.totDate+1 from cte s
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
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