Continuation of 'Simple Query'

[Peer Jones]

Hi Guys,

I posted a question here yesterday concerning a query about streets and houses etc., and you came up with the brilliant solution of using DECODE. Now I got so engrossed in the possibilities of DECODE that I spent half the night just seeing what it could do, creating more and more complicated examples until I hit on one that I just can't get my head around, getting absolutely lost in a maze of DECODES and GROUP BYs. This is actually based on a course system that is being used at my workplace (I have a very limited imagination).

I'm almost at a point that I think it can't be done this way at all. But I'd really like your opinion on this example.

I hope you don't think it's a waste of your time, but this is driving me crazy.

This is the example: I have a number of students that have to complete a number of qualifications. Each of these qualifications are made up of courses that are either required or recommended. Now, the completed courses have a completion date and I want to know the status of the qualifications for each student, but now it gets complicated:

A qualification for a student is marked as completed if:

1) All of the required courses for this qualification are completed, regardless of whether any of the recommended courses have been completed or not.
2) If even only one of the required courses is uncompleted, the qualification is uncompleted.
3) And this is the part I can't work out: If a qualification only has recommended and no required courses, the qualification is regarded as completed, regardless of whether any of the courses were completed or not!!!! Yikes!

This is an example table:

--Student---------Qualification----------Course-------------Course Status---------Course Completion Date

1) ---A----------------Q1----------------C1-------------------required--------------------has date-----
------A----------------Q1----------------C2-------------------required--------------------has date-----

2)----A----------------Q2----------------C3-------------------recommended--------------has date-----
------A----------------Q2----------------C5-------------------required---------------------no date-----
------A----------------Q2----------------C8-------------------required--------------------has date-----

3)----B----------------Q5----------------C3-------------------recommended---------------no date-----
------B----------------Q5----------------C7-------------------recommended---------------no date-----

4)----C----------------Q4----------------C6-------------------recommended---------------no date-----
------C----------------Q4----------------C8-------------------required---------------------no date-----

So as a result, I'd like to know how you get the following results for the table above:

Block 1): Stundent A's qualification Q1 is completed because all courses are required and have been completed
Block 2): Stundent A's qualification Q2 is uncompleted because one required course is uncompleted
Block 3): Stundent B's qualification Q5 is completed !!!!!!, but please don't ask me why, this is an actual example from work
Block 4) Stundent C's qualification Q4 is uncompleted because the one required course is uncompleted.

So the result should be:

Student-----Qualification--------Completed
---A--------------Q1------------------Y------
---A--------------Q2------------------N------
---B--------------Q5------------------Y------
---C--------------Q4------------------N------

Please help me here, otherwise my weekend will be gone. Can you still use DECODE on this one?