SQL-计算日期速率内的项目活动数

Question

我有一个数据集Resources，Projects，StartDate和EndDate。每个资源可以被多个项目利用。

我想统计每个季度使用资源的项目数量。

因此，如果项目在特定年份的Q1开始并在当年的Q3结束，而project2在Q2开始并在Q3结束，那么我想为Q2计数2个项目，因为在Q1期间，project1和project2都处于活动状态。

这是我的数据集：

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')

这是我要寻找的结果：

Resource Name| Year | Quarter|Active Projects
Resource 1     2013     1           2
Resource 1     2013     2           6

Answer 1

使用理货单：

使用from的日期Projects，生成所有季度及其开始日期和结束日期的列表，在本示例中为CteQuarter(sd, ed)。在此之后，你只需要JOIN在Projects表CteQuarter的日期重叠。最后，GROUP BY使用日期的YEAR和Quarter部分。

SQL小提琴

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)

结果：

| 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 |