gistfile1.sql

SELECT s.title AS title,
concat(u.forename, ' ', u.surname) AS name,
from_unixtime(ss.start_time, '%d/%m/%Y') AS sessiondate,
if( v.id IS NULL , ss.venue, v.title ) AS venue,
if(b.status NOT IN ('attended'),'No', 'Yes') AS attended
FROM `sessions` s 
JOIN scheduled_sessions ss ON ss.session_id=s.id 
JOIN session_bookings b ON b.session_id=ss.id 
JOIN users u ON u.id=b.delegate_id
LEFT JOIN venues v ON v.id=ss.venue_id 
AND ($P{provider} IS NULL OR $P{provider}=0 OR $P{provider}=ss.provider_id)
AND ss.status='active' 
AND ($P{GenericSessions}=0 OR $P{GenericSessions} IS NULL OR $P{GenericSessions}=s.id) 
AND ($P{report_start_date} IS NULL OR (ss.start_time>unix_timestamp(STR_TO_DATE($P{report_start_date}, '%Y-%m-%d'))))
AND ($P{report_end_date} IS NULL OR ss.end_time < (unix_timestamp(STR_TO_DATE($P{report_end_date}, '%Y-%m-%d'))+86400))
AND ($P{bookingstatus}='all' OR b.status=$P{bookingstatus} OR $P{bookingstatus} IS NULL) 
GROUP BY CONCAT(u.id,'sess',ss.id)
ORDER BY ss.provider_id ASC, s.title ASC, ss.id, u.profile_code ASC