没有光标可以汇总每日收入吗？

Question

我有一个表，用于存储多种资产的到期收入表。
该表给出了新收入金额生效的日期以及每日收入金额。

我想计算出两个日期之间的总收入。

表结构和示例数据如下：

DECLARE @incomeschedule  
TABLE (asset_no int, start_date datetime, amt decimal(14,2),
      PRIMARY KEY (asset_no, start_date))
/* 
-- amt is the amount of daily income
-- start_date is the effective date, from when that amt starts to be come in
*/

INSERT INTO @incomeschedule (asset_no, start_date, amt)
VALUES (1, '1 Jan 2010', 3)
INSERT INTO @incomeschedule (asset_no, start_date, amt)
VALUES (1, '1 Jul 2010', 4)
INSERT INTO @incomeschedule (asset_no, start_date, amt)
VALUES (1, '1 Oct 2010', 5)
INSERT INTO @incomeschedule (asset_no, start_date, amt)
VALUES (2, '1 Jan 2010', 1)
INSERT INTO @incomeschedule (asset_no, start_date, amt)
VALUES (2, '1 Jan 2012', 2)
INSERT INTO @incomeschedule (asset_no, start_date, amt)
VALUES (2, '1 Jan 2014', 4)
INSERT INTO @incomeschedule (asset_no, start_date, amt)
VALUES (2, '1 Jan 2016', 5)

因此，对于资产 1，从 1 月 1 日起每天有 3 美元收入，从 7 月 1 日起升至 4 美元，从 10 月 1 日起升至 5 美元。

对于 2010 年 1 月 1 日至 2020 年 12 月 31 日期间总收入的计算，以资产 1 为例，我们有
-- 181 天，价格为 3 美元（2010 年 1 月 1 日至 2010 年 6 月 30 日）= 543 美元
-- 加上 92 天 4 美元（2010 年 7 月 1 日至 2010 年 9 月 30 日）= 368 美元
-- 加上 5 美元的 3744 天（2010 年 10 月 1 日至 2020 年 12 月 31 日）= 18720 美元
-- 总计 19631 美元
[同样，资产 2 的价格为 14242 美元]

因此，对于 2010 年 1 月 1 日至 2020 年 12 月 31 日的输入范围，我期望得到以下输出：

asset_no    total_amt
    1      19631.00
    2      14242.00

我使用游标编写了此内容[因为我需要知道前面的行值才能执行calcs] 但我想知道是否可以使用基于集合的技术产生这些结果。

这是基于光标的代码，以防有帮助。

DECLARE @date_from datetime,
        @date_to datetime

SET @date_from = '1 Jan 2010'
SET @date_to = '31 Dec 2020'

/*-- output table to store results */
DECLARE @incomeoutput TABLE (asset_no int PRIMARY KEY, total_amt decimal(14,2))

/*-- cursor definition */
DECLARE c CURSOR FAST_FORWARD FOR 
SELECT asset_no, start_date, amt 
FROM @incomeschedule
UNION 
/* insert dummy records to zeroise from @date_from, 
   in case this is earlier than initial start_date per asset */
SELECT DISTINCT asset_no, @date_from, 0
FROM @incomeschedule
WHERE NOT EXISTS (SELECT asset_no, start_date FROM @incomeschedule WHERE start_date <= @date_from)
ORDER BY asset_no, start_date

/*-- initialise loop variables */
DECLARE @prev_asset_no int, @dummy_no int
SET @dummy_no = -999 /* arbitrary value, used to detect that we're in the first iteration */ 
SET @prev_asset_no = @dummy_no

DECLARE @prev_date datetime
SET @prev_date = @date_from

DECLARE @prev_amt decimal(14,2)
SET @prev_amt = 0

DECLARE @prev_total decimal(14,2)
SET @prev_total = 0

DECLARE @asset_no int, @start_date datetime, @amt decimal(14,2)

/*-- read values from cursor */
OPEN c
FETCH NEXT FROM c INTO @asset_no, @start_date, @amt
WHILE @@FETCH_STATUS = 0 
    BEGIN
        /*-- determine whether we're looking at a new asset or not */
        IF @prev_asset_no = @asset_no -- same asset: increment total and update loop variables
            BEGIN
                SET @prev_asset_no = @asset_no
                SET @prev_total = @prev_total + (@prev_amt * DATEDIFF(d, @prev_date, @start_date))
                SET @prev_date = @start_date
                SET @prev_amt = @amt
            END
        ELSE /*-- new asset: output record and reset loop variables */
            BEGIN
                IF @prev_asset_no <> @dummy_no /*-- first time round, we don't need to output */
                    BEGIN
                        SET @prev_total = @prev_total + (@prev_amt * DATEDIFF(d, @prev_date, @date_to))
                        INSERT INTO @incomeoutput (asset_no, total_amt) VALUES (@prev_asset_no, @prev_total)
                    END
                SET @prev_asset_no = @asset_no
                SET @prev_total = 0
                SET @prev_date = @start_date
                SET @prev_amt = @amt
            END

        FETCH NEXT FROM c INTO @asset_no, @start_date, @amt
    END

SET @prev_total = @prev_total + (@prev_amt * DATEDIFF(d, @prev_date, @date_to))
INSERT INTO @incomeoutput (asset_no, total_amt) VALUES (@prev_asset_no, @prev_total)

CLOSE c
DEALLOCATE c

SELECT asset_no, total_amt 
FROM @incomeoutput

nb我确实考虑过发布基于光标的解决方案作为答案，以避免问题膨胀......但是我表达问题的方式我需要一个非基于光标的答案，所以这感觉是更好的方法。如果这不是正确的礼仪，请发表评论。

Answer 1

select i1.asset_no, 
    sum(i1.amt * cast(isnull(i2.start_date, '2020-12-31') - i1.start_date as int)) as total_amt
from @incomeschedule i1
left outer join @incomeschedule i2 on i1.asset_no = i2.asset_no 
    and i2.start_date = (
        select MIN(start_date) 
        from @incomeschedule 
        where start_date > i1.start_date 
            and asset_no = i1.asset_no
    )
group by i1.asset_no

Answer 2

为什么要使用 CTE？

declare @EndDate datetime
set @EndDate = '20201231'

select t1.asset_no,SUM(DATEDIFF(day,t1.start_date,COALESCE(t2.start_date,@EndDate))*t1.amt)
from
    @incomeschedule t1
        left join
    @incomeschedule t2
        on
            t1.asset_no = t2.asset_no and
            t1.start_date < t2.start_date
        left join
    @incomeschedule t3
        on
            t1.asset_no = t3.asset_no and
            t1.start_date < t3.start_date and
            t3.start_date < t2.start_date
where
    t3.asset_no is null
group by t1.asset_no

如果某些资产没有与范围的开始日期相同的初始条目，则查询会稍微复杂一些（但还不错）

（第三次连接到表（t3）并且空值检查是为了确保t1和t2之间的匹配行是连续的）