-1

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?

Josef
  • 2,648
  • 5
  • 37
  • 73

1 Answers1

1

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
AgeDeO
  • 3,137
  • 2
  • 25
  • 57