I am developing a training management web-based system which will show the management the training record for each division. My database design is like following:
NOTE: The first attribute in each table is the primary key.
The SQL Query that I am using for this task is:
This query will show me the total number of taken quizzes by each division based on month. What I want now is showing these results for the last three months. Also, I want to show all the divisions even if there is a division which does not take any quiz. This means I want to show the division with zero number of taken quizzes.
If you need to filter a result set, generally the best approach is to add a WHERE clause. But, in this case, your filter needs to be in the JOIN criteria to the test table or you'll eliminate the divisions that haven't taken a test.
If you need to work off of datetime values and do something like "last three months" why not do a little date math:
That's the simplest way to do it. There are more sophisticated things you can do, but that'll get you started. Plus, this way avoids performing functions on your column so the query is still going to work with indexes well.