表中多个日期的tsql数学运算

Question

我有一个@variabletable，简单地定义为EOMDate(日期时间)，DandA(浮点数)，Coupon(浮点数)，EarnedIncome(浮点数)

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

我尝试做的是在填充完表之后，我需要返回并计算EarnedIncome字段来填充它。这个公式是当月的DandA减去上月的DandA加息票。我遇到麻烦的地方是如何进行更新？因此，对于6/30，该值应为4453.12 (2375-24640.63)+26718.75

我很乐意用棍棒来解决这个问题。谢谢。此外，在MS SQL2005下运行，因此如果可能，可以使用任何CTE ROW_OVER类型的解决方案。

Answer 1

您需要使用如下所示的子查询：

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

我利用了这个辅助函数

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

当然，有很多方法可以做到这一点。根据数据集的大小和其他因素，您会发现优缺点。

这是我的建议。

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

我最喜欢第二种方法，因为它只会计算前一个月是否存在记录。

(在上面的脚本中添加以下内容以查看差异)

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

这都是你到底想要什么的问题。直接使用当前记录之前的记录(与日期无关)，或仅使用当前记录之前一个月的记录。

对于格式的问题很抱歉...Stackoverflow.com的答案编辑器和我在一起玩得并不好。

:D

Answer 3

您可以使用子查询来执行计算，唯一的问题是如何处理第一个月，因为没有以前的DandA值。在这里，我使用isnull将其设置为0。该查询如下所示

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

这还假设每个表中每个月只有一条记录，并且月份之间没有任何间隔。