I want to return all rows from a Contracts table but a second WHERE clause yields only rows which are not null. (In other words in the code below the 'CAD' restriction means approx half the possible rows have no value traded in Canadian Dollars and thus are not returned --whereas I want all possible rows returned showing NULL values where applicable).
I figure it's a Left Self Join but am struggling with the syntax (and/or whether I need to do an Inner Select),
SELECT MeasurableID,
EntityID,
MIN (ContractPrice) AS LowPrice,
MAX (ContractPrice) AS HighPrice
FROM dbo.Contracts
WHERE dbo.Contracts.MeasurableID = 2018
AND Contracts.CurrencyCode IN ( 'CAD' )
GROUP BY
dbo.Contracts.MeasurableID,
dbo.Contracts.EntityID
Use conditional aggregation:
SELECT
MeasurableID,
EntityID,
MIN (CASE WHEN CurrencyCode = 'CAD' THEN ContractPrice END) AS LowPrice,
MAX (CASE WHEN CurrencyCode = 'CAD' THEN ContractPrice END) AS HighPrice
FROM dbo.Contracts
WHERE MeasurableID = 2018
GROUP BY MeasurableID, EntityID
ORDER BY MeasurableID, EntityID;