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 = '' AND archived IS NULL
ORDER BY asx.symbol
