I am writing this service to try to get my data for this week based on the current day , What I want is to retreive data from my machform starting monday whenever the current day is but i kept correcting sql syntax and it didnt work : can anyone tell me what's wrong with my query ? Thank u guys so much :
The Query :
SELECT date_created as Date, concat(element_41,' ', element_46) as Auditeur, Line.`option` as Ligne, z1.`option` as z1score, z2.`option` as z2score, z3.`option` as z3score, st.`option` as Score_Globale , element_9 as Deviations, d.`option` as defauts, element_12 as Top_action
FROM ap_form_122320 F
LEFT JOIN ap_element_options Line ON Line.form_id = 122320 AND Line.element_id = 3 AND Line.option_id = F.element_3
LEFT JOIN ap_element_options st ON st.form_id = 122320 AND st.element_id = 24 AND st.option_id = F.element_24
LEFT JOIN ap_element_options d ON d.form_id = 122320 AND d.element_id = 11 AND d.option_id = F.element_11
LEFT JOIN ap_element_options z1 ON z1.form_id = 122320 AND z1.element_id = 48 AND z1.option_id = F.element_48
LEFT JOIN ap_element_options z2 ON z2.form_id = 122320 AND z2.element_id = 49 AND z2.option_id = F.element_49
LEFT JOIN ap_element_options z3 ON z3.form_id = 122320 AND z3.element_id = 50 AND z3.option_id = F.element_50
WHERE (SELECT CAST(F.date_created AS DATE) ) <=
CASE (SELECT DATENAME(dw, CAST(NOW() AS DATE)) as theDayName) = ’tuesday’ THEN
(SELECT CAST(NOW() AS DATE))-1
WHEN (SELECT DATENAME(dw, CAST(NOW() AS DATE)) as theDayName) = ’wednesday’ THEN
(SELECT CAST(NOW() AS DATE))-2
WHEN (SELECT DATENAME(dw, CAST(NOW() AS DATE)) as theDayName) = ’thursday’ THEN
(SELECT CAST(NOW() AS DATE))-3
WHEN (SELECT DATENAME(dw, CAST(NOW() AS DATE)) as theDayName ) = ’friday’ THEN
(SELECT CAST(NOW() AS DATE))-4
ELSE
(SELECT CAST(NOW() AS DATE)
END
