Re: 分割株価の使用方法 (Protra-users 83) - Protra

一応ACCESS用のやつを創った、試してください。

SELECT 
  FORMAT(b.`@Date`,'yyyy/mm/dd') AS Dates, 
  INT(b.`@Open` / c.Ratio) AS Open, 
  INT(b.`@High` / c.Ratio) AS High, 
  INT(b.`@Low` / c.Ratio) AS Low, 
  INT(b.`@Close` / c.Ratio) AS Close, 
  INT(b.`@Volume` / b.Unit * 1000) AS Volume
FROM 
  (SELECT 
    Brand.Unit, Brand.`@Code`, 
    Price.`@Date`, Price.`@Volume`,
    Price.`@Open`, Price.`@High`, Price.`@Low`, Price.`@Close`
  FROM 
    Brand, Market, Price 
  WHERE 
    Market.`@Id` = Brand.`@MarketId` AND 
    Brand.`@Id` = Price.`@BrandId` AND 
    Brand.`@Code` = 9984) AND 
    Price.`@Date` > {ts '2005-12-01 00:00:00'} AND
    Price.`@Date` < {ts '2006-01-31 00:00:00'}) b, 
  (SELECT 
    a.`@Code`, a.`@Date`, 
    EXP(SUM(LOG(IIF(IsNull(Split.`@Ratio`),1,Split.`@Ratio`)))) AS Ratio 
  FROM 
    (SELECT 
      Price.`@Date`, Brand.`@Code` 
    FROM 
      Brand, Market, Price 
    WHERE 
      Market.`@Id` = Brand.`@MarketId` AND 
      Brand.`@Id` = Price.`@BrandId` AND 
      Brand.`@Code` = 9984) AND 
      Price.`@Date` > {ts '2005-12-01 00:00:00'} AND
      Price.`@Date` < {ts '2006-01-31 00:00:00'}) a 
  LEFT JOIN 
    Split 
  ON 
    a.`@Code` = Split.`@Code` AND 
    a.`@Date` < Split.`@Date` 
  GROUP BY 
    a.`@Code`, a.`@Date`) c
WHERE 
  b.`@Code` = c.`@Code` AND
  b.`@Date` = c.`@Date`
ORDER BY 
  b.`@Date` DESC

--- panacoran <panac****@yahoo*****> wrote:

> --- Mike Zang <mikez****@yahoo*****> wrote:
> 
> > ProtraDBのデータを取得して使用する場合、ひとつのSQL分で
> > PriceとSplitテーブルから分割後のデータを取得する方法が
> > 知りたいです・・・
> 
> それは無理だと思いますよ。
> 
> -- panacoran
> 
> _______________________________________________
> Protra-users mailing list
> Protr****@lists*****
> http://lists.sourceforge.jp/mailman/listinfo/protra-users
>

Protra

[Protra-users 83] Re: 分割株価の使用方法