CODEWITHSUNDEEP
mysqlsqldatabase-design
user1187
user1187

Reputation: 2198

Logic in MySQL Query To Skip Days

I have a website where people can go fix appointment to doctor.On the day before the doctors appointment the help desk calls the patient to make sure their presence on the day of appointment.

Now I made a Screen in Admin which Shows people who ever have appointment tomorrow.In case tomorrow is Sunday on Saturday it should show those who have fixed appointment on monday since clinic will be closed on Sunday.

I made a query which brings people list who have appointment tomorrow.Now what should i Add to this query in order to get people list for monday incase tomorrow is sunday.

CREATE TABLE Patients_Appointment_List(
  Patent_Id INT  UNSIGNED PRIMARY AUTO_INCREMENT,
  Patient_Name VARCHAR(255),
  appointment_Date DATE  
);

What Should I add To Below Query To Skip Sunday and Start Again From Monday

SELECT Patient_Name
  FROM Patients_Appointment_List
 WHERE appointment_Date BETWEEN now() and date_add(now(), INTERVAL 1 DAY)

Upvotes: 1

Views: 272

Answers (1)

Zane Bien
Zane Bien

Reputation: 23125

You can use a conditional CASE expression in the WHERE clause to check if tomorrow is Sunday. If it is, adjust the conditional check to include the next two days, otherwise, just show appointments up until tomorrow:

SELECT Patient_Name
FROM   Patients_Appointment_List
WHERE 
    CASE WHEN WEEKDAY(CURDATE() + INTERVAL 1 DAY) = 6 THEN 
         appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 2 DAY ELSE
         appointment_Date BETWEEN NOW() AND NOW() + INTERVAL 1 DAY
    END

Upvotes: 1

Related Questions