How to write select query for timebased Pricechange ?

Karthik K

Hi ,

we had requirement to write the query up to Price change automatically based on the Start time and End Time .Mysql

I have written query but its else part not get called .

we have Price like Price ,Price2,Price3,Price4

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.SwitchPrice as S inner join da5.Inventory as I on S.Inventoryid=I.Inventoryid
where (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)) ;

the above query i tried but working partially . if the time condition fails ..its show empty .rather than its point default Price .

Can any one help on this.

Thanks in advance ,

Karthik.K

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Karthik K1y ago
@Daniel Wright,

Thanks for your speedy response to my question.but i tried your modifier query still the same in the result .if case statement is fails , its show null.

Only shows when time condition True..

Thanks ,

Karthik.K
Daniel Wright1y ago
It seems like you are working on a scenario where you need to retrieve prices based on specific conditions, particularly between start and end times. Your SQL query is on the right track, but the behavior you described indicates that the default price is not being selected when the time condition fails.

To ensure that the default price is returned when the time condition is not met, you can modify your query slightly. You can use a combination of `CASE` and `COALESCE` functions to handle this situation effectively.

Here is an adjusted version of your SQL query that should address the concern you raised:
```
SELECT COALESCE(
    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, I.Price) AS Price
FROM da5.SwitchPrice AS S
INNER JOIN da5.Inventory AS I ON S.Inventoryid = I.Inventoryid
WHERE (
    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)
);
```
By using `COALESCE`, if the `CASE` statement returns `NULL` (which would happen if none of the conditions are met), it will default to `I.Price`, ensuring that a price is always returned even if the time condition fails.

Give this adjusted query a try and see if it resolves the issue you were facing. If you encounter any further challenges or have additional questions, feel free to ask for more assistance. Good luck with your time-based price change implementation!