I have a dataset of Resources
, Projects
, StartDate
and EndDate
.
Each Resource can be utilised by multiple projects.
I want to get a count of the number of projects that are using a resource in each quarter.
So if project starts in Q1 of a particular year and ends in Q3 that year, and project2 starts in Q2 and ends in Q3, I want to get a count of 2 projects for Q2, since during Q1 both project1 and project2 were active.
Here is my dataset:
create table Projects
(Resource_Name varchar(20)
,Project_Name varchar(20)
,StartDate varchar(20)
,EndDate varchar(20)
)
insert into Projects values('Resource 1','Project A','15/01/2013','1/11/2014')
insert into Projects values('Resource 1','Project B','1/03/2013','1/09/2016')
insert into Projects values('Resource 1','Project C','1/04/2013','1/09/2015')
insert into Projects values('Resource 1','Project D','1/06/2013','1/03/2016')
insert into Projects values('Resource 1','Project E','15/01/2013','1/09/2015')
insert into Projects values('Resource 1','Project F','3/06/2013','1/11/2015')
And here is the result I'm looking for:
Resource Name| Year | Quarter|Active Projects
Resource 1 2013 1 2
Resource 1 2013 2 6
You can do this by determining the first and last quarters when a project is active, and then using cumulative sum. In SQL Server 2012+, this looks like
In earlier versions, you can do something similar with
cross apply
.Using tally table:
Using the dates from
Projects
, generate a list of all quarters and their start dates and end dates, in this example, that isCteQuarter(sd, ed)
. After that, you simply need toJOIN
theProjects
table toCteQuarter
for overlapping dates. Then finally,GROUP BY
using theYEAR
andQuarter
part of the date.SQL Fiddle
Notes:
RESULT: