Hi, I am trying to build a quarterly report.
The database tables in concern are:
Brief: * Successful Course Applications go on to the Course Register (Course_App_FK) * Once on a Course Register they can attend sessions - a maximum of 6 * People who have attended 3 or more sessions have completed the course * People who have attended less than 3 sessions have not completed the course
I want a report that Counts how many Course Registrants did NOT complete courses broken down by Quarter. With 0 counts.
I have reports building up to this report and the SQL I have so far is as follows:
Telling us who has completed at least 1 session of a course
SELECT q.FISCAL_QTR ,q.FINANCIAL_QTR ,COUNT(cr.Session_6) AS THE_COUNT FROM QUARTERS q LEFT OUTER JOIN Course c ON CONVERT(VARCHAR,DATEPART(YEAR, c.Start_Date)) + '-' + CONVERT(VARCHAR,DATEPART(QUARTER, c.Start_Date)) = q.FISCAL_QTR LEFT JOIN Course_Reg cr ON cr.Course_FK = c.Course_PK WHERE (cr.Course_FK IS NULL) OR ( (cr.Session_1 = 1) OR (cr.Session_2 = 1) OR (cr.Session_3 = 1) OR (cr.Session_4 = 1) OR (cr.Session_5 = 1) OR (cr.Session_6 = 1) ) GROUP BY FISCAL_QTR ,Year_Desc
,Quarter_Desc ,FINANCIAL_QTR ORDER BY Year_Desc
This report returns the results as follows
(So using a view to get the quarters) I would like something similar to that above but how to count the number of 'ticks' against each session (bit fields) and then saying where that count is less than 3 for example.... I cant seem to work it out myself :-/
All help appreciated Thanks MJC