Select based on calculated value, optimization

Question

I need to choose among other fields the age of the client at the time when he bought a product of a certain brand, etc. WHERE the client was, for example, from 30 to 50 years old. He wrote this query (getAge just uses DATEDIFF to return age in years)

SELECT DISTINCT customers.FirstName, customers.LastName, 
             products.ProductName,
             dbo.getAge(customers.BirthDate,sales.Datekey)
              AS Age_when_buying
FROM sales
 INNER JOIN dates ON sales.Datekey=dates.Datekey
 INNER JOIN customers ON sales.CustomerKey=customers.CustomerKey
 INNER JOIN products ON sales.ProductKey=products.ProductKey
 INNER JOIN stores ON sales.StoreKey=stores.StoreKey

WHERE stores.StoreName = 'DribleCom Europe Online Store' AND
products.BrandName = 'Proseware' AND
dbo.getAge(customers.BirthDate, sales.Datekey) >= 30 AND
dbo.getAge(customers.BirthDate, sales.Datekey) <=50

and it works, but I calculate the age three times. I tried to assign age_when_buying to a variable, but that didn't work. My next thought was to use the cursor, but I feel that there is an easier way that I do not see. The question is, is this the right way to solve this problem, or what are my options?

Answer 1

Cross Apply.

SELECT DISTINCT customers.FirstName, customers.LastName, 
             products.ProductName,
             age.age AS Age_when_buying
FROM sales
 INNER JOIN dates ON sales.Datekey=dates.Datekey
 INNER JOIN customers ON sales.CustomerKey=customers.CustomerKey
 INNER JOIN products ON sales.ProductKey=products.ProductKey
 INNER JOIN stores ON sales.StoreKey=stores.StoreKey
CROSS APPLY
(select dbo.getAge(customers.BirthDate, sales.Datekey) as age) age
WHERE stores.StoreName = 'DribleCom Europe Online Store' AND
products.BrandName = 'Proseware' AND
age.age >= 30 AND
age.age <=50

Answer 2

, , , Common Table Expression .

.. , ...

WITH CTE AS(
    select customers.FirstName
         , customers.LastName
         , dbo.getAge(customers.BirthDate,sales.Datekey) AS Age_when_buying
         , sales.StoreName
         , products.BrandName
         , products.ProductName
    from sales
         INNER JOIN customers on sales.CustomerKey=customers.CustomerKey
         INNER JOIN products ON sales.ProductKey = products.ProductKey
         INNER JOIN stores ON sales.StoreKey = stores.StoreKey
)
SELECT DISTINCT FirstName, LastName, ProductName, Age_when_buying
FROM CTE
WHERE StoreName = 'DribleCom Europe Online Store'
  AND BrandName = 'Proseware'
  AND Age_when_buying BETWEEN 30 AND 50

Answer 3

You can use the sentence WITH:

WITH Customers_Info (CustomerFirstName, CustomerLastName, CustomerKey, AgeWhenBuying)
AS
(
    SELECT customers.FirtName,
           customers.LastName,
           CustomerKey
           dbo.getAge(customers.BirthDate, sales.DateKey) As AgeWhenBuying
    FROM customers
    JOIN sale USING(CustomerKey)
)
SELECT FirstName,
       LastName,
       products.ProductName,
       Customers_Info.AgeWhenBuying
    FROM Customers_Info
    JOIN sale USING(CustomerKey)
    JOIN products USING(ProductKey)
    JOIN stores USING(StoreKey)
    WHERE stores.StoreName = 'DribleCom Europe Online Store'
      AND products.BrandName = 'Proseware'
      AND Customers_Info.AgeWhenBuying >= 30
      AND Customers_Info.AgeWhenBuying <= 50;