query with calculations most efficient way

Question

I have written the query below which works fine & produces the correct results. However I feel this is probably not terribly efficient as my SQL experience is quite limited.

The main thing that sticks out is where I calculate the nominal differences & price differences, these two lines.

1.   isnull(hld.Nominal, 0) - isnull(nav.Nominal, 0) NomDiff
2.   isnull((hld.Price / nav.LocalPrice - 1) * 100, 0)

Because I also have to put both these lines in the where condition, so the same calculations are being calculated twice. What is a better way of writing this query?

;WITH hld AS
(
    SELECT id,
           name,
           Nominal,
           Price
    FROM tblIH
),
nav AS
(
    SELECT id,
           name,
           Nominal,
           LocalPrice
    FROM tblNC
)
SELECT COALESCE(hld.id, nav.id) id,
       COALESCE(nav.name, hld.name) name,
       ISNULL(hld.Nominal, 0) HldNom,
       ISNULL(nav.Nominal, 0) NavNom,
       ISNULL(hld.Nominal, 0) - ISNULL(nav.Nominal, 0) NomDiff,
       ISNULL(hld.Price, 0) HldPrice,
       ISNULL(nav.LocalPrice, 0) NavPrice,
       ISNULL((hld.Price / nav.LocalPrice - 1) * 100, 0)
FROM hld
FULL OUTER JOIN nav ON hld.id = nav.id
WHERE ISNULL(hld.Nominal, 0) - ISNULL(nav.Nominal, 0) <> 0
    OR ISNULL((hld.Price / nav.LocalPrice - 1) * 100, 0) <> 0

Answer 1

First you select without where condition, you have result as table tmp, then you add where condition with column NomDiff and PriceDiff

;WITH hld AS
(
    SELECT id,
           name,
           Nominal,
           Price
    FROM tblIH
),
nav AS
(
    SELECT id,
           name,
           Nominal,
           LocalPrice
    FROM tblNC
)
select * 
from (SELECT COALESCE(hld.id, nav.id) id,
           COALESCE(nav.name, hld.name) name,
           ISNULL(hld.Nominal, 0) HldNom,
           ISNULL(nav.Nominal, 0) NavNom,
           ISNULL(hld.Nominal, 0) - ISNULL(nav.Nominal, 0) NomDiff,
           ISNULL(hld.Price, 0) HldPrice,
           ISNULL(nav.LocalPrice, 0) NavPrice,
           ISNULL((hld.Price / nav.LocalPrice - 1) * 100, 0) PriceDiff
    FROM hld
    FULL OUTER JOIN nav ON hld.id = nav.id) tmp
where NomDiff <> 0 or PriceDiff <> 0