SQL Server 查询日期范围之间一个月的总天数 [英] SQL Server query for total number of days for a month between date ranges

问题描述

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

I have specific date range like

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

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

I want to write a SQL query which calculates the total no of days between two dates and sum total number of days of particular month

我想要的输出是，

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

谁能帮我写这个 SQL 查询?

Can anyone help me to write this SQL query?

推荐答案

理想情况下，您有一个名为Dates"的表，其中包含您将使用的所有日期，例如1950 年到 2100 年.这个查询会给你你想要的结果:

Ideally, you have a table named "Dates" with all the dates you will ever use, e.g. year 1950 through 2100. This query will give you the result you want:

  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;

结果:

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

请注意，与其仅将11"或12"显示为月份，如果您的范围超过 12 个月，这将无法正常工作，或者在跨过新年时无助于排序，此查询改为显示该月的第一天.

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

If not, you can virtually create a dates table on the fly, per the expanded query below:

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

The full working sample is given here: SQL Fiddle

这篇关于SQL Server 查询日期范围之间一个月的总天数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！