Tutorial :tsql math across multiple dates in a table



Question:

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.


Solution:1

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  


Solution:2

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.

:D


Solution:3

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.


Solution:4

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:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2009/01/23/denormalizing-to-enforce-business-rules-running-totals.aspx


Solution:5

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.

Ex:

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
Previous
Next Post »