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

委座

我有一个数据集Resources，Projects，StartDate和EndDate。每个资源可以被多个项目利用。
" P/ a% y" n1 N0 l% J我想统计每个季度使用资源的项目数量。
( H& m. _: C7 B: L' _- Y% f因此，如果项目在特定年份的Q1开始并在当年的Q3结束，而project2在Q2开始并在Q3结束，那么我想为Q2计数2个项目，因为在Q1期间，project1和project2都处于活动状态。
' L2 J& [+ X" d* u4 j+ V这是我的数据集：
- P. b+ {! p  Dcreate table Projects
. ?4 ^% y. v  Z(Resource_Name varchar(20)
,Project_Name varchar(20)
% H7 {, [1 z" O, H# C,StartDate varchar(20)
) e0 v: s- s' V; [- g8 s% |: z/ L,EndDate varchar(20)
)
1 N/ Z1 e( x  p& h8 n2 Z* E
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')
$ |) I8 a7 z; @$ Pinsert into Projects values('Resource 1','Project D','1/06/2013','1/03/2016')
; V& p0 i8 I9 E' linsert into Projects values('Resource 1','Project E','15/01/2013','1/09/2015')
3 I# E/ ^: ~- K, xinsert into Projects values('Resource 1','Project F','3/06/2013','1/11/2015')
这是我要寻找的结果：
5 C% T8 G9 @6 Q6 ~7 s( d! wResource Name| Year | Quarter|Active Projects
) q+ a( Z5 }# i3 J6 @Resource 1     2013     1           2
* V2 k$ O1 w) m# ^* `5 CResource 1     2013     2           6
               
# B: M% h: u! E" `" T' O6 _解决方案:
9 n" F0 c; F5 A' l1 |8 P               
+ q- f; Q# Y% x) w& [  e
4 e  l$ D# h- F- t, B4 }
. C. ^% ?8 ]; K( \" c3 d                使用理货单：
$ e# L4 E8 w$ k) ]4 g使用from的日期Projects，生成所有季度及其开始日期和结束日期的列表，在本示例中为CteQuarter(sd,
ed)。在此之后，你只需要JOIN在Projects表CteQuarter的日期重叠。最后，GROUP
BY使用日期的YEAR和Quarter部分。
0 }. {# m/ O% _SQL小提琴
2 O2 n: _9 E' \4 FWITH CteYear(yr) AS(
6 i( d5 Y, l4 @% E    SELECT number
3 t, j0 D3 c+ O  V: {2 v    FROM master..spt_values
2 y" e; s/ {  B, L. z    WHERE
        type = 'P'
        AND number >= (SELECT MIN(YEAR(CONVERT(DATE, StartDate, 103))) FROM Projects)
" }3 ~, t: @6 I1 \        AND number = q.sd
GROUP BY
; m4 ]0 B4 M( q& E4 W0 b: u    p.Resource_Name,
    DATEPART(YEAR, q.sd),
    DATEPART(QUARTER, q.sd)
ORDER BY
    p.Resource_Name,
    DATEPART(YEAR, q.sd),
    DATEPART(QUARTER, q.sd)

结果：
2 t- J3 _  L# Z0 A  V| Resource_Name | Year | Quarter | Active Projects |
|---------------|------|---------|-----------------|
0 }5 N9 O8 D6 V' v) [- V$ ?7 {|    Resource 1 | 2013 |       1 |               3 |
|    Resource 1 | 2013 |       2 |               6 |
2 W) d: t& V2 u2 o  Y# d|    Resource 1 | 2013 |       3 |               6 |
% E' Y- I# \2 V|    Resource 1 | 2013 |       4 |               6 |
) d; Y" B* _7 ~+ e+ c7 A0 [! ^  R|    Resource 1 | 2014 |       1 |               6 |
|    Resource 1 | 2014 |       2 |               6 |
; f! W5 k4 {  g) n|    Resource 1 | 2014 |       3 |               6 |
|    Resource 1 | 2014 |       4 |               6 |
0 h- T" \' j& P& J5 m. T|    Resource 1 | 2015 |       1 |               5 |
|    Resource 1 | 2015 |       2 |               5 |
|    Resource 1 | 2015 |       3 |               5 |
5 _( Y6 C3 ^9 b6 q|    Resource 1 | 2015 |       4 |               3 |
|    Resource 1 | 2016 |       1 |               2 |
|    Resource 1 | 2016 |       2 |               1 |
- R2 ]) G4 ^( J% F! J|    Resource 1 | 2016 |       3 |               1 |