ASX20
Index Constituents & Latest News

select asx.indexCode,asx.symbol as displaySymbol,rc.*,ci.companyname,ci.companyname2,sec.sector,CASE WHEN (sec.sectorurl IS NULL OR sec.sectorurl = '') THEN 'sector' ELSE sec.sectorurl END as SectorURL,ci.lastprice, cast(AvgRating as decimal(10,1)) as AvgRating2 ,targetGainLoss = case when ci.lastPrice = 0 then 0 when targetAvg = 0 then 0 else ((targetAvg-ci.lastPrice)/ci.lastPrice)*100 end ,dividendForecast = case when ci.lastPrice = 0 then 0 else (DividendForecastAvg/ci.lastprice) end ,(countsbuy+countbuy+counthold+countsell+countssell) AS numofrecs ,h.pDate,h.pOpen,h.pHigh,h.pLow,h.pClose,h.pVolume FROM ASXData asx LEFT JOIN companyinfo ci ON asx.symbol = ci.symbol LEFT JOIN (select symbol,pDate,pOpen,pHigh,pLow,pClose,pVolume from priceData where pDate = (select max(pDate) from priceData where pDate < '2024/12/02')) h ON asx.symbol = h.symbol LEFT JOIN ( select r1.* from recommendationcalc r1 INNER JOIN ( select symbol,max(calcDate) as maxDate from recommendationcalc where calcDate > DATEADD(MONTH, -1, getdate()) group by symbol) r2 ON r1.symbol = r2.symbol AND r1.CalcDate = r2.maxDate ) rc ON asx.symbol = rc.symbol LEFT JOIN (SELECT sr.symbol, s.sector,s.sectorID,dbo.RemoveNonAlphaCharacters(s.sector) as sectorurl FROM sector s INNER JOIN SectorRelationship sr on s.sectorID = sr.sectorID WHERE isPriority = 1) AS sec on ci.Symbol = sec.symbol WHERE asx.indexCode = 'ASX20' AND archived IS NULL ORDER BY asx.symbol