CODEWITHSUNDEEP
mysqlsql
Abdo Sh
Abdo Sh

Reputation: 49

how to deal with case, when, and where in MySQL

I have one-to-many relation ship:

branches:

enter image description here

branch_working_hours:

enter image description here

What I am trying to achieve is to set a new column "intrvl" with correct open or close, consdering:

  1. a branch can have 2 opening and closing hours a day (from1,to1 - from2,to2)
  2. if current time is between (from1 and to1) or (from2 and to2) then open
  3. if "to" is less than "from" (means branch opens after midnight), if current time greater than "from" then open
  4. if "to" less than "from" and current time after midnight then check yesterday's "to", if greater than current time then open
  5. Else close
select `branches`.*, 
    CASE
        WHEN 
            ((CURRENT_TIME() BETWEEN branch_working_hours.from1 AND branch_working_hours.to1 )
            OR (CURRENT_TIME() BETWEEN branch_working_hours.from2 AND branch_working_hours.to2) 
        
            OR branch_working_hours.is_24_hours_open= 1)
            AND EXISTS (
                SELECT * FROM branch_working_hours
                WHERE branch_working_hours.branch_id = branches.id AND branch_working_hours.day LIKE UCASE(DAYNAME(CURRENT_DATE()))
            )
        THEN 'OPEN'
        ELSE 
        CASE
            WHEN  
                ((branch_working_hours.to1 < branch_working_hours.from1 AND CURRENT_TIME() > branch_working_hours.from1)
                OR  (branch_working_hours.to2 < branch_working_hours.from2 AND CURRENT_TIME() > branch_working_hours.from2 ))
                AND EXISTS (
                    SELECT * FROM branch_working_hours
                    WHERE branch_working_hours.branch_id = branches.id AND branch_working_hours.day LIKE UCASE(DAYNAME(CURRENT_DATE()))
                  )
            THEN 'OPEN'
            ELSE 
                CASE
                    WHEN  
                        ((branch_working_hours.to1 < branch_working_hours.from1 AND CURRENT_TIME() BETWEEN '00:00:00' AND branch_working_hours.to1)
                        OR  (branch_working_hours.to2 < branch_working_hours.from2 AND CURRENT_TIME() BETWEEN '00:00:00' AND branch_working_hours.to2))
                        AND EXISTS (
                            SELECT * FROM branch_working_hours
                            WHERE branch_working_hours.branch_id = branches.id AND branch_working_hours.day LIKE UCASE(DAYNAME(SUBDATE(CURRENT_DATE(), 1)))
                        )
                    THEN 'OPEN'
                    ELSE 'CLOSED'
            end
        end
    end AS intrvl 
from `branches` 
inner join `branch_working_hours` on `branches`.`id` = `branch_working_hours`.`branch_id` 
group by `branches`.`id` 
having distance < ? 
order by `distance` asc

but this code most of the time returns wrong intrvl unless I add where branch_working_hours.day LIKE UCASE(DAYNAME(CURRENT_DATE())) but some times I need to check the yesterdays to get the current value

Upvotes: 0

Views: 46

Answers (1)

Abdo Sh
Abdo Sh

Reputation: 49

Found the solution to it as follows:

CASE
    When    
        EXISTS (
        SELECT * FROM branch_working_hours
        WHERE (branch_working_hours.branch_id = branches.id AND branch_working_hours.day LIKE UCASE(DAYNAME(CURRENT_DATE())))
        AND ((CURRENT_TIME() BETWEEN branch_working_hours.from1 AND branch_working_hours.to1 )
        OR (branch_working_hours.is_24_hours_open= 1)
        OR (CURRENT_TIME() BETWEEN branch_working_hours.from2 AND branch_working_hours.to2)
        OR (branch_working_hours.to1 < branch_working_hours.from1 AND CURRENT_TIME() > branch_working_hours.from1 )
        OR (branch_working_hours.to2 < branch_working_hours.from2 AND CURRENT_TIME() > branch_working_hours.from2))
) THEN 'OPEN'
    WHEN EXISTS (
        SELECT * FROM branch_working_hours
        WHERE (branch_working_hours.branch_id = branches.id AND branch_working_hours.day LIKE UCASE(DAYNAME(SUBDATE(CURRENT_DATE(), 1))))
        AND (
            (branch_working_hours.to1 < branch_working_hours.from1 AND CURRENT_TIME() BETWEEN '00:00:00' AND branch_working_hours.to1)
            OR (branch_working_hours.to2 < branch_working_hours.from2 AND CURRENT_TIME() BETWEEN '00:00:00' AND branch_working_hours.to2)
        )
        ) THEN 'OPEN'

ELSE 'CLOSED'
end AS intrvl

Upvotes: 1

Related Questions