May 22, 2016 at 5:36 am
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 22, 2016 at 3:22 pm
sorry posted in the wrong forum. I copied this over to SQL 2008 forums.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply