Tutorial :tsql math across multiple dates in a table


I have a @variabletable simply defined as EOMDate(datetime), DandA(float), Coupon(float), EarnedIncome(float)

04/30/2008, 20187.5,17812.5,NULL  05/31/2008, 24640.63, 22265.63, NULL  06/30/2008, 2375, 26718.75,NULL  

What I am trying to do is after the table is populated, I need to go back and calculate the EarnedIncome field to populate it. the formula is DandA for the current month minus DandA for the previous month plus coupon. Where I am having trouble is how can I do the update? So for 6/30 the value should be 4453.12 (2375-24640.63)+26718.75

I'll gladly take a clubbing over the head to get this resolved. thanks. Also, running under MS SQL2005 so any CTE ROW_OVER type solution can be used if possible.


You would need to use a subquery like this:

UPDATE @variabletable v1  SET EarnedIncome = DandA   - (SELECT DandA FROM @variabletable v2 WHERE GetMonthOnly(DATEADD(mm, -1, v2.EOMDate)=GetMonthOnly(v1.EOMDate))  + Coupon  

And I was making use of this helper function

DROP FUNCTION GetMonthOnly  GO  CREATE FUNCTION GetMonthOnly  (      @InputDate DATETIME   )  RETURNS DATETIME  BEGIN      RETURN CAST(CAST(YEAR(@InputDate) AS VARCHAR(4)) + '/' +                  CAST(MONTH(@InputDate) AS VARCHAR(2)) + '/01' AS DATETIME)  END  GO  


There's definitely quite a few ways to do this. You'll find pros and cons depending on how large your data set is, and other factors.

Here's my recommendation...

Declare @table as table   (      EOMDate DateTime,       DandA float,      Coupon Float,      EarnedIncome Float  )    Insert into @table Values('04/30/2008', 20187.5,17812.5,NULL)  Insert into @table Values('05/31/2008', 24640.63, 22265.63, NULL)  Insert into @table Values('06/30/2008', 2375, 26718.75,NULL)      --If we know that EOMDate will only contain one entry per month, and there's *always* one entry a month...  Update @Table Set  EarnedIncome=DandA-  (Select top 1 DandA   from @table t2   where t2.EOMDate<T1.EOMDate   order by EOMDate Desc)+Coupon  From @table T1  Select * from @table    --If there's a chance that there could be more per month, or we only want the values from the previous month (do nothing if it doesn't exist)    Update @Table Set  EarnedIncome=DAndA-(  Select top 1 DandA  From @table T2  Where DateDiff(month, T1.EOMDate, T2.EOMDate)=-1  Order by EOMDate Desc)+Coupon  From @Table T1    Select * from @table  --Leave the null, it's good for the data (since technically you cannot calculate it without a prior month).  

I like the second method best because it will only calculate if there exists a record for the preceding month.

(add the following to the above script to see the difference)

--Add one for August  Insert into @table Values('08/30/2008', 2242, 22138.62,NULL)      Update @Table Set  EarnedIncome=DAndA-(          Select top 1 DandA          From @table T2          Where DateDiff(month, T1.EOMDate, T2.EOMDate)=-1          Order by EOMDate Desc  )+Coupon  From @Table T1    --August is Null because there's no july  Select * from @table  

It's all a matter of exactly what do you want. Use the record directly proceding the current record (regardless of date), or ONLY use the record that is a month before the current record.

Sorry about the format... Stackoverflow.com's answer editor and I do not play nice together.



You can use a subquery to perform the calcuation, the only problem is what do you do with the first month because there is no previous DandA value. Here I've set it to 0 using isnull. The query looks like

Update MyTable  Set EarnedIncome = DandA + Coupon - IsNull(  Select Top 1 DandA                                                From MyTable2                                                Where MyTable.EOMDate > MyTable2.EOMDate                                                Order by MyTable2.EOMDate desc), 0)  

This also assumes that you only have one record per month in each table, and that there are't any gaps between months.


Another alternative is to calculate the running total when you are inserting your data, and have a constraint guarantee that your running total is correct:



There may be a way to do this in a single statement, but in cases like this, I'd be inclined to set up a cursor to walk through each row, computing the new EarnedIncome field for that row, update the row, and then move to the next row.


DECLARE @EOMDateVal DATETIME  DECLARE @EarnedIncomeVal FLOAT    DECLARE updCursor CURSOR FOR      SELECT EOMDate FROM @variabletable    OPEN updCursor    FETCH NEXT FROM updCursor INTO @EOMDateVal     WHILE @@FETCH_STATUS = 0  BEGIN      // Compute @EarnedIncomeVal for this row here.      // This also gives you a chance to catch data integrity problems      // that would cause you to fail the whole batch if you compute      // everything in a subquery.        UPDATE @variabletable SET EarnedIncome = @EarnedIncomeVal          WHERE EOMDate = @EOMDateVal          FETCH NEXT FROM updCursor INTO @EOMDateVal   END  CLOSE updCursor   DEALLOCATE updCursor   

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »