CODEWITHSUNDEEP
mysql
gazepam
gazepam

Reputation: 1

How to add condition in mySQL statement

I'm trying to add a condition in my mySQL statement where if the status is >= 6 then it only shlows the dates between fit_appointment_start_date and if the status is <= 5 then it uses the appointment_start_date condition instead. Is there a way to do this in Mysql?

At the moment i'm just doing an (and or) which is bringing back all the records in the condition, but i need it to change the condition depending on the status

This is my SQL.

SELECT j.client_first_name,
       u.home_postcode,
       j.engineer_id,
       j.deposit_taken,
       j.men,
       j.time_taken,
       j.potential_instalation_date,
       j.id,
       j.status,
       j.postcode,
       j.calendar_notes,
       j.hasinvoice,
       u.color,
       j.fit_appointment_start_date,
       j.fit_appointment_end_date,
       j.client_surname,
       j.appointment_start_date,
       j.appointment_end_date,
       u.username AS engineer_username,
       j.potential_instalation_notes
FROM   jobs j
       INNER JOIN users u
               ON u.id = j.engineer_id
       INNER JOIN status s
               ON s.id = j.status
WHERE  u.active = 1
       AND ( j.fit_appointment_start_date BETWEEN
                   '2021-05-04 00:00:00' AND '2021-05-04 23:59:59'
              OR j.appointment_start_date BETWEEN
                 '2021-05-04 00:00:00' AND '2021-05-04 23:59:59' )
ORDER  BY engineer_username

Upvotes: 0

Views: 72

Answers (1)

Akina
Akina

Reputation: 42632

if the status is >= 6 then it only shlows the dates between fit_appointment_start_date and if the status is <= 5 then it uses the appointment_start_date condition instead.

...
AND CASE WHEN status >= 6 
         THEN j.fit_appointment_start_date BETWEEN
                   '2021-05-04 00:00:00' AND '2021-05-04 23:59:59'
         WHEN status <= 5
         THEN j.appointment_start_date BETWEEN
                 '2021-05-04 00:00:00' AND '2021-05-04 23:59:59' 
         END
...

PS. The rows with status IS NULL won't be selected.

Upvotes: 1

Related Questions