You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
I'm trying to create a custom sql report the start and end filter. I would like to apply the filter to both timeenrolled and timecompleted in different subqueries.
The filter for the timecompleted is not being applied.
Source SQL:
SELECT fullname as Course, Enrolments.Total as Enrolments, CompletedEnrolments.Total as Completed
FROM prefix_course c
LEFT OUTER JOIN
(SELECT course, COUNT(*) AS Total
FROM prefix_course_completions cc
JOIN prefix_user_info_data d ON cc.userid = d.userid
WHERE d.fieldid = 2 %%FILTER_USERS:d.data%%
%%FILTER_STARTTIME:cc.timeenrolled:>=%% %%FILTER_ENDTIME:cc.timeenrolled:<=%%
GROUP BY course) AS Enrolments ON c.id = Enrolments.course
LEFT OUTER JOIN (SELECT course, COUNT(*) AS Total
FROM prefix_course_completions cc2
JOIN prefix_user_info_data d ON cc.userid = d.userid
WHERE d.fieldid = 2 %%FILTER_USERS:d.data%%
%%FILTER_STARTTIME:cc2.timecompleted:>=%% %%FILTER_ENDTIME:cc2.timecompleted:<=%%
GROUP BY course)
AS CompletedEnrolments ON c.Id = CompletedEnrolments.course
WHERE Enrolments.Total > 0 OR CompletedEnrolments.Total > 0
Generated sql
SELECT fullname as Course, Enrolments.Total as Enrolments, CompletedEnrolments.Total as Completed
FROM mood_course c
LEFT OUTER JOIN
(SELECT course, COUNT(*) AS Total
FROM mood_course_completions cc
JOIN mood_user_info_data d ON cc.userid = d.userid
WHERE d.fieldid = 2 AND d.data LIKE '%NSW%'
AND cc.timeenrolled >= 1640998920 AND cc.timeenrolled <= 1644282120
GROUP BY course) AS Enrolments ON c.id = Enrolments.course
LEFT OUTER JOIN (SELECT course, COUNT(*) AS Total
FROM mood_course_completions cc2
JOIN mood_user_info_data d ON cc2.userid = d.userid
WHERE d.fieldid = 2 AND d.data LIKE '%NSW%'
GROUP BY course)
AS CompletedEnrolments ON c.Id = CompletedEnrolments.course
WHERE Enrolments.Total > 0 OR CompletedEnrolments.Total > 0 LIMIT 0, 5000
The text was updated successfully, but these errors were encountered:
I'm trying to create a custom sql report the start and end filter. I would like to apply the filter to both timeenrolled and timecompleted in different subqueries.
The filter for the timecompleted is not being applied.
Source SQL:
Generated sql
The text was updated successfully, but these errors were encountered: