How to use multiple cases in having clause in SQL Server

Question

I have written the following code its working properly but I have to add a third case in it for year=2012.

SELECT P.PRODUCT_NAME,s.PRODUCT_ID
FROM products P
JOIN SALES S
ON P.Product_ID = S.PRODUCT_ID
GROUP BY p.PRODUCT_NAME,S.PRODUCT_ID
having (sum(case when s.year = 2011 then s.quantity else 0 end) >
        sum(case when s.year = 2010 then s.quantity else 0 end)
       );

hope so everyone can understand my problem.

Answer 1

i have found its answer:

SELECT P.PRODUCT_NAME,s.PRODUCT_ID
FROM products P
JOIN SALES S
ON P.Product_ID = S.PRODUCT_ID
GROUP BY p.PRODUCT_NAME,S.PRODUCT_ID
having (sum(case when s.year = 2011 then s.quantity else 0 end) >
        sum(case when s.year = 2010 then s.quantity else 0 end)
        and
        sum(case when s.year = 2012 then s.quantity else 0 end) >
        sum(case when s.year = 2011 then s.quantity else 0 end)
       );

Answer 2

SELECT P.PRODUCT_NAME,s.PRODUCT_ID
FROM products P
JOIN SALES  S
ON P.Product_ID = S.PRODUCT_ID
GROUP BY p.PRODUCT_NAME,S.PRODUCT_ID
having 
-- Product Wise Sold Quantity in 2021 > Sold Quantity in 2020
(
    sum(case when s.year = 2021 then s.quantity  else 0 end) >
    sum(case when s.year= 2020 then s.quantity  else 0 end)
)
OR -- You can use AND here as well
-- Product Wise Sold Quantity in 2020 > Sold Quantity in 2019
(
    sum(case when s.year = 2020 then s.quantity  else 0 end) >
    sum(case when s.year= 2019 then s.quantity  else 0 end)
)

Above solution will surely work and can be changed as per your need. Here i have taken the years 2019,2020 and 2021. You can change it as per your busienss need. However using 'case when then' in having or where always here performance hit when you have more data volume but this always looks good when we are working in the development environment or UAT environment.