Post reply

Stock volume calculation

  • May 22, 2016 at 3:19 pm

    #319023

    Hi there,

    I have a raw table that I get every friday night after stocks close, which gives 45 days worth of stock volume for certain companies. I am trying to build a work table that one record per month stock volume.

    The raw table from last Friday

    create table raw.stockPrice(companyid char(10) not null, Volume numeric(20,4), VolumeDate smalldatetime)

    INSERT INTO raw.StockPrice values('AB1234','12.2','2016-04-01')

    .. every day there are such records until 30th April

    INSERT INTO raw.StockPrice values('AB1234','15.2','2016-05-01')

    INSERT INTO raw.StockPrice values('AB1234','15.3','2016-05-13')

    My output table Should have

    CompanyID, Stock Volume added per month i.e. all April values added to create record 1, All May records to be added to create record 2. VolumeDate should be latest Friday.

    THis runs on Saturday night. At any point in time, there should be only one record per month.

    THe output should look like this if it was run on 14th May

    INSERT INTO work.StockPriceVolume values('AB1234','32.4','2016-04-29') -- last friday of April and addition of all volume from April

    INSERT INTO work.StockPriceVolume values('AB1234','19.4','2016-05-13)

    THe output should look like this if it was run on 21st May

    INSERT INTO work.StockPriceVolume values('AB1234','32.4','2016-04-29') -- last friday of April and addition of all volume from April

    INSERT INTO work.StockPriceVolume values('AB1234','20.4','2016-05-20) --13th may knocked out of table but insert next friday date and add all volume until 20th May

    THe output should look like this if it was run on 28th May

    INSERT INTO work.StockPriceVolume values('AB1234','32.4','2016-04-29') -- last friday of April and addition of all volume from April

    INSERT INTO work.StockPriceVolume values('AB1234','28.4','2016-05-27) --20th may knocked out of table but insert next friday date and add all volume until 27th May

    THe output should look like this if it was run on 4th June

    INSERT INTO work.StockPriceVolume values('AB1234','32.4','2016-04-29') -- last friday of April and addition of all volume from April

    INSERT INTO work.StockPriceVolume values('AB1234','28.4','2016-05-27) --last friday of May and addition of all volume from May

    INSERT INTO work.StockPriceVolume values('AB1234','28.4','2016-06-03) --insert next friday date and add all volume until June 3rd

    This is the query I have,

    INSERT INTO work.StockPriceVolume(PriceDate,CompanyID,Volume)

    SELECT maxmonthlydate as PriceDate,CompanyID,Volume

    FROM

    (

    SELECT CompanyID,YEAR(Pricedate) as Yr,MONTH(pricedate) as Mnth

    ,SUM(Volume) as volume,max(PriceDate) as maxmonthlydate

    FROM(

    SELECT DISTINCT MAX(asofdate) as PriceDate,BB.CompanyID,Volume

    FROM

    (SELECT DATEDIFF(DAY,-2,asofDate)/7 as SalesWeekly

    ,SUM(cast(value as NUMERIC(22,4))) AS Volume

    ,CompanyID

    FROM raw.stockPrice R

    GROUP BY DATEDIFF(DAY,-2,asofDate)/7,CompanyID

    )innerqry

    INNER JOIN raw.stockPrice BB

    on BB.Companyid=innerqry.Companyid and SalesWeekly=DATEDIFF(DAY,-2,asofDate)/7

    GROUP BY BB.CompanyiD,SalesWeekly,innerqry.Volume

    ) tt GROUP BY

    YEAR(Pricedate),MONTH(pricedate),CompanyID

    )qry

    where maxmonthlydate > (select MAX(PriceDate) from work.StockPriceVolume

    where CompanyID=qry.CompanyID)

    As you see, I have 2 rows for May 13th and 20th. How can I delete 13th and have only 20th.

    THanks

    Rs

  • May 23, 2016 at 1:42 am

    #1880681

    Use an UPDATE statement rather than insert?

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply