Efficient SQL query to sum unsettled trades by date.

Question

I've got a table of trades which include fields for amount, tradeDate and settlementDate

I want to return a table with the summed unsettled amount field grouped by all dates.

For a specific date the query would be:

select sum(amount) 
from trades 
where tradeDate <= @Date and settlementDate > @Date

One way to do this is (simplified):

select date, 
(select sum(amount) from trades where tradeDate <= Date and settlementDate > Date) 
from AllDates

Is there a more efficient way to do this? I think the sub query slows it down?

Thank you for any help.

Answer 1

With a plain join:

SELECT AllDates.date, SUM(trades.amount)
FROM AllDates
JOIN trades
    ON trades.tradeDate<=AllDates.date
    AND trades.settlementDate>AllDates.date
GROUP BY AllDates.date