I'm working on a stored procedure. I have a table called #CashFlow that contains data for a span of time.
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
I also have a table called #Funds which contains a list of the fund id's that I'm interested in. (There is a bunch of processing that I won't bore you with that generates this list of fund's for me) For example, let's just say that my #Funds table only has ID's 1 and 2 in it (3 is excluded).
I have three time periods (all ending '8/31/2010') starting at 1/1/2004, 1/1/2006, and 1/1/2010 and I'd like to aggregate the sum over the three periods.
I've tried something like this:
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'
But it isn't doing what I want (in fact I can't figure out what exactly it IS doing). If I only select one period, I DO get the right values for the period, but the second I add one of the joins, my numbers get all whacked ou.
This is something like the original query that I'm trying to replace:
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'
Note that my dates are actually variables (which shouldn't matter for this) and my original query
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
Use group by:
select period, sum(money) from table group by period
精彩评论