sql server 2005 - Summation of Daily Income possible without cursor? -

i have table stores schedule of income due, number of assets.
table gives date new income amount becomes effective, daily income amount.

i want work out total income due between 2 dates.

here's table structure , sample data:

declare @incomeschedule   table (asset_no int, start_date datetime, amt decimal(14,2),       primary key (asset_no, start_date)) /*  -- amt amount of daily income -- start_date effective date, when amt starts come in */  insert @incomeschedule (asset_no, start_date, amt) values (1, '1 jan 2010', 3) insert @incomeschedule (asset_no, start_date, amt) values (1, '1 jul 2010', 4) insert @incomeschedule (asset_no, start_date, amt) values (1, '1 oct 2010', 5) insert @incomeschedule (asset_no, start_date, amt) values (2, '1 jan 2010', 1) insert @incomeschedule (asset_no, start_date, amt) values (2, '1 jan 2012', 2) insert @incomeschedule (asset_no, start_date, amt) values (2, '1 jan 2014', 4) insert @incomeschedule (asset_no, start_date, amt) values (2, '1 jan 2016', 5) 

so asset 1, there $3 income daily 1 jan, rising $4 1 jul, $5 1 oct.

for calculation of total income between 1 jan 2010 , 31 dec 2020, using asset 1 example, have
-- 181 days @ $3 (1 jan 2010 30 jun 2010) = $543
-- plus 92 days @ $4 (1 jul 2010 30 sep 2010) = $368
-- plus 3744 days @ $5 (1 oct 2010 31 dec 2020) = $18720
-- total $19631
[similarly, asset 2 comes in @ $14242]

so input range of 1 jan 2010 31 dec 2020, expect following output:

asset_no    total_amt     1      19631.00     2      14242.00 

i have written using cursor [as need know previous rows values perform calcs] know whether possible produce these results using set-based techniques.

here's cursor based code, in case helps.

declare @date_from datetime,         @date_to datetime  set @date_from = '1 jan 2010' set @date_to = '31 dec 2020'  /*-- output table store results */ declare @incomeoutput table (asset_no int primary key, total_amt decimal(14,2))  /*-- cursor definition */ declare c cursor fast_forward  select asset_no, start_date, amt  @incomeschedule union  /* insert dummy records zeroise @date_from,     in case earlier initial start_date per asset */ select distinct asset_no, @date_from, 0 @incomeschedule not exists (select asset_no, start_date @incomeschedule start_date <= @date_from) order asset_no, start_date  /*-- initialise loop variables */ declare @prev_asset_no int, @dummy_no int set @dummy_no = -999 /* arbitrary value, used detect we're in 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 cursor */ open c fetch next c @asset_no, @start_date, @amt while @@fetch_status = 0      begin         /*-- determine whether we're looking @ new asset or not */         if @prev_asset_no = @asset_no -- same asset: increment total , 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 , reset loop variables */             begin                 if @prev_asset_no <> @dummy_no /*-- first time round, don't need output */                     begin                         set @prev_total = @prev_total + (@prev_amt * datediff(d, @prev_date, @date_to))                         insert @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 c @asset_no, @start_date, @amt     end  set @prev_total = @prev_total + (@prev_amt * datediff(d, @prev_date, @date_to)) insert @incomeoutput (asset_no, total_amt) values (@prev_asset_no, @prev_total)  close c deallocate c  select asset_no, total_amt  @incomeoutput 

n.b. did consider posting cursor-based solution answer, avoid bloating question ... way i've phrased question need non-cursor based answer, feels better approach. please comment if isn't correct etiquette.

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