将内容放入 SQL Server 2005 中的日期范围

Question

我有一个包含周范围（周数、开始日期、结束日期）的表和一个包含教程日期的表（用于编写导师（导师 ID、教程日期、教程类型（A 或 B））。

我想创建两个查询来显示顶部显示周范围（第 1 周、第 2 周），侧面显示导师姓名，并在该周的每个块的日期范围内显示教程数量（类型“A”）。

结果应如下所示：

计数。 “A”类型教程的

Tutor|Week One|Week Two|Week Three|Week Four|Total
Joe  |   3    |   5    |    7     |    8    | 23        
Sam  |   2    |   4    |    3     |    8    | 17        

数量意味着 Joe 在第一周完成了 3 个教程，在第二周完成了 5 个教程，在第三周完成了 7 个教程，在第 4 周完成了 8 个教程。

第二个查询应显示教程类型“A”和类型“B”的总计

Tutor|Week One|Week Two|Week Three|Week Four|Total   |
Joe  |  3/1   |   5/3  |   7/2    |   8/2   | 23/8   |     
Sam  |  2/3   |   4/4  |   3/2    |   8/3   | 17/12  |      

在第一周，Joe 完成了 3 个 A 类教程和 1 个 B 类教程。

教程的示例表数据（第一周）

Tutor | Tutorial_ID | Tutorial Date |Type|
------------------------------------------
Joe   |    1        | 2011-01-01    | A  |
Joe   |    2        | 2011-01-02    | A  |
Joe   |    3        | 2011-01-03    | A  |
Joe   |    4        | 2011-01-03    | B  |
Sam   |    5        | 2011-01-01    | A  |
Sam   |    6        | 2011-01-02    | A  |
Sam   |    7        | 2011-01-03    | B  |

周表如下所示：

weekNumber |startDate |endDate
1          |2011-01-01|2011-01-15

I'd like to gen this in SQL Server 2005

Answer 1

有几种方法可以做到这一点。

对于查询一，您只需在类型“A”上PIVOT，然后您只需执行PIVOT，

select *
from 
(
  select w1.tutor
    , w1.type
    , wk.weeknumber
  from w1
  inner join wk
    on w1.tutorialdate between wk.startdate and wk.enddate
  where w1.type = 'a'
) x
pivot
(
  count(type)
  for weeknumber in ([1])
)p 

请参阅SQL Fiddle 与演示

或者您可以将 Count() 与 CASE 一起使用声明。

select w1.tutor
  , COUNT(CASE WHEN w1.type = 'A' THEN 1 ELSE null END) [Week One]
from w1
inner join wk
  on w1.tutorialdate between wk.startdate and wk.enddate
group by w1.tutor

请参阅 SQL Fiddle with Demo

但对于第二个查询，我只需使用 Count() 和 CASE

select w1.tutor
  , Cast(COUNT(CASE WHEN w1.type = 'A' AND wk.weeknumber = 1 THEN 1 ELSE null END) as varchar(10))
     + ' / '
     + Cast(COUNT(CASE WHEN w1.type = 'B' AND wk.weeknumber = 1 THEN 1 ELSE null END) as varchar(10)) [Week One]
   , Cast(COUNT(CASE WHEN w1.type = 'A' AND wk.weeknumber = 2 THEN 1 ELSE null END) as varchar(10))
     + ' / '
     + Cast(COUNT(CASE WHEN w1.type = 'B' AND wk.weeknumber = 2 THEN 1 ELSE null END) as varchar(10)) [Week Two]
from w1
inner join wk
  on w1.tutorialdate between wk.startdate and wk.enddate
group by w1.tutor

请参阅 SQL Fiddle with Demo

编辑为 AndriyM 指出第二个可以通过 PIVOT 完成，这里是第二个查询的解决方案：

SELECT *
FROM
(
  select distinct w1.tutor
    , wk.weeknumber
    , left(total, len(total)-1) Totals
  FROM w1
  inner join wk
      on w1.tutorialdate between wk.startdate and wk.enddate
  CROSS APPLY
  (
    SELECT cast(count(w2.type) as varchar(max)) + ' / '
    from w1 w2
    inner join wk wk2
      on w2.tutorialdate between wk2.startdate and wk2.enddate
    WHERE w2.tutor = w1.tutor
      AND wk2.weeknumber = wk.weeknumber
    group by w2.tutor, wk2.weeknumber, w2.type
    FOR XML PATH('')
  )  D ( total )
)  x
PIVOT
(
  min(totals)
  for weeknumber in ([1], [2])
) p

请参阅 SQL 摆弄演示