Блог

Во-первых, некоторые примеры данных, чтобы можно было объяснить бизнес-проблему —

 select
ItemID = 276,
Quantity,
Bucket,
DaysInMonth = day(eomonth(Bucket)),
DailyQuantity = cast(Quantity * 1.0 / day(eomonth(Bucket)) as decimal(4, 0)),
DaysFactor
into #data
from
(
    values
    ('1/1/2021', 95, 5500),
    ('2/1/2021', 75, 6000),
    ('3/1/2021', 80, 5000),
    ('4/1/2021', 82, 5300),
    ('5/1/2021', 90, 5200),
    ('6/1/2021', 80, 6500),
    ('7/1/2021', 85, 6100),
    ('8/1/2021', 90, 5100),
    ('9/1/2021', null, 5800),
    ('10/1/2021', null, 5900)
) d (Bucket, DaysFactor, Quantity);

select * from #data;
 

Итак, проблема бизнеса —
В первом ряду DaysFactor цифра 95.
Сумма прямого переноса для этой строки рассчитывается как
(31 x 177) (28 x 214) (31 x 161) (5 x 177) = 17 355
То есть…
ежедневное количество за все 31 день ведра 1/1/2021 плюс
ежедневное количество за все 28 дней ведра 2/1/2021 плюс
ежедневное количество за все 31 день ведра 3/1/2021 плюс
ежедневное количество за 5 дней ведра 4/1/2021.
Это приводит к 95 дням прогнозируемого количества.
95 дней = 31 28 31 5

Для второй строки, С DaysFactor 75 лет, то он начнется с ежедневным номер по 28 дней в 2/1/2021 ведро и выходить, пока в общей сложности 75 дней количество суммировались, вот так:
(28 х 214) (31 х 161) (16 х 177) = 13,815
75 дн. = 28 31 16

Одним из подходов к этому является построение календаря ежедневного спроса, а затем суммирование количества за указанные дни. Однако я застрял на том, как подвести итоги. Вот код, который строит календарь с ежедневными количествами:

 with
dates as
(
    select
    FirstDay = min(cast(Bucket as date)),
    LastDay = eomonth(max(cast(Bucket as date)))
    from #data
),
tally as (
    select top (select datediff(d, FirstDay, LastDay)   1 from dates)   --restrict to number of rows equal to number of days between first and last days
    n = row_number() over(order by (select null)) - 1
    from sys.messages
),
calendar as (
    select
    Bucket = dateadd(d, t.n, d.FirstDay)
    from tally t
    cross join dates d
)
select
c.Bucket,
d.DailyQuantity
from #data d
inner join calendar c
on year(d.Bucket) = year(c.Bucket)
and month(d.Bucket) = month(c.Bucket);
 

Вот скриншот подмножества строк из этого запроса:

Я надеялся использовать LEAD() для этого T-SQL, но не вижу способа поместить DaysFactor предложение into the ROWS в OVER(). Есть ли способ сделать это? Если нет, то существует ли подход, основанный на наборе, для расчета скользящей суммы?

Набор ожидаемых результатов:

Понял это, используя подход, отличный от LEAD() . Эта колонка была добавлена в #data :

 BucketEnd = cast(dateadd(d, DaysFactor - 1, Bucket) as date)
 

Затем код, который строит календарь с ежедневными количествами, показанными в исходном вопросе, был помещен во временную таблицу под названием #calendar .

Затем этот запрос выполняет вычисления:

 select
d.ItemID,
d.Bucket,
RollingForwardQuantitySum = sum(iif(c.Bucket between d.Bucket and d.BucketEnd, c.DailyQuantity, null))
from #data d
cross join #calendar c
group by
d.ItemID,
d.Bucket
order by 
d.ItemID,
cast(d.Bucket as date);
 

Результат этого запроса соответствует снимку экрана с ожидаемым набором результатов в исходном сообщении.