我有一个表，表中有每天的行，int列为1/0，表示没有。我需要计算每行缺勤天数的滚动总和。我正在使用sql 2016。
行上的日期将是一个月的最后一天，第一天将是过去的27到30天，具体取决于像2月份这样的月份。
我试过使用 SUM () OVER (PARTITION BY col1 ORDER by col2 ROWS BETWEEN 30 PRECEDING and CURRENT ROW) 当我们有像二月这样的月份时，它就会失败。我需要的是一种 ROWS BETWEEN N PRECEDING and CURRENT ROW 其中n是基于行上的日期和一个月前的开始日期计算的。

为了便于我尝试，这里有一个示例脚本：

if object_id ('tempdb..#data') is not null drop table #data

create table #data
(
emp_name varchar(50)
,calendar_date date
,absence INT default(0)
)

-- script to populate table
;WITH Tally (n) AS
(
    select 0 as n
    union
    -- 1000 rows
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    CROSS JOIN (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
)
insert into #data(emp_name, calendar_date)
SELECT 'Dorris Johanssen' as emp_name, cast(DATEADD(dd, n, '2019-01-01') as date) calendar_date
FROM Tally
union
SELECT 'Broderick Snob' as emp_name, cast(DATEADD(dd, n, '2019-01-01') as date) calendar_date
FROM Tally

-- Populate Absence
update #data set absence = 1 where emp_name = 'Dorris Johanssen' and calendar_date between '2020-02-25' and '2020-03-02'
--update #data set absence = 1 where emp_name = 'Dorris Johanssen' and calendar_date between '2020-03-23' and '2020-04-07'
update #data set absence = 1 where emp_name = 'Broderick Snob' and calendar_date between '2020-03-23' and '2020-04-07'

-- Rolling sum of absence for the last one month
select *
, dateadd(dd, 1, dateadd(mm, -1, calendar_date)) as  date_one_month_before
, datediff(dd, dateadd(dd, 1, dateadd(mm, -1, calendar_date)), calendar_date) day_diff
, sum(absence) over (Partition by emp_name order by calendar_date rows between 30 preceding and current row) abs_day 
from #data 
where emp_name = 'Dorris Johanssen'