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.

Answer 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

Answer 2

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

Answer 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.

Answer 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

Answer 5

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