Summation of Daily Income possible without cursor?

I have a table that stores the schedule of income due, for a number of assets.

That table gives the date that a new income amount becomes effective, together with that daily income amount.

I want to work out the total income due between 2 dates.

Here's the table structure and sample data:

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',开发者_Python百科 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)

So for Asset 1, there is $3 income daily from 1 Jan, rising to $4 from 1 Jul, to $5 from 1 Oct.

For the calculation of total income between 1 Jan 2010 and 31 Dec 2020, using Asset 1 as an example, we have

-- 181 days at $3 (1 Jan 2010 to 30 Jun 2010) = $543

-- plus 92 days at $4 (1 Jul 2010 to 30 Sep 2010) = $368

-- plus 3744 days at $5 (1 Oct 2010 to 31 Dec 2020) = $18720

-- total $19631

[Similarly, Asset 2 comes in at $14242]

So for an input range of 1 Jan 2010 to 31 Dec 2020, I expect the following output:

asset_no    total_amt
    1      19631.00
    2      14242.00

I have written this using a cursor [as I need to know the previous rows values to perform the calcs] but I would like to know whether it is possible to produce these results using set-based techniques.

Here's the cursor based code, in case that helps.

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

n.b. I did consider posting the cursor-based solution as an answer, to avoid bloating the question ... but the way I've phrased the question I need a non-cursor based answer, so this feels like the better approach. Please comment if this isn't the correct etiquette.

---

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

---

Why use a 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

If there are some assets which don't have an initial entry the same as the start date of your range, the query is slightly more complex (but not too bad)

(The join to the table a 3rd time (t3) and the null check is to ensure that the matching rows between t1 and t2 are consecutive)