cancel
Showing results for 
Search instead for 
Did you mean: 
cancel
Showing results for 
Search instead for 
Did you mean: 

sql queries

i_abidi
Regular Member

sql queries

 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

Announcements

Check out the upcoming Expert Session: Understanding ThingWorx Navigate Licensing in Community "Customer Events" section.