Databases
StackOverflow2013
Postsdbo
POSummation of Daily Income possible without cursor?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSummation of Daily Income possible without cursor?
    primarykey
    data
    text
    <p>I have a table that stores the schedule of income due, for a number of assets.<br> That table gives the date that a new income amount becomes effective, together with that daily income amount. </p> <p>I want to work out the total income due between 2 dates. </p> <p>Here's the table structure and sample data: </p> <pre><code>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) </code></pre> <p>So for Asset 1, there is $3 income daily from 1 Jan, rising to $4 from 1 Jul, to $5 from 1 Oct.</p> <p>For the calculation of total income between 1 Jan 2010 and 31 Dec 2020, using Asset 1 as an example, we have<br> -- 181 days at $3 (1 Jan 2010 to 30 Jun 2010) = $543<br> -- plus 92 days at $4 (1 Jul 2010 to 30 Sep 2010) = $368<br> -- plus 3744 days at $5 (1 Oct 2010 to 31 Dec 2020) = $18720<br> -- total $19631<br> [Similarly, Asset 2 comes in at $14242]</p> <p>So for an input range of 1 Jan 2010 to 31 Dec 2020, I expect the following output:</p> <pre><code>asset_no total_amt 1 19631.00 2 14242.00 </code></pre> <p>I have written this using a cursor [as I need to know the previous rows values to perform the calcs] but <b> I would like to know whether it is possible to produce these results using set-based techniques.</b></p> <p>Here's the cursor based code, in case that helps. </p> <pre><code>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 &lt;= @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 &lt;&gt; @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 </code></pre> <p><i>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.</i> </p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USrichaux
    1. USrichaux
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSummation of Daily Income possible without cursor?
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 for posting DDL, I would not have answered it without it.
      singulars
      1. POSummation of Daily Income possible without cursor?
      1. USRedFilter
    2. CO@RedFilter - many thanks, I like to post DDL as I can't really afford the Bounty. :)
      singulars
      1. POSummation of Daily Income possible without cursor?
      1. USrichaux
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload