SET DATEFORMAT dmy;
DECLARE @dateranges TABLE( BegDate DATE, EndDate DATE,Qty int)
INSERT INTO @dateranges( BegDate, EndDate,qty)
VALUES( '01-01-2023', '31-05-2023', 2000)
;WITH CTE AS
(
--initial part
SELECT BegDate AS StartOfMonth, DATEADD(DD, -1, DATEADD(MM, 1, BegDate)) AS EndOfMonth, EndDate,qty/datediff(month,begdate,dateadd(month,1,enddate)) as quantity, qty - qty/datediff(month,begdate,dateadd(month,1,enddate)) as rem_quantity
FROM @dateranges
-- recursive part
UNION ALL
SELECT DATEADD(MM, 1, StartOfMonth) AS StartOfMonth,
DATEADD(DD, -1, DATEADD(MM, 2, StartOfMonth)) AS EndOfMonth,
EndDate, Quantity, Rem_quantity - Quantity as Rem_quantity
FROM CTE
WHERE DATEADD(MM, 1, StartOfMonth)< EndDate
)
SELECT StartOfMonth, EndOfMonth, Quantity, rem_quantity
FROM CTE
2条答案
按热度按时间yruzcnhs1#
根据我对输入和输出的理解,这里有一个例子
我们可以使用
sequence
在start & end中创建一个month-date数组。使用数组,我们可以transform
它来计算每个月的开始和结束日期,以及数量列。iaqfqrcu2#
使用SQL将列值拆分为多行