不同时期的 SQL sum()？

Question

我正在开发一个存储过程。我有一个名为 #CashFlow 的表，其中包含一段时间的数据。

FundID   TradeDate Amount 
 1       1/1/2004  123.00 
 1       6/30/2006 100.00 
 2       1/1/2004  100.00 
 2       3/15/2010 150.00
 3       1/1/2010  100.00

我还有一个名为 #Funds 的表，其中包含我感兴趣的基金 ID 列表。(有一堆处理过程可以为我生成此基金列表，我不会让您感到厌烦)例如，假设我的 #Funds 表中只有 ID 1 和 2(不包括 3)。

我有三个时间段(均以“8/31/2010”结尾)，分别从 1/1/2004、1/1/2006 和 1/1/2010 开始，我想汇总这三个时间段的总和三个时期。

我尝试过这样的事情:

select sum(c1.amount), sum(c2.amount), sum(c3.amount)
from 
    #fundtable f
inner join
    #cashflow c1 on f.fundid = c1.fundid and c1.tradedate between '1/1/2004' and '8/31/2010'
inner join
    #cashflow c2 on f.fundid = c2.fundid and c2.tradedate between '1/1/2006' and '8/31/2010'
inner join 
    #cashflow c3 on f.fundid = c3.fundid and c3.tradedate between '1/1/2010' and '8/31/2010'

但它没有做我想做的事(事实上我无法弄清楚它到底在做什么)。如果我只选择一个时间段，我确实会得到该时间段的正确值，但是当我添加其中一个连接时，我的数字就会全部被破坏。

这类似于我试图替换的原始查询:

select 
   (select sum(Amount) from #Cashflow c inner join #fundtable f on c.fundid = f.fundid where tradedate between '1/1/2004' and '8/31/2010') as 'Period1',
   (select sum(Amount) from #Cashflow c inner join #fundtable f on c.fundid = f.fundid where tradedate between '1/1/2006' and '8/31/2010') as 'Period2',
   (select sum(Amount) from #Cashflow c inner join #fundtable f on c.fundid = f.fundid where tradedate between '1/1/2010' and '8/31/2010') as 'Period3'

请注意，我的日期实际上是变量(这对此无关紧要)和我的原始查询

Answer 1

SELECT  SUM(CASE WHEN TradeDate BETWEEN '2004-01-01' AND '2010-08-31' THEN Amount END),
        SUM(CASE WHEN TradeDate BETWEEN '2006-01-01' AND '2010-08-31' THEN Amount END),
        SUM(CASE WHEN TradeDate BETWEEN '2010-01-01' AND '2010-08-31' THEN Amount END)
FROM    Funds
JOIN    Cashflow
ON      Cashflow.id = Funds.id