Search code examples
mysqlselect-case

Check specific dates using select case

I have two tables. One contains data with some dates, and second one consist all public holidays. My task is to determine let's say DayType for each record in the table.

For example if date is '2015-09-08 22:33' then output is "Sunday/Night Shift" or if date is '2015-06-08 12:20" then output is "Normal day".

I've used select case but i don't get good results.

Thake loot at my sample SQL Fiddle

Is there any other way for checking dates instead of using select case statement?

Solution

  • You order of Cases need work. MySQL stops 'CASING' when a CASE is true

    Your first CASE is WHEN (WEEKDAY(pdate) BETWEEN 0 AND 4) THEN "Normal Day" That means that it does not check for holidays on weekdays

    Check for holidays first then nightshifts and then weekdays/weekends

    SELECT tbltest.datac,
(
CASE
   WHEN (DATE(pdate) IN (SELECT HolidayDate FROM tblholidays)) THEN "Holiday"
   WHEN (TIME(pdate) > '22:00:00' && (DAYNAME(pdate)="Sunday")) THEN "Night shift/Sunday"
   WHEN (TIME(pdate) > '22:00:00') THEN "Night shift"
   WHEN (WEEKDAY(pdate) BETWEEN 0 AND 4) THEN "Normal Day"
   WHEN ((DAYNAME(pdate))="Saturday") THEN "Saturday"
   WHEN ((DAYNAME(pdate))="Sunday") THEN "Sunday"
END) AS DayType
FROM tbltest