I would like an attendance register for face to face courses.
The report shows people attending and those who have cancelled.
I would like to eliminate the name of those who have cancelled.
When someone books on a course they are assigned a 50 in the status code.
When they cancel a new row is added with a 10.
AND fss.statuscode = '50'
AND fss.statuscode <> '10'
doesn't do the job as everyone is given a 50 and retains it.
Any ideas or alternatives welcome.
Heres the code..
SELECT DISTINCT u.firstname AS first_name, fss.statuscode, u.lastname AS last_name, uid3.data AS Payroll, uid6.data AS jobtitle, uid2.data AS Department, f.name AS course,
DATE_FORMAT(FROM_UNIXTIME(fsd.timestart),'%d-%m-%y | %T') AS course_start, DATE_FORMAT(FROM_UNIXTIME(fsd.timefinish),'%d-%m-%y | %T') AS course_end, " " AS Signature, " " certificate_required
FROM prefix_facetoface AS f
JOIN prefix_facetoface_sessions as fs ON f.id = fs.facetoface
JOIN prefix_facetoface_sessions_dates as fsd ON fs.id = fsd.sessionid
JOIN prefix_facetoface_signups as fsu ON fs.id = fsu.sessionid
JOIN prefix_user as u ON fsu.userid = u.id
JOIN prefix_facetoface_signups_status as fss ON fsu.id = fss.signupid
JOIN prefix_user_info_data AS uid ON uid.userid = u.id
JOIN prefix_user_info_data AS uid2 ON uid2.userid = u.id
JOIN prefix_user_info_data AS uid3 ON uid3.userid = u.id
JOIN prefix_user_info_data AS uid4 ON uid4.userid = u.id
JOIN prefix_user_info_data AS uid5 ON uid5.userid = u.id
JOIN prefix_user_info_data AS uid6 ON uid6.userid = u.id
JOIN prefix_user_info_data AS uid7 ON uid7.userid = u.id
JOIN prefix_user_info_data AS uid8 ON uid8.userid = u.id
WHERE uid.fieldid = '13'
AND uid2.fieldid = '1'
AND uid3.fieldid = '3'
AND uid4.fieldid = '8'
AND uid5.fieldid = '7'
AND uid6.fieldid = '9'
AND uid7.fieldid = '10'
AND uid8.fieldid = '12'
AND u.firstname <> 'test'
AND u.lastname <> 'test'
AND fss.statuscode = '50'
AND fss.statuscode <>'30'
AND fss.statuscode <>'60'
AND fss.statuscode <>'20'
AND fss.statuscode <>'80'
AND fss.statuscode <>'40'
AND fss.statuscode <>'10'
AND fss.statuscode <>'70'
AND fss.statuscode <>'100'
AND fss.statuscode <>'90'
%%FILTER_COURSENAME:f.name:~%%
%%FILTER_STARTTIME:fsd.timestart:>%% %%FILTER_ENDTIME:fsd.timestart:<%%
