CASE Statement in MSSQL

In Microsoft SQL Server (MSSQL), the CASE statement serves a similar purpose as in Oracle, allowing for conditional logic within SQL queries. The syntax is quite similar as well. Here's how you can use the CASE statement in MSSQL with an example:

SELECT

    CustomerID,

    OrderDate,

    CASE

        WHEN DATEDIFF(day, OrderDate, GETDATE()) <= 30 THEN 'Recent'

        WHEN DATEDIFF(day, OrderDate, GETDATE()) <= 90 THEN 'Within 3 Months'

        ELSE 'More than 3 Months Ago'

    END AS OrderAge

FROM

    Orders;

In this example, suppose you have a table called Orders with columns CustomerID and OrderDate. You want to categorize orders based on their age:

- If the order was placed within the last 30 days, it's categorized as "Recent".

- If the order was placed within the last 90 days but more than 30 days ago, it's categorized as "Within 3 Months".

- If the order was placed more than 90 days ago, it's categorized as "More than 3 Months Ago".

The CASE statement evaluates each condition sequentially and returns the result associated with the first condition that evaluates to true. If none of the conditions are met, the ELSE clause provides a default result.

You can use various functions and expressions within the CASE statement to tailor the logic to your specific requirements. The DATEDIFF function in this example calculates the difference in days between OrderDate and the current date (GETDATE() function).