SQL Server 查询日期范围内一个月的总天数

我有特定的日期范围，例如

From Date        To Date
---------------------------    
2012-11-10       2012-11-15
2012-11-21       2012-11-22
2012-11-30       2012-12-01

我想编写一个 SQL 查询来计算两个日期之间的总天数以及特定月份的总天数

我想要的输出是

No of days     month
--------------------
   9            11
   1            12

谁能帮我写这个 SQL 查询？

理想情况下，您有一个名为“日期”的表，其中包含您将使用的所有日期，例如1950 年到 2100 年。此查询将为您提供所需的结果：

  select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
    from dates d
    join ranges r on d.thedate between r.[from date] and r.[to date]
group by datediff(m, 0, d.thedate)
order by themonth;

Result:

|   themonth | COLUMN_1 |
-------------------------
| 2012-11-01 |        9 |
| 2012-12-01 |        1 |

请注意，此查询不是仅将“11”或“12”显示为月份（如果范围超过 12 个月，则效果不佳，或者在跨越新的一年时无助于排序），而是显示第一天代替月份。

如果没有，您可以虚拟地创建一个dates根据下面的扩展查询，动态表：

;with dates(thedate) as (
  select dateadd(yy,years.number,0)+days.number
    from master..spt_values years
    join master..spt_values days
      on days.type='p' and days.number < datepart(dy,dateadd(yy,years.number+1,0)-1)
   where years.type='p' and years.number between 100 and 150
      -- note: 100-150 creates dates in the year range 2000-2050
      --       adjust as required
)
  select dateadd(m,datediff(m, 0, d.thedate),0) themonth, count(1)
    from dates d
    join ranges r on d.thedate between r.[from date] and r.[to date]
group by datediff(m, 0, d.thedate)
order by themonth;

完整的工作示例如下：SQL小提琴