Avatar of Scripter25
Scripter25
 asked on

True or False SQL

Check this one out for size

I am uploading a file for this in a minute as to hopefully explain this question a bit better.

You will see in the results in this excel file that a person had to take a test twice (dont Ask me what Attempted Test means) If they have Attempted a test or Failed a Test this script needs to check the Attempt_Number Column and see if was taken again and passed.

I need a script that will tell me True or False if all tests are passed


I need this answer ASAP I have a funeral to go to tomorrow.
Thanks in advance
The upload file should be up in a minute
Microsoft SQL Server

Avatar of undefined
Last Comment
DireOrbAnt

8/22/2022 - Mon
Scripter25

ASKER
dduser

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
DireOrbAnt

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
dduser

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)

Scripter25

ASKER
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
Scripter25

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Lowfatspread

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...

Scripter25

ASKER
there is no table for that
DireOrbAnt

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
EugeneZ

Scripter25

ASKER
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'.
ASKER CERTIFIED SOLUTION
DireOrbAnt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Scripter25

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

Thank you
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
DireOrbAnt

Not a problem :)