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
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 is CteQuarter(sd, ed). After that, you simply need to JOIN the Projects table to CteQuarter for overlapping dates. Then finally, GROUP BY using the YEAR and Quarter part of the date.
SQL Fiddle
WITH CteYear(yr) AS(
SELECT number
FROM master..spt_values
WHERE
type = 'P'
AND number >= (SELECT MIN(YEAR(CONVERT(DATE, StartDate, 103))) FROM Projects)
AND number <= (SELECT MAX(YEAR(CONVERT(DATE, EndDate, 103))) FROM Projects)
),
CteQuarter(sd, ed) AS(
SELECT
DATEADD(QUARTER, q.n - 1, DATEADD(YEAR, cy.yr - 1900, 0)),
DATEADD(DAY, -1, DATEADD(QUARTER, q.n, DATEADD(YEAR, cy.yr - 1900, 0)))
FROM CteYear AS cy
CROSS JOIN(
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
) AS q(n)
)
SELECT
p.Resource_Name,
[Year] = DATEPART(YEAR, q.sd),
[Quarter] = DATEPART(QUARTER, q.sd),
[Active Projects] = COUNT(*)
FROM Projects p
INNER JOIN CteQuarter q
ON CONVERT(DATE, StartDate, 103) <= q.ed
AND CONVERT(DATE, EndDate, 103) >= q.sd
GROUP BY
p.Resource_Name,
DATEPART(YEAR, q.sd),
DATEPART(QUARTER, q.sd)
ORDER BY
p.Resource_Name,
DATEPART(YEAR, q.sd),
DATEPART(QUARTER, q.sd)
Notes:
- Here is a great way to check for overlapping dates.
- Some common date routines
RESULT:
| Resource_Name | Year | Quarter | Active Projects |
|---------------|------|---------|-----------------|
| Resource 1 | 2013 | 1 | 3 |
| Resource 1 | 2013 | 2 | 6 |
| Resource 1 | 2013 | 3 | 6 |
| Resource 1 | 2013 | 4 | 6 |
| Resource 1 | 2014 | 1 | 6 |
| Resource 1 | 2014 | 2 | 6 |
| Resource 1 | 2014 | 3 | 6 |
| Resource 1 | 2014 | 4 | 6 |
| Resource 1 | 2015 | 1 | 5 |
| Resource 1 | 2015 | 2 | 5 |
| Resource 1 | 2015 | 3 | 5 |
| Resource 1 | 2015 | 4 | 3 |
| Resource 1 | 2016 | 1 | 2 |
| Resource 1 | 2016 | 2 | 1 |
| Resource 1 | 2016 | 3 | 1 |
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
select resource_name, yyyyq,
(sum(sum(s)) over (partition by resource_name order by yyyyq) -
sum(sum(e)) over (partition by resource_name order by yyyyq) +
e
) as activeProjects
from ((select resource_name, datepart(year, startdate) + datepart(quarter, startdate) as yyyyq, 1 as s, 0 as e
from projects
) union all
(select resource_name, datepart(year, enddate) + datepart(quarter, enddate), 0 as s, 1 as e
from projects
)
) yq
group by resource_name, yyyyq;
In earlier versions, you can do something similar with cross apply.
