Case statement Else part Not Working Properly .

SELECT
    CASE 
        WHEN S.SwitchPrice = 'Price2' THEN I.Price2
        WHEN S.SwitchPrice = 'Price3' THEN I.Price3
        WHEN S.SwitchPrice = 'Price4' THEN I.Price4
        ELSE I.Price
    END AS Price
FROM da5.Inventory AS I
LEFT JOIN da5.SwitchPrice AS S 
    ON S.Inventoryid = I.Inventoryid
    AND TIME(CONVERT_TZ(NOW(), @@session.time_zone, '+8:00')) > TIME(S.Start_Tm)
    AND TIME(CONVERT_TZ(NOW(), @@session.time_zone, '+8:00')) < TIME(S.End_Tm)

try this. I hope this should work.

Key changes and explanations:

1. Changed INNER JOIN to LEFT JOIN:
   • INNER JOIN requires all conditions to match, so when time condition fails, you get no rows
   
   
   • LEFT JOIN keeps all rows from Inventory table regardless of time condition match
   
   
    


2. Moved time conditions to JOIN clause:
   • This way, when time conditions fail, you'll still get the Inventory row with NULL SwitchPrice values
   
   
   • When SwitchPrice is NULL (time condition fails), CASE will fall back to ELSE (I.Price)

@Pankaj Namekar  , 

   Awesome!! and Great      Thanks lot .you have saved my time .its working as like i expected. 

 @Daniel Wright ,

 Only when the time added , that query not return default value ..so without time condition its works 

It seems like the issue you're facing with your query lies in the way the `else` part of your `CASE` statement is behaving when the time condition fails.

In your SQL query, you have set up a `CASE` statement to select the appropriate price based on certain conditions. If none of the specified conditions are met, it should default to `I.Price`. However, as you mentioned, when the time condition fails, it returns empty instead of the default price.

To address this, you may want to consider the following points:

1. Check the Data: Ensure that the data in your `SwitchPrice` table and `Inventory` table is accurate and that the `Inventoryid` is correctly mapped between the two tables for all scenarios.

2. Time Zone Consideration: Double-check that the time zone conversion and comparison logic is correctly implemented. Any discrepancy here could lead to unexpected results, especially when comparing times.

3. Debugging: You can troubleshoot by breaking down the query to see where exactly it fails. You could run the query without the `WHERE` clause to see if the `CASE` statement is working as expected and then gradually introduce the time conditions back in to pinpoint the issue.

4. Default Value Handling: If everything seems correct but the default price is not being selected, you might want to explicitly check whether `S.SwitchPrice` has unexpected values that are not being caught in your `CASE` statement structure.

By verifying these aspects and potentially debugging step by step, you should be able to identify why your `else` part is not working correctly and ensure that the default price is returned when the time condition fails. If you encounter any specific error messages or unexpected behaviors during this process, that could provide further insights into the root cause of the issue.

Let me know if you need further assistance or if you have any specific details to share for a more targeted approach!