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 < '2025/04/16')) 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,
STRING_AGG(s.sector, ', ') AS sector,
STRING_AGG(CAST(s.sectorID AS VARCHAR), ',') AS sectorID,
STRING_AGG(dbo.RemoveNonAlphaCharacters(s.sector), ',') AS sectorurl
FROM sector s
INNER JOIN SectorRelationship sr ON s.sectorID = sr.sectorID
GROUP BY sr.symbol
) AS sec ON ci.Symbol = sec.symbol
WHERE asx.indexCode = '' AND archived IS NULL
ORDER BY asx.symbol
