SQL Server Case statement for date condition-CodePudding

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'.

CodePudding user response：

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:

Product_Code	Start_Date	End_Date	Price	Status
abc10	2019	2020	10	Old
abc10	2020	2021	11	Old
abc10	2021	2025	12	Old
abc11	2020	2021	10	Mature
abc11	2021	2025	12	Mature
abc12	2021	2025	15	New

CodePudding user response：

Edit: Updated for join.

SELECT 
    t.productcode
    ,Start_Date
    ,End_Date
    ,Price
    ,s.Status
FROM t1 as t
INNER JOIN (
    SELECT
        productcode
        ,CASE 
            WHEN MIN(Start_Date) = 2019 THEN 'Old'
            WHEN MIN(Start_Date) = 2020 THEN 'Mature' 
            WHEN MIN(Start_Date) = 2021 THEN 'New'
            ELSE 'Unknown'
        END AS Status
    FROM t1
    group by productcode
) as s on t.productcode = s.productcode

Generally speaking, you wouldn't want to hardcode these values but rather use some logic to determine Old, mature and new. As time progresses, 2021 will turn into Mature, then Old, so on and so forth. Someone will need to remember to maintain this and update the years. It would be more advisable to use DATEDIFF based on either the maximum date in the data or the current date using GETDATE() depending on your requirements.