SQL - Count number of itemactive within a date rate

Question

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

score 1 · Accepted Answer · edited May 23 '17 at 11:44

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 |

@FelixPamittan You need to make change to your CteYear. I changed the end date of first Project A to 2015 from 2014. Now your solution doesn't show 2014. http://sqlfiddle.com/#!6/c2236/1 — Anup Agrawal, Sep 09 '15 at 03:36
Q: If I have a DimDate table, with all the possible dates, do I still need the first 2 CTEs? — jmich738, Sep 09 '15 at 03:41
I know what he did. Not sure yet _how_. First have a list of `years` to cover the data. Then split all those years in `quarters` with begin and end date. The check each `quarter` and see how many project have some portion on that `quarter` — Juan Carlos Oropeza, Sep 09 '15 at 03:46
@FelixPamittan you need to use recursion to get all the years in your cteyr. Something like this http://sqlfiddle.com/#!6/c2236/16 — Anup Agrawal, Sep 09 '15 at 03:57
@jmich738, no need, as long as you can generate the start and end dates of each quarter. — Felix Pamittan, Sep 09 '15 at 04:08

score 0 · Answer 2 · answered Sep 09 '15 at 03:05

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.

SQL - Count number of itemactive within a date rate

2 Answers2

Linked