sql - SQL从数据集中总结每个月的天数

Question

我有一个看起来像这样的表，我希望能够通过 ReportID 总结以下内容。在列出的月份下，应该有一个 ReportID 和类型以及每个月的天数的列表。我不想弄清楚数据集的开始和结束日期，它应该是自动的。

[

.

[

标签： sqldatesummary

Answer 1

如果您正在使用并且您需要在 ReportIdSql server中计算 Startdate 和 EndDate 之间的天数，而 不是使用和（计算天数）：window function sumdatetime function datediff

select Type,ReportID,sum(datediff(dd,StartDate,EndDate))
over (partition by ReportId order by StartDate rows unbounded preceding)count_days_rep 
from Table

或者，如果您需要在 ReportId 和类型中汇总计数天数：

select Type,ReportID,sum(datediff(dd,StartDate,EndDate))
over (partition by ReportId,Type  order by StartDate rows unbounded preceding) count_days_rep_type
from Table

编辑：

首先，我们计算startdate和enddate的天数，然后使用Cross apply在一列中获取天数。之后，仅summing values针对每个ReportId,Type :（写评论）：

--counting days for each month group by ReportId,Type
select ReportId,Type,Tab.month_num,
sum(Tab.count_days)count_days
from
(
select *, 

--startdate: if startdate's month=enddate's month then difference between days 
--ELSE count report days for startdate (counting days from this date to the end of the month)

case when datepart(month,StartDate)=datepart(month,EndDate) then datediff(dd,StartDate,EndDate)+1 
else datepart(dd,EOMONTH(StartDate))-datepart(dd,StartDate)+1 end CountStartDays,
--stardate's month
datename(month,StartDate)MonthStartDate,


--enddate: if startdate's month=enddate's month then 0 (because this value taken into account already in CountStartDays) ELSE count report days for enddate 
--(counting days from the begginning of enddate's month till date)

 case when datepart(month,StartDate)=datepart(month,EndDate) then 0 else datepart(dd,EndDate) end CountEndDays,
--enddate's month
datename(month,EndDate)MonthEndDate
from Table

)y

CROSS APPLY

(values  (MonthStartDate,CountStartDays),
         (MonthEndDate, CountEndDays)  ) Tab (month_num,count_days)

group by Type,ReportId,Tab.month_num          

我希望你能欣赏我的努力。