我有3个表,1(PortfolioInstrument)持有在一个投资组合中持有的工具(Instrument),持有(holding)跨越一个日期范围(DateAdded,DateRemoved)。另一个(价格)持有每个工具(工具)的每日(交易日期)收盘价([收盘])。第三个可能很有用,(CalcDate)持有我们重新计算持有量并从投资组合中添加和删除工具的日期(CalcDate)。
SELECT SUM([Close]*Holding), TradeDate
FROM Price p1 INNER JOIN PortfolioInstrument pio ON pio.Instrument = p1.Instrument
AND pio.Portfolio = 3
WHERE EXISTS (SELECT TradeDate FROM Price p
INNER JOIN PortfolioInstrument pi ON pi.Instrument = p.Instrument AND Portfolio = 3
WHERE TradeDate >= pi.DateAdded AND
(TradeDate < pi.DateRemoved OR pi.DateRemoved IS NULL)
AND p1.ID = p.ID GROUP BY TradeDate) GROUP BY TradeDate
这是PortfolioInstrument数据集的示例
ID Portfolio Instrument Holding DateAdded DateRemoved
16256 3 410 714.28571 2007-10-01 00:00:00.0 2007-11-01 00:00:00.0
16257 3 611 564.97174 2007-10-01 00:00:00.0 2007-11-01 00:00:00.0
16258 3 538 1,797.75281 2007-10-01 00:00:00.0 2007-11-01 00:00:00.0
...
16302 3 5352 1,067,319.75 2008-02-01 00:00:00.0 2008-04-01 00:00:00.0
16303 3 5353 1,057,800.875 2008-02-01 00:00:00.0 2008-04-01 00:00:00.0
16304 3 11952 0 2008-02-29 00:00:00.0 2008-04-01 00:00:00.0
16305 3 11952 261,484,400 2008-04-01 00:00:00.0 2008-05-01 00:00:00.0
...
16315 3 8374 14,199.99902 2009-01-30 00:00:00.0 <null>
16316 3 11952 246,102,960 2009-01-30 00:00:00.0 2009-02-27 00:00:00.0
16317 3 11952 246,148,912 2009-02-27 00:00:00.0 2009-04-01 00:00:00.0
这方面的问题在于,它包括具有DateRemoved<TradeDate,因此每个重新计算的日期都有一个跳跃,它们应该从集合中删除。看了Stackoverflow上的各种DateDiff方法,但在这种情况下无法确定如何使用它们进行分组。还要注意的是,现金工具(工具=11952)在某个时候进入投资组合,然后每个月都会得到一个条目,正如你所看到的,在几个月内它会减少到0,我认为在生成的SQL中这应该无关紧要。
Thx。
David
不太清楚为什么要使用相同联接的另一个实例。如果你想排除DateRemoved <= TradeDate
中的特定持股,你可以直接在where子句中检查:
SELECT SUM(p1.[Close]*pio.Holding), TradeDate
FROM Price p1
INNER JOIN PortfolioInstrument pio
ON pio.Instrument = p1.Instrument AND pio.Portfolio = 3
WHERE p1.TradeDate >= pio.DateAdded
AND (p1.TradeDate < pio.DateRemoved OR pio.DateRemoved IS NULL)
GROUP BY p1.TradeDate
;
但是,如果您想丢弃由相同TradeDate
行组成的整个组,其中至少有一行满足条件DateRemoved <= TradeDate
,则可以使用HAVING子句,如下所示:
SELECT SUM(p1.[Close]*pio.Holding), TradeDate
FROM Price p1
INNER JOIN PortfolioInstrument pio
ON pio.Instrument = p1.Instrument AND pio.Portfolio = 3
GROUP BY p1.TradeDate
HAVING COUNT(CASE WHEN p1.TradeDate <= pio.DateRemoved) THEN 1 END) = 0
;
与适用于单个行的WHERE子句不同,HAVING是针对一组行进行计算的。在这种情况下,COUNT()函数用于计算组中有多少行具有p1.TradeDate <= pio.DateRemoved
。如果至少有一个,那么该组将从输出中丢弃,因为我在这里假设的要求是不存在这样的行。
无法找到解决方法,最终拉入原始行并在代码中进行计算。