question

MJC avatar image
MJC asked

Query (Grouped By Quarters) needs to include 0 values

Hi, I am trying to build a quarterly report.

The database tables in concern are:

http://www.freeimagehosting.net/uploads/7213fd7f96.png

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
,Quarter_Desc

This report returns the results as follows

http://www.freeimagehosting.net/uploads/458e323780.png

(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

aggregates
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.