SQL Server Case statement for date condition

I have a SQL table which keeps ProductCode, Start_Date, End_date and prices for different products. I want create a view from this table and create a conditional column ‘Status’ and I need to assign the status of Product on the basis of how old it is and I want to keep that status same on every row.
How can I write a ‘case’ statement to create column ‘Status’?

If ProductCode started in 2019 – ‘Old’, if it started in 2020 – ‘Mature’, if it is started in 2021 – ‘New’.

>Solution :

If I understand your issue correctly, you need a combination of CASE expression and windowed MIN():

SELECT 
   *,
   CASE 
      WHEN MIN(Start_Date) OVER (PARTITION BY Product_Code) = 2019 THEN 'Old'
      WHEN MIN(Start_Date) OVER (PARTITION BY Product_Code) = 2020 THEN 'Mature'
      WHEN MIN(Start_Date) OVER (PARTITION BY Product_Code) = 2021 THEN 'New'
      ELSE ''
   END AS Status
FROM (VALUES
   ('abc10', 2019, 2020, 10),
   ('abc10', 2020, 2021, 11),
   ('abc10', 2021, 2025, 12),
   ('abc11', 2020, 2021, 10),
   ('abc11', 2021, 2025, 12),
   ('abc12', 2021, 2025, 15)
) t (Product_Code, Start_Date, End_Date, Price)
ORDER BY Product_Code, Start_Date

Result: