SQL to count the amount of hours between two dates, split by Quarter

I'm struggling to create a chart in SSRS (output from SQL Query) that will show how many project hours have been spent during a quarter. If the total number of hours crosses several quarters, each quarter should have the total number of hours spent during each quarter)

I understand that using DATEDIFF(hh, ProjectStartDate, ProjectFinishDate)as [Total Number of Hours] will give me the total number of hours of a project but I need to be able to split the output into Quarters

i.e. Q1 2014, Q2 2014, Q3 2014, Q4 2014, Q1 2015 etc.

Please see attached image to show what I am aiming for with the results.projectworkdistribution.JPG
Fields in SQL table: Project Name, ProjectStartDate, ProjectEndDate

Exepected output:
Quarter              TotalHoursSpent
Q1 2014              1,800 Hours
Q2 2014              1,992 Hours
Q3 2014              990 Hours
Q4 2014              800 Hours
Q1 2015              950 Hours

Many thanks in advance!
ihatelagAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Can you give some sample data which leads to this expected output.
ihatelagAuthor Commented:
Hi Phillip,
Thanks for responding back, here is some data:
ProjectName      ProjectStartDate      ProjectFinishDate      Total Number of Hours
Alpha      2014-02-24 08:00:00.000      2016-08-18 09:00:00.000      21745
Bravo      2014-05-27 08:00:00.000      2016-01-22 17:00:00.000      14529
Charlie      2014-03-07 08:00:00.000      2015-05-26 15:00:00.000      10687
Delta      2014-12-15 08:00:00.000      2015-06-30 17:00:00.000      4737
Echo      2013-04-01 08:00:00.000      2015-05-26 12:00:00.000      18844
Foxtrot       2009-10-21 08:00:00.000      2015-03-13 17:00:00.000      47265
Golf        2013-10-07 08:00:00.000      2016-02-17 17:00:00.000      20721
Hotel      2013-10-07 08:00:00.000      2017-07-19 17:00:00.000      33153
India      2014-12-01 08:00:00.000      2015-08-31 17:00:00.000      6561
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Here's the data you gave me:
 
create table Projects
(ProjectName nvarchar(10) CONSTRAINT cntProjects PRIMARY KEY,
ProjectStartDate datetime,
ProjectFinishDate datetime,
[Total Number of Hours] int)
insert into Projects
Values ('Alpha','2014-02-24 08:00:00.000', '2016-08-18 09:00:00.000', 21745),
('Bravo', '2014-05-27 08:00:00.000', '2016-01-22 17:00:00.000', 14529),
('Charlie', '2014-03-07 08:00:00.000', '2015-05-26 15:00:00.000', 10687),
('Delta', '2014-12-15 08:00:00.000', '2015-06-30 17:00:00.000', 4737),
('Echo', '2013-04-01 08:00:00.000', '2015-05-26 12:00:00.000', 18844),
('Foxtrot', '2009-10-21 08:00:00.000', '2015-03-13 17:00:00.000', 47265),
('Golf', '2013-10-07 08:00:00.000', '2016-02-17 17:00:00.000', 20721),
('Hotel', '2013-10-07 08:00:00.000', '2017-07-19 17:00:00.000', 33153),
('India', '2014-12-01 08:00:00.000', '2015-08-31 17:00:00.000', 6561)

Open in new window


And here's the answer:
 
With myDates as
(Select top(100) dateadd(month,3 * row_number() over(Order by (select 1))-3,'2009-01-01') as QuarterStart,
dateadd(month,3 * row_number() over(Order by (select 1)),'2009-01-01') as QuarterFinish,
datediff(day,dateadd(month,3 * row_number() over(Order by (select 1))-3,'2009-01-01'),dateadd(month,3 * row_number() over(Order by (select 1)),'2009-01-01')) as NumberofDays
from sys.all_objects),
myAnalysis as
(Select *, iif(ProjectStartDate>QuarterStart, ProjectStartDate, QuarterStart) as PeriodStartDate,
iif(ProjectFinishDate<QuarterFinish, ProjectFinishDate, QuarterFinish) as PeriodFinishDate,
Datediff(day,iif(ProjectStartDate>QuarterStart, ProjectStartDate, QuarterStart),
iif(ProjectFinishDate<QuarterFinish, ProjectFinishDate, QuarterFinish)) as PeriodDays,
Datediff(day,ProjectStartDate,ProjectFinishDate) as ProjectDays
from myDates
cross join Projects)
--Select *, [Total Number of Hours]*1.0/ProjectDays*PeriodDays as QuarterHours from myAnalysis
--where PeriodDays>0
Select 'Q' + convert(varchar(1),(month(QuarterStart)+2)/3) + ' ' +
convert(varchar(4),year(QuarterStart)) as QuarterName, sum([Total Number of Hours]*1.0/ProjectDays*PeriodDays) as QuarterHours
from myAnalysis
where PeriodDays>0
group by QuarterStart

Open in new window


If you want to see the detailed table, uncomment the two lines above and comment out the subsequent lines.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

ihatelagAuthor Commented:
Thank you for your response,

I get an error "iif" is not a recognised built in function name?

Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '>'.

Also just for info sake -  noticed that you are an expert with Microsoft Project - the fields above are an extract from the MSP_Projects table from Microsoft Project Server.
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I take it you are using SQL Server 2008R2 or earlier (always worth pointing this out when asking a question).
 
Here you go;
 
With myDates as
(Select top(100) dateadd(month,3 * row_number() over(Order by (select 1))-3,'2009-01-01') as QuarterStart,
dateadd(month,3 * row_number() over(Order by (select 1)),'2009-01-01') as QuarterFinish,
datediff(day,dateadd(month,3 * row_number() over(Order by (select 1))-3,'2009-01-01'),dateadd(month,3 * row_number() over(Order by (select 1)),'2009-01-01')) as NumberofDays
from sys.all_objects),
myAnalysis as
(Select *, case when ProjectStartDate>QuarterStart then ProjectStartDate else QuarterStart end as PeriodStartDate,
case when ProjectFinishDate<QuarterFinish then ProjectFinishDate else QuarterFinish end as PeriodFinishDate,
Datediff(day,case when ProjectStartDate>QuarterStart then ProjectStartDate else QuarterStart end,
case when ProjectFinishDate<QuarterFinish then ProjectFinishDate else QuarterFinish end) as PeriodDays,
Datediff(day,ProjectStartDate,ProjectFinishDate) as ProjectDays
from myDates
cross join Projects)
--Select *, [Total Number of Hours]*1.0/ProjectDays*PeriodDays as QuarterHours from myAnalysis
--where PeriodDays>0
Select 'Q' + convert(varchar(1),(month(QuarterStart)+2)/3) + ' ' +
convert(varchar(4),year(QuarterStart)) as QuarterName, sum([Total Number of Hours]*1.0/ProjectDays*PeriodDays) as QuarterHours
from myAnalysis
where PeriodDays>0
group by QuarterStart

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ihatelagAuthor Commented:
Superb. Thank you so very much!

Also - thanks for letting me know I can use "iif" function in SQL 2012 and beyond! Apologies for not pointing out that I was using SQL Server 2008 R2, I thought my tags had it in there!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.