SQL根据条件对特定行求和？

Question

我正在使用 MS SQL Server .这是我的表:

Create table tblVal
(
Id int identity(1,1),
Val NVARCHAR(100),
startdate datetime,
enddate datetime
)
--Inserting Records--
Insert into tblVal values(500,'20180907','20191212')
Insert into tblVal values(720,'20190407','20191212')
Insert into tblVal values(539,'20190708','20201212')
Insert into tblVal values(341,'20190221','20190712')

表如下:

   Id   |Val        |startdate      |enddate
   --- ----------------------------------------------
    1   |500        |2018-09-07     |2019-12-12 
    2   |720        |2019-04-07     |2019-12-12 
    3   |539        |2019-07-08     |2020-12-12 
    4   |341        |2019-02-21     |2019-07-12    

这就是我想要的:

Mon     |   Total
------------------
Jan     |   500
Feb     |   841
March   |   841
April   |   1561 
May     |   1561
June    |   1561
July    |   2100
........|.........

我要求和 Val列，如果它位于该特定月份。例如。在 April 的情况下月份它位于两行之间。我必须检查条件开始日期和结束日期。然后对值求和。

这是我尝试过的:

select *
into #ControlTable 
from dbo.tblVal

DECLARE @cnt INT = 0;
while @cnt<12
begin
select sum(CASE  
             WHEN MONTH(startdate) BETWEEN @cnt and MONTH(enddate) THEN 0 
              ELSE 0 
           END) 
 from #ControlTable
SET @cnt = @cnt + 1;
end

drop table #ControlTable

但从上面我无法达到结果。
我该如何解决这个问题？谢谢。

Answer 1

我相信你想要这样的东西:

with dates as (
      select min(datefromparts(year(startdate), month(startdate), 1)) as dte,
             max(datefromparts(year(enddate), month(enddate), 1)) as enddte
      from tblVal
      union all
      select dateadd(month, 1, dte), enddte
      from dates
      where dte < enddte
     )
select d.dte, sum(val)
from dates d left join
     tblval t
     on t.startdate <= eomonth(dte) and
        t.enddate >= dte
group by d.dte
order by d.dte;

这会计算数据中的所有月份。

结果与您的示例结果略有不同，但似乎与提供的数据更一致。

Here是一个db<> fiddle 。