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

Question

我有特定的日期范围，如

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查询吗？

Answer 1

理想情况下，您有一个名为"Dates"的表，其中包含您将要使用的所有日期，例如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;

结果：

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

Answer 2

尝试这个

select ((day(date_to)) - (day(date_from))) as no_of_days,month(date_from)as month from tablename 

Answer 3

我会将其分解为几个步骤(每个步骤都有一个单独的CTE)：

declare @Ranges table (FromDate date not null,ToDate date not null)
insert into @Ranges (FromDate,ToDate) values
('20121110','20121115'),
('20121121','20121122'),
('20121130','20121201')
;with Months as (
select
DATEADD(month,DATEDIFF(month,'20010101',FromDate),'20010101') as MonthStart,
DATEADD(month,DATEDIFF(month,'20010101',FromDate),'20010131') as MonthEnd
from @Ranges
union /* not all */
select
DATEADD(month,DATEDIFF(month,'20010101',ToDate),'20010101') as MonthStart,
DATEADD(month,DATEDIFF(month,'20010101',ToDate),'20010131') as MonthEnd
from @Ranges
), MonthRanges as (
select
CASE WHEN r.FromDate > m.MonthStart then r.FromDate ELSE m.MonthStart END as StartRange,
CASE WHEN r.ToDate < m.MonthEnd then r.ToDate ELSE m.MonthEnd END as EndRange
from
@Ranges r
inner join
Months m
on
r.ToDate >= m.MonthStart and
r.FromDate <= m.MonthEnd
)
select
DATEPART(month,StartRange),
SUM(DATEDIFF(day,StartRange,EndRange)+1) /* Inclusive */
from
MonthRanges
group by
DATEPART(month,StartRange)

首先，MonthsCTE会找到我们可能感兴趣的每个月的第一天和最后一天(*)。然后，MonthRanges将这些数据与原始范围重新组合，并根据需要对其进行拆分，以便我们处理的每个周期仅代表单个月的天数。然后，我们可以使用DATEDIFF来计算每个范围所跨越的天数(并加1，因为我们处理的是日期，需要包含值)

(*)如果我们不处理任何跨越多个月的范围，并且在其间的几个月内没有其他范围开始或结束，MonthsCTE将起作用。如果你需要应对这种情况，我需要修改MonthsCTE。例如，如果我们将('20120115','20120315')(没有其他范围)添加到上面的样本中，那么使用上面的样本将不会得到2月份的结果。我们需要应对这种情况吗？

---

为了应对(*)中提到的情况，我们可以将上述查询中的MonthsCTE替换为：

;With LastMonth as (
select MAX(ToDate) as Mx from @Ranges
), MultiMonths as (
select
DATEADD(month,DATEDIFF(month,'20010101',FromDate),'20010101') as MonthStart,
DATEADD(month,DATEDIFF(month,'20010101',FromDate),'20010131') as MonthEnd
from @Ranges
union all
select
DATEADD(month,1,MonthStart),
DATEADD(month,1,MonthEnd)
from MultiMonths
where MonthStart <= (select Mx from LastMonth)
), Months as (
select distinct MonthStart,MonthEnd from MultiMonths 
)

Answer 4

请原谅我写得不好的SQL
假设fromdate和today之间的月份差为1。

架构

CREATE TABLE dateData
(fromdate datetime, todate datetime)
;
INSERT INTO dateData
(fromdate, todate)
VALUES
('2012-11-10', '2012-11-15'),
('2012-11-21', '2012-11-22'),
('2012-11-30', '2012-12-01')
;

SQL

select mth, sum(days) as daysInMth
from
(
select month(fromdate) as mth,
sum(case 
when month(fromdate) = month(todate) then datediff(dd, fromdate, todate)+1
else datediff(dd, fromdate, dateadd(mm, 1, fromdate) - day(fromdate)) + 1 end)
as days 
from dateData
group by month(fromdate)
union
select month(todate) as mth,
sum(case when month(todate) <> month(fromdate) then
datediff(dd, fromdate, dateadd(mm, 1, fromdate) - day(fromdate)) + 1
else 
case when month(todate) = month(fromdate) then 0 else
datediff(dd, convert(datetime, year(todate) + '-' + month(todate) + '-1'), todate) 
end
end) as days
from dateData
group by month(todate)
) aggregated
group by mth

在SQLFiddle上查看：http://www.sqlfiddle.com/#！3/9f7da/56