Results 1 to 4 of 4

Thread: SQL Help

  1. #1
    Join Date
    Mar 2007
    Posts
    212

    Unanswered: SQL Help

    Hi

    I have the following tables:

    Code:
    CREATE TABLE Persons(
      PersonID int NOT NULL,
      PersonName varchar NOT NULL,
      PersonEmail varchar Not NULL,
      PRIMARY KEY (PersonID) 
      
    )
    
    CREATE TABLE ClassStudents(
      ClassID int NOT NULL,
      StudentID int NOT NULL,
      PRIMARY KEY (ClassID, StudentID) 
      FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
      FOREIGN KEY (StudentID ) REFERENCES Persons(PersonId)
    )
    
    
    CREATE TABLE Class(
      ClassID int NOT NULL,
      CourseID int NOT NULL,
      StartDate datetime NOT NULL,
      EnsDate datetime NOT NULL,
      Location nvarchar NOT NULL,
      ... 
      PRIMARY KEY (ClassID) 
      FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
      FOREIGN KEY (CourseID) REFERENCES Courses(CourseId)
    )
    
    
    CREATE TABLE FeedBackForms(
      ClassID int NOT NULL,
      FormID int NOT NULL,
      PRIMARY KEY (ClassID, FormID) 
      FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
      FOREIGN KEY (FormID ) REFERENCES Forms(FormId)
    )
    
    CREATE TABLE Forms(
      FormID int NOT NULL,
      FormName int NOT NULL,
      AlertDays int NOT NULL -- number of days after class end date to send out form
      PRIMARY KEY (FormID)
    )
    
    CREATE TABLE CompletedFeedBackForms(
      ClassID int NOT NULL,
      FormID int NOT NULL,
      StudentID int NOT NULL,
      PRIMARY KEY (ClassID, FormID, StudentID) 
      FOREIGN KEY (ClassID) REFERENCES Class(ClassID)
      FOREIGN KEY (FormID ) REFERENCES Forms(FormId)
      FOREIGN KEY (StudentID ) REFERENCES Persons(PersonId)
    )
    A training class can have a number of different types of feedback forms attached to it (stored in table called FeedBackforms).

    Each type of feedback form (stored in table called Forms) has a rule (AlertDays) which determines the number of days after the class ended, to send out an alerts to each student that was enrolled on to the class.

    What would be the SQL to check the CompletedFeedBackForms table and the Forms AlertDays rule
    to pull out students and forms that have not yet been completed.

    I try the following but i cant get the AlertDays rule to work. The query will pull out all forms not completed regardless of the AlerDays rule.

    Code:
    SELECT DISTINCT	 t1.Email 
                   , t5.FormName 
    	       , t3.classid
                   , t5.FormID
    FROM Persons t1
    INNER JOIN ClassStudents t2 ON t1.PersonID = t2.StudentID
    INNER JOIN Class t3 ON t2.ClassID = t3.ClassID
    INNER JOIN ClassForms t4 on t3.ClassID = t4.ClassID
    INNER JOIN Forms t5 on t4.FormID = t5.FormID 
    where DATEDIFF(d,t3.EndDate,GETDATE()) >= t5.AlerDays 
    AND t1.PersonID Not in (SELECT StudentID                                    
                              FROM CompletedFeedBackForms                                     
                             WHERE Formid = t4.FormID                                      
                               AND Classid = t3.ClassID)
    Any one know what the correct sql would be?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    What do you get from this:
    Code:
    SELECT DISTINCT	 t1.Email 
                   , t5.FormName 
    	       , t3.classid
                   , t5.FormID, DATEDIFF(d,t3.EndDate,GETDATE()) , t5.AlerDays
    FROM Persons t1
    INNER JOIN ClassStudents t2 ON t1.PersonID = t2.StudentID
    INNER JOIN Class t3 ON t2.ClassID = t3.ClassID
    INNER JOIN ClassForms t4 on t3.ClassID = t4.ClassID
    INNER JOIN Forms t5 on t4.FormID = t5.FormID 
    where DATEDIFF(d,t3.EndDate,GETDATE()) >= t5.AlerDays 
    AND t1.PersonID Not in (SELECT StudentID                                    
                              FROM CompletedFeedBackForms                                     
                             WHERE Formid = t4.FormID                                      
                               AND Classid = t3.ClassID)

  3. #3
    Join Date
    Mar 2007
    Posts
    212
    Quote Originally Posted by MCrowley View Post
    What do you get from this:
    Code:
    SELECT DISTINCT	 t1.Email 
                   , t5.FormName 
    	       , t3.classid
                   , t5.FormID, DATEDIFF(d,t3.EndDate,GETDATE()) , t5.AlerDays
    FROM Persons t1
    INNER JOIN ClassStudents t2 ON t1.PersonID = t2.StudentID
    INNER JOIN Class t3 ON t2.ClassID = t3.ClassID
    INNER JOIN ClassForms t4 on t3.ClassID = t4.ClassID
    INNER JOIN Forms t5 on t4.FormID = t5.FormID 
    where DATEDIFF(d,t3.EndDate,GETDATE()) >= t5.AlerDays 
    AND t1.PersonID Not in (SELECT StudentID                                    
                              FROM CompletedFeedBackForms                                     
                             WHERE Formid = t4.FormID                                      
                               AND Classid = t3.ClassID)
    Get the same problem. Not sure why adding the two columns would make any difference anyway?

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So you can see what SQL Server sees. If you are getting all rows when you add the condition on AlerDays, then maybe there is a flaw with the way that condition is formed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •