True or False SQL

Heres the direct link file

https://filedb.experts-exchange.com/incoming/ee-stuff/830-mydbResults.zip 

OK tell me do you have a table where all test details are mentioned, if yes then you need to following:-

Select Student_Number from Student Right Outer Join Test on Student.Test_Number = Test.Test_Number where Event_Type = 'Passed Test' group by Student_Number where Count(Test.Test_Number) = Count(Student.Test_Number)

Regards,

dduser

IF EXISTS (
  SELECT 1 FROM MyTestTable WHERE Student_Number = 10774 AND Event_Type = 'Attempted Test' AND Test_Number NOT IN
    (SELECT Test_Number FROM MyTestTable WHERE Student_Number = 10774 AND Event_Type = 'Passed Test')
)
  SELECT 'Not all passed' AS Result
ELSE
  SELECT 'All passed' AS Result

Sorry Correction:-

Select Student_Number from Student Right Outer Join Test on Student.Test_Number = Test.Test_Number where Event_Type = 'Passed Test' group by Student_Number having Count(Test.Test_Number) = Count(Student.Test_Number)

Um ok maybe this will help

The script I am looking for is to go inside this stored procedure This SP is working miunus this one function.

CREATE PROCEDURE usp_Student_Report_51
@student_num varchar(32)

AS
SELECT                 dbo.department.department_name,  
                  dbo.Course_Definition.mandatory,
                      dbo.Employee.lastname,
                  dbo.Employee.firstname,
                  dbo.Classes.Class_Number,
             
            (SELECT     TOP 1 Class_events.Event_Type
                            FROM          dbo.Class_Events
                            WHERE      dbo.Class_Events.Class_Number = dbo.Classes.Class_Number
                            ORDER BY CASE WHEN dbo.Class_Events.event_type = 'Passed Course' THEN 1 WHEN dbo.Class_Events.event_type = 'Past Test' THEN 2 WHEN
                                                    dbo.Class_Events.event_type = 'Attempted Test' THEN 3 ELSE 4 END DESC) AS Event_Type,
     
                 CASE WHEN (dbo.Classes.Status = 'Classroom' OR
                      dbo.Classes.Status = 'Other') THEN Classes.Class_Start_Date WHEN dbo.classes.status = 'OPEN' THEN
                          (SELECT     TOP 1 Class_events.Event_Date
                            FROM          dbo.Class_Events
                            WHERE      dbo.Class_Events.Class_Number = dbo.Classes.Class_Number
                            ORDER BY CASE WHEN dbo.Class_Events.event_type = 'Passed Course' THEN 1 WHEN dbo.Class_Events.event_type = 'Past Test' THEN 2 WHEN
                                                    dbo.Class_Events.event_type = 'Attempted Test' THEN 3 ELSE 4 END) END AS completion_date,

             CASE WHEN dbo.Classes.Status = 'Classroom' THEN 'Web-Assisted' WHEN dbo.classes.status = 'OPEN' THEN 'Web-Based' ELSE 'All Other' END AS Type,
                 CASE WHEN (dbo.Classes.Status = 'Classroom' OR
                  dbo.Classes.Status = 'Other') THEN Classes.Class_Start_Date WHEN dbo.classes.status = 'OPEN' THEN Activity.Start_Date END AS Start_date,
                      dbo.Classes.teacher, dbo.Classes.Class_Start_Date,
                      dbo.Classes.department_id,
                  dbo.Classes.teacher,
                  dbo.Students.Student_Number,    
                  dbo.Students.Bureau_ID,
                      dbo.Course_Definition.Course_Description,
                  dbo.Course_Definition.Standard_Hours
FROM         dbo.Students
INNER JOIN
                      dbo.Activity ON dbo.Students.Student_Number = dbo.Activity.Student_Number
INNER JOIN
                      dbo.Employee ON dbo.Students.employee_id = dbo.Employee.employee_ID
INNER JOIN
                      dbo.Classes ON dbo.Activity.Class_Number = dbo.Classes.Class_Number
INNER JOIN
                      dbo.Course_Definition ON dbo.Course_Definition.Course_Def_Number = dbo.Classes.Course_Number
INNER JOIN
                      dbo.department ON dbo.Classes.department_id = dbo.department.department_id
                 
WHERE dbo.Students.Student_Number = 108 AND dbo.Classes.deleted_class=0
GO


I think DireOrbAnt  is on the right track but I am unsure how to incorporate it into the above SP

oh and also the excel file was a small copy of what was in Class_Events table

DO NOT SUGGEST A JOIN OF ANY SORT AFTER THE FROM FOR THIS TABLE IT WILL SCREW THINGS UP

3 Days worth of work has taught me that much

you shouldn't have any problem joining to that table as it  is....

however what appears to be missing is a table which contains details of the number of tests that a course requires....


your sql then becomes

 coalesce((
 select  'NOT PASSED'
        from student as s
        Inner Join StudentCourse as Sc
         on s.studentid=sc.studentid
        Inner Join CourseTests as CT
         on sc.courseid=ct.courseid
        Where Not Exists
                 (select  'Y'
                     from  studenttestresults as str
                    Where s.studentid=str.studentid
                         and str.courseid=sc.courseid
                          and ct.testid = str.testid
                          and str.testresult = 'PASSED'
                    )
    ),'PASSED') as Result


just added to the select statement
although you could join it into the main body...

there is no table for that

You want another field with Passed or not? Then wrap my query in a CASE like:
CASE WHEN EXISTS (
  SELECT 1 FROM MyTestTable WHERE Student_Number = 10774 AND Event_Type = 'Attempted Test' AND Test_Number NOT IN
    (SELECT Test_Number FROM MyTestTable WHERE Student_Number = 10774 AND Event_Type = 'Passed Test')
) THEN 'Not all passed' ELSE 'All passed' END AS TestResult

where did you get the link:
https://filedb.experts-exchange.com/incoming/ee-stuff/830-mydbResults.zip 
it became:
http://www.ee-stuff.com/accessLogin.php?returnURL=%2FExpert%2FUpload%2FgetFile.php%3Ffid%3D830
it is asking for my EE login - not good

Sorry for taking so long to get back to everyone on this but DireOrbAnt this is the error I get when I try to add your script

Server: Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'CASE'.
Server: Msg 156, Level 15, State 1, Line 37
Incorrect syntax near the keyword 'THEN'.

Sorry for the long time it took for me to accept this I have been getting extremely busy

Thank you

Not a problem :)