SQL-计算日期速率内的有效项目数 [英] SQL - Count number of itemactive within a date rate

问题描述

我有一个资源，项目， StartDate 和 EndDate 。
每个资源可以由多个项目使用。

我想获得每个季度使用资源的项目数。

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

这是我的数据集：

 创建表格项目
 （Resource_Name varchar（20）
，Project_Name varchar（20）
，StartDate varchar（20）
，EndDate varchar（20）
）
 
 
插入到Projects值中（'Resource 1'，'Project A'，'15/01/2013'，'1/11/2014'）
插入到Projects值中（'Resource 1'，'Project B'，'1/03/2013'，'1/09/2016'）
插入到Projects值中（'Resource 1'，'Project C'，'1/04/2013'，'1/09 / 2015'）
插入到Projects值中（'Resource 1'，'Project D'，'1/06/2013'，'1/03/2016'）
插入到Projects值中（'Resource 1'，'Project D'，'1/06/2013'，'1/03/2016'） 1'，'Project E'，'15/01/2013'，'1/09/2015'）
 insert int o项目值（资源1，项目F， 2013年3月6日， 2015年1月11日）
  

这是我要查找的结果：

 资源名称|年份季度|活动中的项目
资源1 2013 1 2 
资源1 2013 2 6 
  

解决方案

使用计数表：

使用来自 Projects 的日期，生成所有季度及其开始日期和结束日期的列表，在此示例中为 CteQuarter（sd，ed）。之后，您只需要 JOIN Projects 表到 CteQuarter 表示重叠的日期。然后最后，使用 Year 和 Quarter 部分， GROUP BY

SQL Fiddle

 与CteYear（yr）AS（
 SELECT number 
 FROM master..spt_values 
 WHERE 
 type ='P'
 AND number> ==（从项目中选择MIN（YEAR（CONVERT（DATE，StartDate，103））） 
 AND数字< =（从项目中选择SELECT MAX（YEAR（CONVERT（DATE，EndDate，103））））
），
 CteQuarter（sd，ed）AS（
选择
 DATEADD（QUARTER，qn-1，DATEADD（YEAR，cy.yr-1900，0）），
 DATEADD（DAY，-1，DATEADD（QUARTER，qn，DATEADD（YEAR，cy。 yr-1900，0）））
 from CteYear AS cy 
交叉联接（
选择1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 
）AS q（n ）
）
选择
 p.Resource_Name，
 [Year] = DATEPART（YEAR，q.sd）， 
 [季度] = DATEPART（QUARTER，q.sd），
 [活动项目] = COUNT（*）
来自项目p 
内部联接CteQuarter q 
开CONVERT（DATE，StartDate，103）< = q.ed 
和CONVERT（DATE，EndDate，103）> = q.sd 
 GROUP BY 
 p.Resource_Name，
 DATEPART（年，q.sd），
 DATEPART（四分之一，q.sd）
 OR BY BY 
 p.Resource_Name，
 DATEPART（YEAR，q.sd），
 DATEPART（QUARTER，q.sd）
  

注意：

1. 此处 是一种很好的方法检查重叠的日期。


2. 一些常用的日期例程

---

结果：

  |资源名称|年份季度|活动项目| 
 | --------------- | ------ || --------- | ------------ ----- | 
 |资源1 | 2013 | 1 | 3 | 
 |资源1 | 2013 | 2 | 6 | 
 |资源1 | 2013 | 3 | 6 | 
 |资源1 | 2013 | 4 | 6 | 
 |资源1 | 2014 | 1 | 6 | 
 |资源1 | 2014 | 2 | 6 | 
 |资源1 | 2014 | 3 | 6 | 
 |资源1 | 2014 | 4 | 6 | 
 |资源1 | 2015 | 1 | 5 | 
 |资源1 | 2015 | 2 | 5 | 
 |资源1 | 2015 | 3 | 5 | 
 |资源1 | 2015 | 4 | 3 | 
 |资源1 | 2016 | 1 | 2 | 
 |资源1 | 2016 | 2 | 1 | 
 |资源1 | 2016 | 3 | 1 | 
  

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:

1. Here is a great way to check for overlapping dates.
2. 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 |

这篇关于SQL-计算日期速率内的有效项目数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！