Have Geek Brain

Tsql math across multiple dates in a table

I have @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 populate the table, I need to go back and calculate the EarnedIncome field in order to populate it. DandA formula for current month minus DandA for previous month plus coupon. Where am I having problems, how can I update? So for 6/30 the value should be 4453.12 (2375-24640.63) +26718.75

I'd love to take the club over my head to address this issue. Thank you. In addition, any solution of the CTE ROW_OVER type can be used under MS SQL2005.

+1


a source to share


5 answers


You will 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 used 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
+1


a source


There are definitely quite a few ways to do this. You will find pros and cons depending on how big your data 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 *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 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 good for the data (since technically you cannot calculate it without a prior month).

I like the second method because it will only calculate if a record exists for the previous 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 no july
Select * from @table

It's all about what you want. Use a record that directly processes the current record (regardless of date), or ONLY use a record one month before the current record.

Sorry for the format ... Stackoverflow.com answer editor and I don't play well together.

: D

+1


a source


You can use a subquery to do the calculation, the only problem is what you do with the first month, because there is no previous DandA value. Here I have set the value to 0 using isnull. The request 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 no spaces between months.

0


a source


Another alternative is to calculate the running total when you insert your data and have a constraint to ensure your current total is correct:

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

0


a source


There might be a way to do this in one of the statements, but in such cases I would tend to adjust the cursor to traverse each line by calculating a new EarnedIncome field for that line, updating the line, and then moving on to the next line.

Example:

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
-1


a source






More articles:


All Articles